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