This is a second part of MySQL optimizing tutorial mini series. As the title indicates, we’re going focus on MySQL slow queries and their logging. We’ll use the official MySQL manual for version 5.0 as a refernce. After read of this post you should be able to set up logging of MySQL slow queries for further use and be familiar with its basic output.
What Is It?
As mentioned earlier, in the first Optimizing MySQL part, the logging and evaluation of MySQL slow queries is one of the most important optimization techniques. The slow query log consists of all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. By exploring a slow query log we can get very interesting data to analyze. Results of such analysis may have positive consequences on MySQL performance. But before we start check something we must have it available.
Turn it ON
Logging of slow queries is builtin capability of mysqld (MySQL deamon) which physically makes these entries. To enable mysql log slow queries, we have two choices how to do that:
If you do not specify the optional parameter file_name in the first case, MySQL creates a new file with default name host_name-slow.log and starts write slow queries in there. The second case does not allow you to specify the filename and slow queries are written into the slow_log files and are very friendly checkable as a classic MySQL table of default mysql database. Actually, you can find slow queries files in the /var/db/mysql/mysql/ directory in both cases. Before we’ll move forward, it would be usefull to mention the fields of slow queries log.
Slow Log Table Fields
As you can see from the fields above, an information about slow query is good enough to use it for optimization. Maybe I forgot explain one important detail mentioned in the second paragraph of this post: the selection criteria for slow query entry is long_query_time option set in the mysql config file. I have this option set to 5 seconds (long_query_time=5 right before the log_slow_queries in the my.cnf file) as shown below.
The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process the slow query log using the mysqldumpslow command to summarize the queries that appear in the log or explore the slow_log table entries by means of classic phpMyAdmin web interface. There’s also CSV file in the /var/db/mysql/mysql/ directory available for data export and further manipulation.