Exporting the mysql.slow_log table into slow query log format

Posted in: Technical Track

Using pt-query-digest is an excellent way to perform a SQL review. However, sometimes you don’t have access to the slow_query_log_file. For example, when MySQL runs on Amazon RDS, the slow_query_log_file is unavailable (see the RDS FAQ). To get around this, export the mysql.slow_log table.

To export the data, run the following SQL command from an instance that has database access. The data is exported into a MySQL slow-log format, which pt-query-digest can analyze:

mysql -u user -p -h host.rds.amazonaws.com -D mysql -s -r -e “SELECT CONCAT( ‘# Time: ‘, DATE_FORMAT(start_time, ‘%y%m%d %H%i%s’), ‘\n’, ‘# User@Host: ‘, user_host, ‘\n’, ‘# Query_time: ‘, TIME_TO_SEC(query_time),  ‘  Lock_time: ‘, TIME_TO_SEC(lock_time), ‘  Rows_sent: ‘, rows_sent, ‘  Rows_examined: ‘, rows_examined, ‘\n’, sql_text, ‘;’ ) FROM mysql.slow_log” > /tmp/mysql.slow_log.log

The -s (–silent) option is used to suppress echoing of the query.

The -r (–raw) option is used to disable character escaping making \n into an actual newline, otherwise it’s echoed as ‘\n’.

Once the export is complete, run pt-query-digest to do the analysis. A simple review command is:

pt-query-digest –limit 100% /tmp/mysql.slow_log.log > /tmp/query-digest.txt

query-digest.txt is now ready for review.

email

Interested in working with Charlie? Schedule a tech call.

3 Comments. Leave new

Thanks for making this public – I was struggling to get a large slow log into a format I could analyze.

I had to make 2 adjustments to the text of the query you have published. I had to convert the quotes to simple quotes, since on the website they are smart quotes that don’t work in mysql.
The query returned “Killed” when I ran it the first time, and I had to set a limit of 500,000 rows to get it to complete.

Reply

This is brilliant and still actual after 6 years. Thank you!

Reply
Harjit Lakhan
December 3, 2020 7:45 am

I had to change the quotes to make it work in bash:

mysql -u $USER -p -h $HOST -D mysql -s -r -e “SELECT CONCAT( ‘# Time: ‘, DATE_FORMAT(start_time, ‘%y%m%d %H%i%s’), ‘\n’, ‘# User@Host: ‘, user_host, ‘\n’, ‘# Query_time: ‘, TIME_TO_SEC(query_time), ‘ Lock_time: ‘, TIME_TO_SEC(lock_time), ‘ Rows_sent: ‘, rows_sent, ‘ Rows_examined: ‘, rows_examined, ‘\n’, sql_text, ‘;’ ) FROM mysql.slow_log limit 1” > slow.log

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *