3

We are using an Amazon RDS instance and it ran out of free storage space. Looking into the issue we dropped a no-longer required table. Amazon's monitoring tool showed that 1Gb of space was now available.

Further investigation showed that another table had 7 million entries, so we removed 5 million of them (using a simple delete command). However now the Free Storage Space has dropped to almost 1/2 Gb. We are dangerously close to running out of space again.

I (naively) assumed that deleting entries from a table would reclaim space. Why is this not the case? Do I need to perform some form of manual cleanup operation? Why did performing the delete cause nearly 1/2 Gb of free space to be used (looking at the table the size was 2677Mb before the delete and is now 2961Mb!)

Thanks,

Phil

Phil
  • 1,897
  • 4
  • 25
  • 48
  • MySQL will keep the space reserved after deletion, but you can insert new rows in that space. Also, deletes will be logged, which may cause the that extra space to be used. For generic info about deleting rows see [this question](http://stackoverflow.com/questions/634257/does-the-space-occupied-by-deleted-rows-get-re-used). For more info about log files and such on AWS, see [this document](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html) – GolezTrol Jan 07 '15 at 11:57

1 Answers1

0

You may have unused indexes in your database which maybe taking up space. Percona makes tools and patches for MySQL to collect index usage data.

Check these links:

You can also have a look at these:

I'll recommend to use performance schema provided you use MySQL version 5.6 or higher. Also you can have look at MONyog- Mysql Monitoring tool. It include tools to monitor different performance metrics of the MySQL server, there's a disk info feature in it which might help you in this issue.

sid1408
  • 404
  • 3
  • 10