Performance analysis and optimization of mysql statements

1. Use explain to view the query plan

2. Use show processlist to view the query process (in which state), complete The command is as follows mysql -uroot -p -e 'show processlist \G' |grep state: |sort|uniq -c|sort -rn This method is similar to method 3, and it should be said that method 3 is more useful.

3. Use show profile. It is disabled by default and needs to be enabled with set profiling = 1. After executing some queries, type show profiles to see that the query time of the previously executed statements is displayed with high accuracy. Then use show profile for query n to see each step of query execution corresponding to the query statement and the time it takes.

4. Use slow logs and use the third-party tool pt-query-digest to generate analysis reports. When using this analysis method, it is very likely that you need to change the configuration file, which can be set to the following form: log_slow_queries = /var/log/mysql/mysql-slow.log#The log storage directory long_query_time = 0 //Capture all query logs -queries-not-using-indexes//Can be recorded even if indexes are not used

In the project, it was found that almost all the program execution time is consumed in database operations. Use pt-query-digest to make an analysis report on the slow query log (in actual production, it is not easy to open and close the slow query log. In this case, you can simulate it by monitoring TCP traffic or using tcpdump). It is found that update and insert operations account for all 95% of the time.

So the executed statements are further analyzed.

The time consumption of each part of this update statement is as follows:

It can be seen that the time is mainly spent on the query end status.

Get the answer from Google, add innodb_flush_log_at_trx_commit = 0 to the mysql configuration file my.conf. After verification, the problem was successfully solved and the speed improvement was very obvious (the above changes also had an effect on the insert operation). At the same time, questions are left: What is the status of query end, why does it take so long, and why is the performance improved so much after adding innodb_flush_log_at_trx_commit = 0?

What is the status of query end? The official documentation of mysql explains: This state occurs after processing a query but before the freeing items state. My understanding is that the statement is executed, but there is still some follow-up work that has not been completed.

So what is the status of freeing items? The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up. It is to release the space in the query cache (because it is an update operation, so the corresponding records in the cache are It is invalid, so this step is needed).

The default value of innodb_flush_log_at_trx_commit is 1. The behavior at this time is: the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. The function of the log buffer: allows the transaction to write the log (the transaction needs to maintain a log) to the disk only after the execution is completed. Should the time be mainly spent on disk IO?

After changing the value of innodb_flush_log_at_trx_commit to 0, the behavior is as follows: If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. It can be seen that after changing to 0, the operation that should be performed every time is now performed only once every second, thus saving a lot of time.

Setting the value of innodb_flush_log_at_trx_commit to 0 has a side effect: the crash of any server-side mysql program will cause the last second of transactions to be lost (before they have time to be included in the log file). But considering that this application does not have to have such strict requirements on transactions, this is acceptable.

The above is the performance analysis and optimization of mysql statements. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!