3

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?

2

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.

| improve this answer | |
  • Thanks for the complete answer. I ran your sql command and I get 47 GB, which is consistent with what I had found. That means that I have 47 GB of actual data in a ibdata1 file of 99 GB. Is this normal? I often delete millions of rows from different table to clean up space. Can this be a problem? Also, my database is used in a write-heavy environment (several hundreds of write per second). – Mathieu Savaria Jan 7 '13 at 16:58
  • @RolandoMySQLDBA, if I do the 'InnoDB Cleanup' with innodb_file_per_table enabled, would I still have lots of huge files starting like 'FTS_*.ibd' in my database folder? I have about 240Gb data but the folder has the size of about 400Gb due to these 'FTS_*.ibd' files – SAVAFA Jan 30 '17 at 17:11
0

Effectively, DELETE order doesn't release space... You should reorganize tables with OPTIMIZE TABLE order.

If you can stop activity, you can also make a dump and restore it.

Max.

| improve this answer | |
  • One of the disadvantage to not use innodb_file_per_table is the difficulty to identify what database are problematics... – Maxime Fouilleul Jan 7 '13 at 15:16
  • Yes, that's correct. But deleted space, even if the ibdata1 file doesn't skrink should be reused. What I don't get is why my mysql/var partition keeps growing when I supposedly only have 47 GB worth of data. – Mathieu Savaria Jan 7 '13 at 15:33
  • Try the following request, it detects table which freespaces that can be removed with OPTIMIZE TABLE order : SELECT s.schema_name, table_name, IFNULL(ROUND(SUM(data_free)/1024,2),0.00) data_free FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t WHERE s.schema_name = t.table_schema GROUP BY s.schema_name, table_name HAVING data_free > 100 ORDER BY data_free DESC – Maxime Fouilleul Jan 7 '13 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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