A tutorial on how to enable debugging on mysql

aws_mysql_debugging.md

AWS MySQL Debugging

This tutorial provides a means to enable slow query logs on MySQL.

Setting Up Parameters

Enable these on AWS RDS Parameter Groups.

slow_query_log: 1
long_query_time: 10
log_slow_admin_statements: 1
log_slow_slave_statements: 0
log_output: FILE
min_examined_row_limit: 0

These are very dangerous in production environments, ensure that they are off/disabled, since these parameters will either fill up the database or slow it down.

general_log: 0
log_queries_not_using_indexes: 0

Verifying Work

To check that a variable is has been set with AWS via MySQL.

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

Simulate a long-running query.

mysql> select sleep(10);

Using AWS CLI

Check the slow query logs. Please see AWS RDS LogAccess for more information.

I believe the logs will all be placed in *.log initially, but will be transfered once an hour has elapsed. The destination for these logs will correspond to the 24-hour value XX and will be moved to the bin *.log.XX.

List RDS Logs

aws rds describe-db-log-files \
    --db-instance-identifier <RDS INSTANCE IDENTIFIER> \
    | less

Download RDS Logs

aws rds download-db-log-file-portion \
    --output text \
    --db-instance-identifier <RDS INSTANCE IDENTIFIER> \
    --log-file-name slowquery/mysql-slowquery.log \
    | less

Sample RDS Log

Note that Query_time is in seconds.

# Time: 180313 18:34:32
# User@Host: dashboard[dashboard] @  [10.0.0.1]  Id: 123456
# Query_time: 10.000172  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use prod;
SET timestamp=1520966072;
select sleep(10);

Troubleshooting

The test query may not be appearing in the logs because the local value for long_query_time is not updated yet. It appears that the "local" session variable will be used instead of global. Make sure that the "local" variables match global variables. Reconnect to the RDS to fix this.

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)