ready-to-use resources, tutorials, tips and many other web development related stuff
WARNING! You probably have JavaScript disabled. Some features of this site will not work properly unless JavaScript enabled.
www.webdevelopmentstuff.com changed to www.webdevstuff.com

Optimizing MySQL: Log Slow Queries

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:

  • Start mysqld with option --log-slow-queries[=file_name] from the command line
  • Start mysqld with option log-slow-queries=1 in the MySQL configuration file (usually /usr/local/etc/my.cnf)

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

MySQL Slow Log Table

  • start_time: datetime when a query starts
  • user_host: host on which a query was executed
  • query_time: how long a query took
  • lock_time: how long a table lock took
  • row_sent: number of rows sent back to caller
  • row_examined: number of rows scanned
  • db: name of database on which a query was executed
  • last_insert_id: not applicable, used if AUTO_INCREMENT set up
  • insert_id: not applicable, used if AUTO_INCREMENT set up
  • server_id: mysql server identification
  • sql_text: text of sql statement (which actually causes the slow query)

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.

MySQL Config Excerpt

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.

8 Responses to “Optimizing MySQL: Log Slow Queries”

  1. David says:

    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.

  2. Teddy says:

    I don’t know what exactly do you think about, but PHP uses the sleep() function to delay execution.

  3. Nice post but you can also optimize your query using profiling system which is now a days most popular technique.

  4. Teddy says:

    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! 😉

  5. 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!

  6. sonny says:

    How come insert the slow log queries info in slow log table as shown above?

  7. Luke says:

    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?

  8. Teddy says:

    Yes, of course it’s possible. Use some file searching utility – grep for an instance…

© 2008 - 2017, webdevstuff.com | Powered by Wordpress | Theme by Elegant Themes | Valid XHTML and CSS | Subscribe to RSS