I have delete database from mysql but storage is not freed

Question

I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage?

I have a mysql database that the total storage is 15GB. After adding multiple databases (copied from another mysql database) and deleting them the free storage available is 0. I can not simply delete the RDS instance as there is another database on this instance that I need it. but I am 100% sure that the database is not using 15GB of storage.

I have read about this issue on other threads but most of the solutions where talking about deleting all the data and shutting down the server which I can't do as the database is being used all the time.

Answer 1

You may find this surprising, but there are lots of little transactions that occur inside ibdata1. I wrote about this back on Jan 14th : AWS RDS showing large growth in Write Ops/sec, despite low # DB connections?

Even though MySQL 5.6 has innodb_file_per_table on by default, the only way ibdata1 can grow is by having lots of transactions. If you look at this diagram:

you will see that ibdata1 has 1023 rollback segments for supporting MVCC and Transaction Isolation. In a heavy-write DB environment, ibdata1 can grow. I have discussed this before in my old post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

In that post, I mentioned the following:

According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

• Lots of Transactional Changes
• Very Long Transactions
• Lagging Purge Thread

Thus, I would expect ibdata1 to outgrow your current disk allocation if you chose a number too small.

BTW Where does the ibdata1 and the transaction logs actually reside ?

When you run show variables like 'innodb%home_dir'; you find those two folder

• /rdsdbdata/db/innodb
• /rdsdbdata/log/innodb

You cannot just reach into it and expand it that easily.

Just to make sure you have nothing of yours in the database, run this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

If you get nothing back, then you can reasonable assert what I just said. Otherwise, go delete your data.

You may want to check for binary logs as well. Just run one of the following:

SHOW BINARY LOGS;
SHOW MASTER STATUS;

You may or may not have rights to purge them. Amazon may not want you to touch them since they control binary logging for the sake of spinning up read-only Slaves in the cloud.

SUGGESTIONS

• Get more disk space allocated for the RDS Instance (if possible).
• You may need to switch to a higher server model and then migrate.
• Get away from RDS and switch to EC2 where you can micromanage everything, but you must shoulder the responsibility for monitoring the MySQL instance.
• Contact Amazon for any pricing before you do anything (switching server models, migration to another RDS Instance, migration to EC2, etc).