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?
Best Answer
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: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 makesibdata1
grow. In light of this, shrinkage ofibdata1
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 InnoDBNov 26, 2011
: ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is fullSUMMARY
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:
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:
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. Theibdata1
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 shrinkibdata1
.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.