Amazon RDS Instance slowly losing free diskspace

Question

A while back the server running my instance had 20GB of EBS storage. It then started getting disk storage errors, so I increased it to 40GB. Then again, storage missing errors so I increased again to 60GB. (So this is a 60GB RDS Instance)

You can see here the Free Storage Space (MB) chart. Each time it shoots up I add more storage space..

If I run this query..

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

I get the following response...

Nothing stands out as taking up a huge amount of space.

If I then run

select table_schema, CONCAT(ROUND( sum((data_length+index_length)/1024/1024)/1024, 2), 'G') AS MB from information_schema.tables group by 1;

I can see that the largest table has about 10GB. (This includes data_length and index_length )

My next thought is that the slow low in storage is general_log or slow query logs writing to disk...

If I check the parameter groups on my RDS instance I can see that logging is disabled.

Does anyone have any idea why my RDS server is slowly leaking storage?

---

UPDATE:

I got some help from the kind folk on #mysql

After running

show global variables like 'log_bin';

It was clear that binary logs were enabled.

I then ran

show binary logs and had 41674+ logs.

Scrolling down through my logs I could see one of the file size was 2064636

I then attempted to delete all the binary logs up to that changelog file.

purge binary logs to "mysql-bin-changelog.152193"

However RDS does not provide File_priv or Super_priv to the master user.

I would like to think that this is where the diskspace has gone.. however, 2064636 is only about 2Mb... So back to the drawing board?

Answer 1

You can check your binary log retention period by the following command under MySQL:

mysql> call mysql.rds_show_configuration;

To set the retention period to 1 day, use the command:

mysql> call mysql.rds_set_configuration('binlog retention hours', 24);

Which means MySQL servce will purge binary log every day.

Besides, the binary log, I think you can check the setting of innodb_file_per_table, all data will be stored to ibdata file if it is assigned with "0".

I suggest you to assign the option to "1" so your table will be stored separately and you can reclaim your used space by:

mysql> OPTIMIZE TABLE <table_name>

Check the reference links below to see if they reflect your problem:
How to reclaim space in InnoDB when innodb_file_per_table is ON

Why is the ibdata1 file continuously growing in MySQL?

One more thing, according to the website, you will have fast growing ibdata1 file because you have long running transaction. Try to commit them as soon as possible to avoid performing system restore to reclaim space.

Answer 2

I think the lost free space resides at the tablespace files of InnoDB storage engine. After talking with AWS support they suggested me to recreate them by setting up a new instance by dumping all data and importing to this new instance, then switch. Creating a read replica does not work in this case because read replicas use same storage with the master instance when being created.

In this process I had to setup replication manually. I followed the guide here (http://www.ruempler.eu/2014/06/15/external-non-mysql-slaves-with-rds-reloaded/) and created a script to automate this process. You can find it at:

https://github.com/cenk/darbe