Optimizing MySQL: The Slow Query Log

I prescribe to the slow and steady approach in life. It’s a proven methodology in becoming succesful (just ask the Tortoise and/or the Hare). However, slow and steady is typically not acceptable in data computation, and SQL is no different. Thus, MySQL provides a Slow Query Log.

Do you use the slow query log? Let us know your tips/stories in the comments section.

The long_query_time parameter in your MySQL configuration becomes the baseline for which queries will end up in your slow query log. If no value is specified, the default value of 10 is used. Changing the default to any number (minimum of one) will log any query taking longer than that number of seconds to execute. So, by default, any queries taking longer than 10 seconds will be logged. The execution time does not include time the query waits to receive a table lock. If your performance issues are related to an overload of requests, slow query logs will not be very beneficial.

I recommend enabling this feature on your development servers, as it will potentially shed light on problems before your application reaches production. MySQL also provides an excellent tool, mysqldumpslow, to extract the logged queries.

Some other caveats to the slow query log:

  • By default, administrative queries like OPTIMIZE TABLE, ALTER TABLE, and others are not included in the slow query log. To include these type of statements, use the --log-slow-admin-statements flag.
  • Keep the log file in a secure location, as some queries logged may include passwords or other sensitive data.
  • The slow query log will not tell you have to optimize slow queries, only that a slow query exists.
  • Slow queries are written to the log after the query has been executed and related locks released. Thus, order of queries in the slow query log may not correspond with actual execution time.
Published
Categorized as Database

By Michael Marr

Michael Marr is a staff writer for WebProNews

Leave a comment