Why is my RDS mysql slow query log full of server restarts?

Question

One of my RDS mysql databases is giving me warnings about the slow query logs taking up significant space.

The logs are rotated once an hour, and contain a dozen repetitions of:

/rdsdbbin/mysql/bin/mysqld, Version: 5.6.13-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument

What could be causing this?

• akuzminsky: I don't think it's restart. When RDS rotates logs it runs FLUSH LOGS

Answer 1

This is probably not cause for concern.

MySQL writes a new header to the file each time the logs are flushed. Presumably this is just in case you rotated the log file, so the new file will have a header... but it doesn't actually check whether it's a new file or not. The server does not have to restart to write this entry, so it doesn't mean the server is necessarily restarting.

The rdsadmin user you see in the processlist appears to be the supervisory connection that Amazon uses to monitor and manage each instance. Something -- presumably that connection -- periodically rotates the log files, most likely with some variant of FLUSH LOGS;. It sounds like the flush occurs more often than the rotate, which would exactly explain what you're seeing.

mysql> SHOW STATUS LIKE 'uptime';

This will give you the actual uptime of the instance in seconds. If that value is high, this is just the server writing a new header when the logs are being flushed to disk.

• chris: It's a bit of a concern because we are getting warnings that the slow query log is using > 20% of our allocated space. RDS does eventually purge the logs, so it doesn't cause problems, but I would like to deal with it if possible.
• Mike T: i agree this is a space concern, but i believe this answers your question above

Answer 2

Usually slow log should not grow .. if it keeps on growing then you must optimize queries and their performance add indexes wherever possible change datatypes of a table.

You can try disabling log_queries_not_using_indexes variable in db_parameter this entry will log all queries those are not using indexes.

You can specify db_parameter of log_output to TABLE

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html