Variable's Day Out #9: long_query_time

Properties:

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

Description:

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:

• MySQL manual entry on long_query_time
• "The Slow Query Log" documentation
• ... & Slow Query Log Output Destinations

 

Hope you enjoyed reading this.