MySQL Tuning series _ Log Analysis

Technical preparation

Hosted under the Ubuntu14.04.2 platform, based on the MYSQL5.5.46 version.

Log files record the various types of MySQL database activities, as a daily location problem of the most common analysis means, MySQL database commonly used in the log files are divided into the following categories: error log, binary log, slow query log, query log.

First, error log

This log records some detailed records of the process of starting, running, and closing the MySQL runtime, and in the event of a problem, you can view the log, which not only records the error message, but also notes some warnings and, of course, some running information.

You can view the file path for the error log by using the following command:

Show variables like ' log_error ';

Navigate to the file of the error log with the above command and, if there is a problem, you can view the log details on the appropriate server.

Second, slow query log

The slow query log is a log file of related SQL statements that affect database performance in the MySQL server, which is improved to improve database performance by analyzing these special SQL statements. By default, the MySQL database does not start the slow query log, we need to manually set this parameter, of course, if not tuning needs, it is generally not recommended to start this parameter, because there is a certain performance impact.

Of course, what kind of statement can be called a slow statement, so there needs to be a threshold to define, once the run time exceeds this value will be recorded in this slow query log.

Let's take a look at the setting of this threshold, which can be set by Long_query_time with a default value of 10s.

By default, the MySQL database does not start the slow query log, we need to manually set this parameter, of course, if not tuning needs, it is generally not recommended to start this parameter, because there is a certain performance impact.

Look, the Setup method

Show variables like '%long% ';

Show variables like ' log_slow_queries '

Let's turn on this slow query statement.

SET Global Slow_query_log=1

SHOW variables like  '%query_log% ';

Of course, we can adjust the default time threshold to the following method

SET Global long_query_time=0.1;

Verification, there is a little bit of skill here, need to open a new window to query, the current window is not in effect, do not know that is not counted MySQL a small bug. We opened a new window to verify the following:

Isn't that cool? Let's take a statement to verify how it works.

To facilitate the demonstration, I set this threshold to 0.001S, a small value, let's find a statement to test:

Let's execute the following script:

SELECT * from tables;

Let's take a look at whether the slow query log has been recorded:

sudo more  /var/lib/mysql/wu-virtual-ubuntu01-slow.log

Here the log view, need to take the right operation.

As you can see, the statements we have queried are now exported to the log, and of course some other statements are recorded.

And, detailed record execution time, execution user, run time, lock time, return line and other basic information.

Of course, there are many students here to meet the demand, generally we run the time, the server to monitor the time, let me a machine to see the file, I want to engage in a monitoring system, need to monitor each slow log file, and parse it very uncomfortable.

In order to solve this problem, MySQL intimate to provide us with a system of the table to view, which is convenient for us to operate, such as I want to see the slowest first 10 statements ...

It is important to note that this method is only built on top of MySQL 5.1, as follows:

First, let's look at the default output, the script is as follows:

Show variables like ' log_output ';

As you can see, the default output here is file, and we change this to table.

Set global log_output= ' TABLE ';

Select Sleep (10);

SELECT * from Mysql.slow_log;

is not very intimate .... You can do a variety of checks through T-SQL statements.

In our database optimization, many times by creating the appropriate index to optimize, so that if we know that a database of those statements are not applied to the index, or is a full table scan, it is very convenient and easy for us to optimize.

So, in the slow log of MySQL, we have a thoughtful addition of a parameter, used to record the statement without using the index;

Show variables like ' log_queries_not_using_indexes ';

The default is closed, we can open this parameter, to make a detailed record;

SET Global Log_queries_not_using_indexes=1

With this parameter setting, you can track the statements in MySQL that do not use the index and run longer, and the following optimizations are understood.

Three, slow query log parameters:

Long_query_time: Set the threshold of the slow query, the SQL that exceeds the sub-set value is logged to the slow query log, the default value is 10s
Slow_query_log: Specifies whether to turn on the slow query log
Log_slow_queries: Specifies whether to turn on the slow query log (this parameter should be replaced by Slow_query_log for compatibility retention)
Slow_query_log_file: Specifies the slow log file location, can be empty, the system will give a default file Host_name-slow.log
Min_examined_row_limit: Query check returns less than the specified row for SQL is not logged to the slow query log
Log_queries_not_using_indexes: Slow query logs that do not use indexes are logged to the index

Article from:http://www.cnblogs.com/zhijianliutang/p/4731307.html

MySQL Tuning series _ Log Analysis