Mysql – Amazon RDS Instance slowly losing free diskspace

amazon-rdsamazon-web-servicesMySQLstorage

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..

enter image description here

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…

enter image description here

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 )

enter image description here

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.

enter image description here

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

enter image description here

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?

Best Answer

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.

Related Topic