Monday, April 28, 2008

Variable's Day Out #9: long_query_time


Applicable To MySQL Server
Server Startup Option --long-query-time=<value>
Scope Both
Dynamic Yes
Possible Values (< 5.1.21): Integer
(>=5.1.21): Numeric
Minimum Value (< 5.1.21): 1
(>=5.1.21): 0
Default Value 10
Category Monitoring


In case (as generally the case is) one wants to know about the bottlenecks in their system, MySQL's has a small answer to this in "Slow Query Logs". Any query that takes more time than specified by long_query_time, is labeled as a slow query and logged. Time measurement for a query is done in real time and not CPU time. So, whether a query is slow or not depends on the system that you are running on and the load the system is running under.

For many people's delight, as of MySQL 5.1.21, the long_query_time can be specified in microseconds and minimum value has been changed to 0. Earlier, the minimum value was 1.  If set to "0", it will log all the queries under slow query log. However, the new microsecond resolution in 5.1.21 is only supported when logging to a file and microsecond part is ignored when logging to table. The table logging was introduced in MySQL 5.1.6.

How to Use:

This is completely dependent on your use-case, SLA's etc. I have two systems, one of them is running with long_query_time being 5 seconds. Whereas for the second one, we are planning to upgrade so to become capable of using microsecond resolution (actually we need up-to millisecond resolution ;) ).

Related Variables:

  • log_slow_queries
  • log_queries_not_using_indexes
  • min_examined_row_limits
  • log_output
  • slow_query_log_file

Read More:


Hope you enjoyed reading this.

No comments: