40.3. Using The Slow Query Log for Diagnostic Purposes

MySQL Server has the capability of writing information about "slow" queries to its slow query log. The value of the long_query_time system variable indicates how long a query can run before being considered slow. Its value is interpreted as number of seconds in wall clock (elapsed) time. The default long_query_time value is 10, so if the slow query log is enabled, queries that run for longer than 10 seconds are logged, along with comments that contain additional information about the queries.

The slow query log does not contain errors, but it is a source of diagnostic information in the sense that the frequent appearance of a query in the log is an indicator that the query should be analyzed to see if any optimizations can be applied to it to make it execute faster. Details about optimizing are beyond the scope of this chapter, but the following list indicates some general types of optimizations that are possible and the chapters where they are discussed further:

• Rewrite the query into more efficient form. (See Chapter 22, "Basic Optimizations.")

• Change the tables that the query uses (for example, by normalizing or adding an index). (See Chapter 37, "Optimizing Queries," and Chapter 38, "Optimizing Databases.")

• Tune server parameters. (See Chapter 39, "Optimizing the Server.")

The server writes queries to the slow query log in plain text format, so you can examine the log using any text display program such as a pager or a text editor. To obtain a summary of the log's contents, use the mysqldumpslow utility:

 shell> mysqldumpslow log_file 

mysqldumpslow tries to determine when queries are similar and can be grouped. For example, the following two queries fit the same pattern because they differ only in the data values:

 SELECT * FROM t WHERE id = 'H7XQ19' AND age < 10; SELECT * FROM t WHERE id = 'J8MZ48' AND age < 20; 

mysqldumpslow groups those queries and reports them in its summary output using a "template" that looks like this:

 SELECT * FROM t WHERE id = 'S' AND age < N; 

Here, 'S' and N indicate where the query contains string and numeric data values, respectively.

Along with each query, mysqldumpslow shows the number of times it appears in the log, the user who issued it, and some execution time information.

Normally, the slow query log can be found, if it is enabled, in the server's data directory. Configuration options for enabling it, specifying its name and location, and changing the value of "slow" are provided in Chapter 24, "Starting, Stopping, and Configuring MySQL."