Microsecond Support for MySQL Slow Logs

转载 2011年01月21日 15:14:00

Microsecond Support for MySQL Slow Logs

This document summarizes microsecond resolution support for the MySQL slow query log. MySQL has not always supported microsecond resolution for slow query logging. The first major appearance of such support was in late 2006 by the experts at MySQL Performance Blog. Since then, their patch has been officially integrated into newer versions of MySQL and they have written patches for older versions of MySQL.

Summary of Official Support

v4.0, 4.1, 5.0: not supported; long_query_time is limited to a resolution of 1 to 10 seconds.

v5.1 up to and including 5.1.20: not supported; long_query_time is limited to a resolution of 1 to 10 seconds.

5.1.21+: for the value of long_query_time "the minimum is 0, and a resolution of microseconds is supported when logging to a file. However, the microseconds part is ignored and only integer values are written when logging to tables." (MySQL :: MySQL 5.1 Reference Manual :: 5.2.5 The Slow Query Log)

6.0 up to and including 6.0.3: not supported; long_query_time is limited to a resolution of 1 to 10 seconds.

6.0.4+: for the value of long_query_time "the minimum is 0, and a resolution of microseconds is supported when logging to a file. However, the microseconds part is ignored and only integer values are written when logging to tables." (MySQL :: MySQL 6.0 Reference Manual :: 5.2.5 The Slow Query Log)

Microsecond Resolution: What & Why

A microsecond, denoted by the symbol µs, is one millionth of a second. That means 1 µs is equal to 0.000001 second. This is not to be confused with a millisecond (denoted by 'ms') which is one thousandth of a second, or 0.001 second.

Obviously, the change from second to microsecond resolution support for long_query_time is huge. Microsecond resolution allows a realistic logging of slow queries. In most high-end applications, even 1 full second is too long. Therefore, developers, DBAs and hackers alike think in much finer terms: milliseconds and microseconds.

In reality, query optimization is most often done in milliseconds rather than microseconds. Whereas a 1 second query can be painfully long, a 1 millisecond query is better than most queries can achieve. Regardless, the maximum resolution is in microseconds because this is the resolution provided by the underlying code.

1 s vs. 1 ms vs. 1 µs At A Glance

Since microsecond slow logging in MySQL is relatively new, I have not seen many applications which make the clear distinction between 1 second, 1 millisecond and 1 microsecond. Some "legacy" applications if ran on a new version of MySQL supporting microsecond resolution may show unwieldy values, for example: 0.025000 s instead of 25 ms.

At a glance, the distinction between these three resolutions is:

0.000000 - 0.000999 s = 0 - 999 µs
0.001000 - 0.999999 s = 1 ms - 999.999 ms
1.000000 - n.nnnnnn s = 1 s - n.nnnnnn s

Presently, mysqlreport v3.5+ formats the value of long_query_time shown on the Slow line of its report (line 16) according the proper distinction, displaying µ, ms or s. Eventually, mysqlsla will do the same, too.

Microslow (msl) Logs

Microslow (msl) logs were created by the people at MySQL Performance Blog. Microslow logs are basic MySQL microsecond slow logs plus 10 to 16 additional meta-properties.

An msl log entry looks like (line numbers added for reference):

      1  # User@Host: user[user] @ host []
      2  # Thread_id: 1  Schema: db
      3  # Query_time: 0.000000  Lock_time: 0.000000  Rows_sent: 0 Rows_examined: 0
      4  # QC_Hit: Yes  Full_scan: No  Full_join: Yes  Tmp_table: No  Tmp_table_on_disk: Yes
      5  # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 10
      6  #   InnoDB_IO_r_ops: 11  InnoDB_IO_r_bytes: 12  InnoDB_IO_r_wait: 13.000000
      7  #   InnoDB_rec_lock_wait: 14.000000  InnoDB_queue_wait: 15.000000
      8  #   InnoDB_pages_distinct: 16
      9  SELECT bananas FROM fruits;

 

Lines 2, 4 and 5 show the first 10 additional meta-properties which are always logged. Lines 6, 7 and 8 show the 6 additional InnoDB-related meta-properties which are logged if the query used an InnoDB table; otherwise a singe line is logged which says:

      6  # No InnoDB statistics available for this query

 

These additional meta-properties are excellent additions to the basic MySQL slow log. Unfortunately, msl logs are not officially supported in any version of MySQL. To get them, you must patch your server. An installation walk-through was written to describe how.

To my knowledge, there is no script which can parse msl logs, but one is being created: mysqlsla v2. Watch the Hack MySQL news for updates; I expect to release mysqlsla v2 in early June, 2008.

Microsecond Support for MySQL Slow Logs was last updated May 21, 2008.

相关文章推荐

Expdp For Metadata Is Slow In 11.2.0.3 With Partitioned Objects [ID 1466040.1]

Applies to: Oracle Server - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2] Infor...

Checklist for Slow Performance of (expdp) (impdp) [ID 453895.1]

Checklist for Slow Performance of Export Data Pump (expdp) and Import DataPump (impdp) [ID 453895.1...

Logs and Monitoring for Apache Servers

Logs and Monitoring for Apache ServersDaniel LopezExcerpted from Apache Phrasebook, published by Pea...

Android Gradle Build Error:Some file crunching failed, see logs for details解决办法

错误日志:Error:java.lang.RuntimeException: Some file crunching failed, see logs for details     ...

【阅读笔记】Mining Concept Sequence from Large-Scale Search Logs for Context-Aware Query Suggestion

背景: 这是微软研究院的一篇论文。第一作者是南开大学 Liao Zhen ,主页是http://kdd.nankai.edu.cn/showMemberAction.do?tp=0&&id=80...
  • poson
  • poson
  • 2012年04月05日 11:46
  • 933

android studio编绎时出现Error:Some file crunching failed, see logs for details

解决办法如下 在build.gradle(Module:app)这一个文件中添加下面的配置 aaptOptions { cruncherEnabled = false ...
  • callzjy
  • callzjy
  • 2016年12月24日 02:02
  • 1298

Jenkins使用Gradle构建Android项目时Error: Some file crunching failed, see logs for details

* What went wrong: Execution failed for task ':app:mergeReleaseResources'. Error: Some file crunchi...
  • msl0903
  • msl0903
  • 2017年05月02日 11:41
  • 980

用mysqldumpslow分析mysql的slow query log

mysql有一个功能就是可以log下来运行的比较慢的sql语句,默认是没有这个log的,为了开启这个功能,要修改my.cnf或者在mysql启动的时候加入一些参数。如果在my.cnf里面修改,需增加如...

处理 mysql slow sql

在slow_query_log 值为ON的情况下(默认为OFF 默认为10s,同时精确到微秒)默认(log_output值为FIFL时)就会把这种慢查询记录到:slow_query_log_file值...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Microsecond Support for MySQL Slow Logs
举报原因:
原因补充:

(最多只允许输入30个字)