A friend needed to analyze slow queries and for that he decides to use pt-query-digest tool. Though he got stuck at exporting slow queries from slow logs in RDS for two problems: encrypted data and slow logs in mysql table.<\/p>\n\n\n\n
1. The queries are hexed in sql_field and unreadable when he executed SELECT from mysql.slow_log.<\/p>\n\n\n\n
start_time: 2023-05-30 06:52:46.808701\n insert_id: 0\nlast_insert_id: 0\n sql_text:<\/strong> 0x53454C454354202A2046524F4D206D795F7461626C65205748455245206964203D2031323331<\/strong><\/code><\/pre>\n\n\n\n2. The default log_output is set to “TABLE” and hence he couldn’t download the slow log. Note that this is unlike standard MySQL where default log_output is FILE.<\/p>\n\n\n\n
Unreadable slow query in mysql slow_log<\/h3>\n\n\n\n
Is that mysql table for slow log encrypted? No!<\/p>\n\n\n\n
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'slow_log';\n+--------------+------------+----------------+\n| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |\n+--------------+------------+----------------+\n| mysql | slow_log | |\n+--------------+------------+----------------+<\/pre>\n\n\n\nThe table definition for slow_log shows that the sql_text is a mediumblob column that’s storing the text of slow queries.<\/p>\n\n\n\n
CREATE TABLE `slow_log` (\n`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),\n`user_host` mediumtext NOT NULL,\n`query_time` time(6) NOT NULL,\n`lock_time` time(6) NOT NULL,\n`rows_sent` int(11) NOT NULL,\n`rows_examined` int(11) NOT NULL,\n`db` varchar(512) NOT NULL,\n`last_insert_id` int(11) NOT NULL,\n`insert_id` int(11) NOT NULL,\n`server_id` int(10) unsigned NOT NULL,\n`sql_text` mediumblob NOT NULL,<\/strong>\n`thread_id` bigint(21) unsigned NOT NULL\n) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'<\/pre>\n\n\n\nTo overcome the challenge of unreadable MySQL slow query log, we can utilize the CONVERT function to convert the binary sql_text field to a readable format, such as UTF-8 encoding.<\/p>\n\n\n\n
Use the CONVERT function to fetch readable records from the slow_log table.<\/p>\n\n\n\n
mysql> SELECT CONVERT(sql_text USING utf8)<\/strong> FROM mysql.slow_log LIMIT 1;\n+----------------------------------------+\n| CONVERT(sql_text USING utf8) |\n+----------------------------------------+\n| SELECT * FROM my_table WHERE id = 1231 |\n+----------------------------------------+\n1 row in set (2.71 sec)<\/code><\/pre>\n\n\n\nExporting or Parsing MySQL slow logs to regular file<\/h2>\n\n\n\n
To export the slow query log from the table (mysql.slow_log) to a regular log file, use the following command:<\/p>\n\n\n\n
mysql -h RDS_ENDPOINT -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', CONVERT(sql_text USING utf8)<\/strong>, ';') FROM mysql.slow_log LIMIT 10\" > rds_slow_log.log<\/code><\/pre>\n\n\n\nThis command exports the slow logs to the rds_slow_log.log file, containing formatted log entries.<\/p>\n\n\n\n
Finally you may run the pt-query-digest against the log file and you should be ready to start your query analysis.<\/p>\n\n\n\n
MySQL log_output on AWS<\/h2>\n\n\n\n
It is recommended to use the log_output as FILE instead of TABLE. This is because writing logs to a file is more efficient, reliable, performant and simpler than logging to a table<\/p>\n\n\n\n
AWS provides you with tools to extract the slow logs thus generated using the AWS CLI <\/p>\n\n\n\n
aws rds download-db-log-file-portion<\/code><\/pre>\n\n\n\n