How to Read MySQL Slow Query Log?

When you are preparing for an interview for a position that deals with specific IT applications, it is important to be ready for pertinent questions on the listed job skills. For instance, if the job has MySQL listed as a required or preferred skill, then you should be prepared to answer questions such as how to read MySQL slow query log during the interview.

How to Read MySQL Slow Query Log?

The MySQL slow query log is a log file that contains all queries that take longer to execute than a pre-determined amount of time. This time limit can be either the default limit or a specified time limit. Using the MySQL slow query log is a useful tool to ensure that each page loads quickly and to help in diagnosing possible issues with slow loading times. This is especially useful when you are designing your own web applications.

How to Enable the MySQL Slow Query Log

By default, the MySQL slow query log is disabled. To enable it, set the “slow_query_log” system variable to “1”. The query time can be set with “long_query_time=time”.

The log file is written to file by default but can also be written to a table. You can name the file with “slow_query_log_file=file_name”, which is by default named “host_name-slow.log”, and the default table is the “slow_log” table in the MySQL database.

What Is in the MySQL Slow Query Log?

The slow query log contains all queries that take longer than the designated time limit to run. The default time is 10 seconds. You can use microseconds when saving the log as a file but not when saving as a table. The slow query log variable can be adjusted dynamically.

The default for logged queries is for non-administrative statements and indexes; however, you can set the log for queries without indexes by “log_queries_not_using_indexes”. Administrative statements can be set with the “log_slow_admin_statements” variables. Table queries that only have one or less rows will not be logged.

How to Analyze the MySQL Slow Query Log

While you can read these log files for yourself, it is helpful to have some type of tool that analyzes the log files and gives statistical analysis and information on exactly what is happening during your application runtimes. Two useful analysis tools are mysqldumpslow from MySQL and pt-query-digest from Percona.

Conclusion

The MySQL slow query log is particularly useful when you are designing your own applications, especially if the project’s size is set to substantially grow over time. An application that only contains a small amount of data in the beginning may become slower to run over time as more rows are added to the tables. Being able to pinpoint slower queries that could be contributing to long load times is a crucial part of all database troubleshooting processes.

Any additional thoughts and helpful ideas on this How to read MySQL slow query log question are welcome inclusions in the comment section.

Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.