EmergencyEMERGENCY? Get 24/7 Help Now!

Analyzing Slow Query Table in MySQL 5.6

 | February 18, 2013 |  Posted In: Events and Announcements, Insight for DBAs, MySQL, Percona Toolkit

February 25-28, 2013 9AM - 11AM Pacific
Analyzing SQL Queries with Percona Toolkit, Feb. 25-28, 9-11 a.m. PST

Next week I’m teaching an online Percona Training class, called Analyzing SQL Queries with Percona Toolkit.  This is a guided tour of best practices for pt-query-digest, the best tool for evaluating where your database response time is being spent.

This month we saw the GA release of MySQL 5.6, and I wanted to check if any improvement has been made to the slow query log in table format.  Users of some MySQL appliances like Amazon RDS must use table logs, since they can’t access the file-based logs on the server.

I read the logging code in MySQL 5.6.10 source, in file sql/log.cc.  I discovered  that they have refactored the way they write to file-based logs versus table-based logs, but one particular feature I was looking for has not changed.

When writing slow query information to the log file, it outputs the query time and lock time as floating-point numerics to microsecond scale:

sql/log.cc, MYSQL_QUERY_LOG::write():

However, just like in prior releases of MySQL, when writing slow query information to the log table, query times and lock times are truncated to to integers:

sql/log.cc, Log_to_csv_event_handler::log_slow():

All the times recorded in slow query log tables are rounded down to the nearest whole second, meaning any query that executes in less than 1.0 seconds counts as 0 seconds.  This will skew your average query time report.

Still, if you use Amazon RDS or a similar instance without filesystem access, you may want to do a report on the query log in spite of this.  You could still get a report of the most frequent queries, even if the response time is so imprecise.  But pt-query-digest and similar tools don’t read the query log table—they only read the query log file.

To account for this, you can use a client script to dump the contents of the query log table into a flat file in the format of a conventional query log file, which you can then use as input to pt-query-digest.  I’ve seen several implementations of this type of script in the past, but here’s mine:

I host the script above in my bk-tools github project. It’s called export-slow-log-table.  I distribute it under the terms of the GNU Public License v3.

Do you want to learn more about best practices for manipulating query logs and running reports on them?  Register to join me February 25-28, 9AM-11AM Pacific Time, for my online class Analyzing SQL Queries with Percona Toolkit.

Bill Karwin

Bill Karwin has been a software professional for over 20 years. He's helped thousands of developers with SQL technology. Bill authored the book "SQL Antipatterns," collecting frequent blunders and showing better solutions.

One Comment

Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.