October 9 2011
Logging slow queries in MySQL
It's straight-forward to log slow queries in MySQL both on your local machine and on an Amazon AWS RDS instance. I'll walk through both, although MySQL has good documentation on slow query log and log destinations.
Local MySQL
There are many blog posts about enabling slow query logging to a file within the MySQL data folder, but very few point out that, for files outside that folder, you have to manually create the log file and set its permissions correctly with touch and chown.
# Run in Terminal.
sudo touch /var/log/mysql.slow.queries.log
sudo chown mysql:mysql /var/log/mysql.slow.queries.log
# Insert the following into /etc/my.cnf.
[mysqld]
# log_slow_queries is deprecated
# log_output = FILE (use 'TABLE' if you prefer accessing slow queries through the mysql.slow_log table)
slow_query_log = 1
slow_query_log_file = /var/log/mysql.slow.queries.log
long_query_time = 1
min_examined_row_limit = 100
log_queries_not_using_indexes
# Reboot mysqld.Amazon RDS
AWS does not allow shell access, so there is no way to read a local log file. Instead, RDS logs the output (log_output = TABLE) to the built-in table: mysql.slow_log. Here are the RDS command line instructions for setting up the parameter group, modified from Inventables' excellent write-up.
# Create a test parameter group for slow query logging.
rds-create-db-parameter-group --db-parameter-group-name test-group --engine MySQL5.1 --description "Test group"
# Modify the parameter group to support UTF8 and slow query logging.
rds-modify-db-parameter-group test-group \
--parameters="name=character_set_server, value=utf8, method=immediate" \
--parameters="name=character_set_client, value=utf8, method=immediate" \
--parameters="name=character_set_results,value=utf8,method=immediate" \
--parameters="name=collation_server, value=utf8_unicode_ci, method=immediate" \
--parameters="name=collation_connection, value=utf8_unicode_ci, method=immediate" \
--parameters="name=slow_query_log, value=ON, method=immediate" \
--parameters="name=long_query_time, value=1, method=immediate" \
--parameters="name=min_examined_row_limit,value=100,method=immediate"
# Modify the EC2 instance and reboot it.
rds-modify-db-instance rds-db --db-parameter-group-name test-group
rds-reboot-db-instance rds-db
# Connect to the RDS instance after it's rebooted, execute 2s query, and check the table.
select * from mysql.slow_log \G
# Rotate the log out of the table with this command. It will move the queries to mysql.slow_log_backup.
\u mysql
call rds_rotate_slow_log
