3

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?

  • I don't think it's restart. When RDS rotates logs it runs FLUSH LOGS – akuzminsky Aug 4 '14 at 4:13
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.

| improve this answer | |
  • 1
    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. – chris Jan 17 '14 at 13:13
  • i agree this is a space concern, but i believe this answers your question above – Mike T Sep 24 '15 at 19:46
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.