Amazon – RDS High Disk Usage & Logs

Recently we took over support of an Amazon solution. It consisted of some web servers(EC2) and a database server(RDS).

When we looked at the RDS disk size using CloudWatch – we saw it was eating up about 5 gigabytes a day. This seemed really high, as the databases were WordPress and there were very few writes to them.

I first checked to see the changes in database size, I did this over a few days and saw very little change in database size.

mysql> SELECT table_schema “database_name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”, sum( data_free )/ 1024 / 1024 “Free Space in MB” FROM information_schema.TABLES GROUP BY table_schema;
+——————–+———————-+——————+
| database_name      | Data Base Size in MB | Free Space in MB |
+——————–+———————-+——————+
| db1                |         372.34188843 |      53.18891907 |
| db2                |          65.07812500 |      27.00000000 |
| db3                |          44.43750000 |      13.00000000 |
| information_schema |           0.00878906 |       0.00000000 |
| mysql              |           5.35545349 |       0.00000000 |
| performance_schema |           0.00000000 |       0.00000000 |
+——————–+———————-+——————+

I then checked for Slow Query logging, it was enabled, but the table was fairly small. So it could not have been the slow query log causing it.

With much searching on the internet, I found that many people had a similar issue and they have found that the General Log had been enabled and was quite large. (The General Log is not normally used). This had been enabled by the previous hosts for them to troubleshoot an issue months ago and they had never disabled it…

The General Log – “The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.”

Luckily the Amazon RDS team has created a stored procedure to rotate those logs:

CALL mysql.rds_rotate_slow_log;

CALL mysql.rds_rotate_general_log;

The above commands will move the data into a table with “_backup” in the name.
I wanted to totally empty that table and not keep the backup copy – so you run the above commands TWICE. In our case that freed up over 100 gigabytes!