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.
Summary
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.
Could we use waits here? I forgot what the command is in PHP to initiate a wait. You could also sort the slow queries by which took the longest, giving you a priority list.
I don’t know what exactly do you think about, but PHP uses the sleep() function to delay execution.
Nice post but you can also optimize your query using profiling system which is now a days most popular technique.
Yes Shahriat, I agree with you but another optimizing techniques are getting ready, so stay tuned! Nevertheless, any suggestion for new parts of this MySQL optimization mini series is appreciated as well! 😉
I am new to PHP having moved over from .NET. I’m glad I read this article as I have many queries that seem to run slow in our CMS package which have been identified using the slow log process. I just need to know how to optimize them now! Great post thanks!
How come insert the slow log queries info in slow log table as shown above?
I have the same query in multiple places, I would like to find out what file the query is in in the log. Is this possible?
Yes, of course it’s possible. Use some file searching utility – grep for an instance…