MySQL microsecond slow query patch

Author/Translator: ye Jinrong (Email: imysql@imysql.com), source: http://imysql.cn, reproduced please indicate/translator and source, and cannot be used for commercial purposes, offenders must investigate.

Original article: The new cool MySQL patch has landed! Check your queries performance !, This article only provides partial translation.

The microtime slow query patch package of MySQL has the following features:

• Identify connections

The connection thread is recorded in the results of each slow query, as shown below:

# Thread_id: 571

• Microsecond count

You can record the slow query with a execution time of 1 second.Long_query_timeThe Unit is now microseconds, not seconds. For example, if it is set to 300000, it indicates 0.3 seconds.

# Query_time: 0.021752  Lock_time: 0.000016  Rows_sent: 1  Rows_examined: 103

• Slow query generated by record Replication

In general, MySQL does not record slow queries generated by Replication. After using this patch, you only need to enable the option-- Log-slow-slave-statementsSolve this problem.


• Detailed query execution plan

The execution plans for each query are different. You may use index scans, full table scans, or temporary tables. Normally, this information can also be retrieved fromEXPLAINThe result is displayed. The patch will show the most important aspects of the query in the log, as shown below:

1.  # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No2.  # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0

QC_HitIndicates whether the query cache is hit by this query (Query Cache). If0The query is not actually executed.
IfFull_scanThe result isYesThis indicates that the query is poor because the full table needs to be scanned.
Full_joinIndicates that no index is used for joint queries.
To create a temporary tableTmp_tableThe result isYesIf you want to create a temporary disk-based table instead of a memory-based tableDisk_tmp_tableIsYes.
If you useFilesortAlgorithm, thenFilesortThe value is Yes,Filesort_on_diskIt indicates that the sorting is based on temporary files.


• Use of InnoDB

The last part is the statistics of InnoDB usage results. MySQL is now allowed to runSHOW SESSION STATUSTo view the statistics of each thread, but this does not include the use of InnoDB, because InnoDB always displays the global statistics. This patch allows you to view the InnoDB resource usage of each query.

#   InnoDB_IO_r_ops: 3  InnoDB_IO_r_bytes: 49152  InnoDB_IO_r_wait: 0.018690#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000#   InnoDB_pages_distinct: 7

InnoDB_IO_r_opsThe number of pages to be read in the statistical plan. The actual quantity may be different. Although it can be done asynchronously, unfortunately there is no good way to measure it. The Unit is bytes ).
SlaveInnoDB_IO_r_waitThe InnoDB read data lock wait time (in seconds) from the storage engine is displayed ).
InnoDB_rec_lock_waitThe time required to wait for the row lock (in seconds ).
InnoDB_queue_waitThe time (in seconds) consumed to wait for the queue to enter the InnoDB queue or to wait for execution in the queue ).
InnoDB_pages_distinctDisplays the number of independent pages read. This is only based on the approximate value of the entire buffer pool represented by a small hash array, because it may need a large memory to map all pages. The increase in the number of read pages generated by the same query may be due to a hash collision.
If InnoDB is not used in the query, the related log lines are changed to the following, and the others remain unchanged:

# No InnoDB statistics available for this query

Note:Although the patch has been stress tested and used in the actual environment, we recommend that you do not use it in very important systems. In case of a problem, people are not responsible for it :)