MySQL Tuning series _ Log Analysis

Preface

This article summarizes several MySQL database logs, for the daily maintenance process problem solving and performance optimization, a slightly more basic, daily accumulation of use.

Part of the article will be the MySQL database and the SQL Server database part of the content of a comparison, not to criticize the pros and cons, only to describe the technology, the right to do learning.

Gossip less, directly into the topic of this article.

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

I remember when I wrote SQL Server, I wrote an article about some of the error logging in the SQL Server startup process, which you can click to view. Also, during the MySQL database run, there are its own database error logs.

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:

 like ' Log_error ';

Navigate to the file of the error log through the above command, and if so, you can view the log details on the appropriate server as follows:

/var/log/mysql/error.  Log     

Of course, the above commands are generally used to modify files under the Linux platform, but they can also be used as viewing files.

And, under the right conditions, MySQL automatically archives these error logs for later problem lookups.

In the above log file can be seen, there is a prompt is the IP address of the domain name resolution problem, so you can use this file to record login information and so on.

Second, slow query log

The so-called slow query log is used to record the slow running statements in MySQL, so this file is convenient for overall performance tuning, we know that in SQL Server can only be found through the appropriate DMV.

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, the default value is 10, which means to run a statement above 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

 like ' %long% ';

 like ' log_slow_queries '

Let's turn on this slow query statement.

SET Global Slow_query_log=1

 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:

 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 (ten);

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;

 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.

It's a little bit longer, and we're not going to show you here.

Conclusion

This article comes first, the content of MySQL performance tuning involves a wide range of articles, followed by the analysis.

If you read this blog, feel that you have something to gain, please do not skimp on your " recommendation ".

MySQL Tuning series _ Log Analysis