Database space doesn't match ibdata1 size

I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table.

In the MySql workbench, when I query for the databases used space, with this query

SELECT table_schema "Database", sum( data_length + index_length ) / 1024 / 1024 "Data     Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema;

I get 47 GB.
However, when I check the space on the /var directory with df -h, it returns 129 GB of used space. Last week, this was at 127 GB, so I know that the whole 129 GB is used because new space is allocated by MySql.

I was under the impression that when deleting rows from a table, the space was freed, but the ibdata1 does not skrink and re-use the space latter.

So my question is, why does ibdata1 keep growing to 129 GB and beyond when, supposedly, my databases only uses 47 GB of space?

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

• Apr 01, 2012 : Is innodb_file_per_table advisable?

• Mar 25, 2012 : Why does InnoDB store all databases in one file?

• Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB

• Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

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.

StackExchange Database Administrators Q#31545, answer score: 2