Exporting the mysql.slow_log table into slow query log format
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.
Share this
You May Also Like
These Related Stories
Hashing Algorithm in MySQL PASSWORD()
Hashing Algorithm in MySQL PASSWORD()
Dec 4, 2011
1
min read
MySQL log rotation
MySQL log rotation
Mar 22, 2018
3
min read
Internals of querying the concurrent requests' queue - revisited for R12.2
![](https://www.pythian.com/hubfs/Imported_Blog_Media/TheInternalWorkflowOfEBusinessSuiteConcurrentManagerProcess.png)
Internals of querying the concurrent requests' queue - revisited for R12.2
Apr 19, 2016
7
min read
No Comments Yet
Let us know what you think