Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
We need a more precise description of the problem, specifically what you mean by "storage is not freed." The property referred to as "storage" on RDS is the amount of space allocated for your database, not used by your database, and this property does not change... so I assume you are talking about something else. Please describe exactly what you are seeing. –  Michael - sqlbot Feb 4 at 18:25
    
@Michael-sqlbot I have edited the question and added some details. I meant free storage that is 0 after deleting some databases. –  Navid Feb 4 at 18:50
    
I have little idea what you're talking about here but what happens when you run OPTIMIZE TABLE statements ( if you can )? –  Avarkx Feb 4 at 18:55

1 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:

InnoDB Architecture

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).
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.