7

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?

  • What is the actual alert that tells you that you have a lack of storage? – Reaces Mar 19 '15 at 10:24
  • I can't remember the error now. Attempting to connect to run select queries after having connected to mysql would give errors clearly outlining it to be storage related. I googled as well and confirmed. – Laykes Mar 19 '15 at 10:49
  • 1
    Hello @Laykes, did you find the cause of growing disk usage? I have the exact problem but couldn't find solution yet. – Cenk Alti Jul 18 '16 at 11:27
  • @CenkAlti No, I did not. I just increased the disk size masively. – Laykes Jul 20 '16 at 8:35
  • Do you have a master-slave model in your infrastructure? Would it be the usage for relay log? Also, which MySQL version do you use? – Simon MC. Cheng Aug 8 '16 at 16:41
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.

  • original question already states that they are only about 2Mb – Cenk Alti Aug 8 '16 at 14:51
0

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

  • 1
    This could be the cause. MySQL is horrible at reclaiming space from deleted rows. Recreating the tables will reclaim this space. I HIGHLY recommend enabling "innodb_file_per_table" if possible. This avoids fragmenting the system tablespace. – RMathis Apr 5 '17 at 16:36

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.