This sounds a lot like another post I wrote back on August 27, 2012 : How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed
With innodb_file_per_table disabled, the system tablespace ibdata1 is the home of seven classes of information:
- Data Pages for InnoDB Tables
- Index Pages for InnoDB Tables
- Data Dictionary
- Double Write Buffer
- Safety Net to Prevent Data Corruption
- Helps Bypass OS for Caching
- Insert Buffer (Streamlines Changes to Secondary Indexes)
- Rollback Segments
- Undo Logs
- Click Here to see a Pictorial Representation of
ibdata1
With innodb_file_per_table disabled, running OPTIMIZE TABLE against any InnoDB table is really asking for trouble. Why? Because all OPTIMIZE TABLE does is write all data pages and index pages for a given table contiguously in ibdata1. That makes ibdata1 grow. In light of this, shrinkage of ibdata1 is totally impossible.
Please note that even if innodb_file_per_table were enabled and you ran OPTIMIZE TABLE, that will extract the table into an external file. Yet, the space left behind is unrecoverable.
I wrote a nice one-time Cleanup Procedure of ibdata1 in StackOverflow back on Oct 29, 2010.
Here are my other posts on this subject of InnoDB and its effects on ibdata1
SUMMARY
Even if you implement the InnoDB Cleanup (which separates all data and index pages from ibdata1), ibdata1 can still grow in a heavy-write, heavy transaction environment due to the 5 other classes of information (Data Dictionary, Double Write Buffer, Insert Buffer, Rollback Segments, Undo Logs).
If you want to know how much space is used by data and index pages in ibdata1, run this:
SELECT InnoDB_Bytes,InnoDB_Bytes/POWER(1024,3) InnoDB_GB
FROM (SELECT SUM(data_length+index_length) InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
Now as for ibdata1, it still the Data Dictionary, Double Write Buffer, Insert Buffer, Rollback Segments, Undo Logs, and unused pages due to fragmentation. There is no really way to know:
- the exact amount of fragmentation
- how much space is used by the following (since they change rapidly):
- Data Dictionary
- Double Write Buffer
- Insert Buffer
- Rollback Segments
- Undo Logs
For the sake of simplicity, I will say this: Just subtract InnoDB_Bytes from the filesize of ibdata1.
After doing the InnoDB Cleanup, you should schedule running OPTIMIZE TABLE on every InnoDB table that is transaction-heavy. That will actually shrink the .ibd file for every InnoDB tables. The ibdata1 file will grow much slower thereafter. Yet, you will still have to live with some transactional growth.
If you implement InnoDB Cleanup but leave innodb_file_per_table disabled, that will shrink shrink ibdata1, but it will just climb back to 99G and beyond through normal application usage.
If you do not implement InnoDB Cleanup, no OPTIMIZE TABLE will ever shrink ibdata1.
RECOMMENDATION
Please implement InnoDB Cleanup with innodb_file_per_table enabled. Going forward, you should create a cronjob to run OPTIMIZE TABLE on all tables what experience mass INSERTs, mass UPDATEs, and mass DELETEs.
