22 Oct
InnoDB tablespace fragmentation (find it and fix it!)
If you frequently delete rows (or update rows with variable-length data types), you can end up with a lot of wasted space in your data file(s), similar to filesystem fragmentation.
If you’re not using the innodb_file_per_table option, the only thing you can do about it is export and import the database, a time-and-disk-intensive procedure.
But if you are using innodb_file_per_table, you can identify and reclaim this space!
Prior to 5.1.21, the free space counter is available from the table_comment column of information_schema.tables. Here is some SQL to identify tables with at least 100M (actually 97.65M) of free space:
SELECT table_schema, table_name, table_comment FROM information_schema.tables WHERE engine LIKE 'InnoDB' AND table_comment RLIKE 'InnoDB free: ([0-9]{6,}).*';
Starting with 5.1.21, this was moved to the data_free column (a much more appropriate place):
SELECT table_schema, table_name, data_free/1024/1024 AS data_free_MB FROM information_schema.tables WHERE engine LIKE 'InnoDB' AND data_free > 100*1024*1024;
You can reclaim the lost space by rebuilding the table. The best way to do this is using ‘alter table’ without actually changing anything:
ALTER TABLE foo ENGINE=InnoDB;
This is what MySQL does behind the scenes if you run ‘optimize table’ on an InnoDB table. It will result in a read lock, but not a full table lock. How long it takes is completely dependent on the amount of data in the table (but not the size of the data file). If you have a table with a high volume of deletes or updates, you may want to run this monthly, or even weekly.
“…It will result in a read lock, but not a full table lock…”
My understanding was that all ALTER TABLE commands used a full table lock. Is that mistaken?
I think it is a mistake to strongly correlate “free space” with fragmentation – many of the MySQL tuning scripts make this same error. Often there is no immediate benefit to rebuild a table (aside from perhaps getting a little space back) and in many cases rebuilding InnoDB tables will make secondary indexes more fragmented than they were initially.
I prefer the advice here:
http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/
and
http://mituzas.lt/2009/05/21/innodb-tablespace/
SELECT table_schema, table_name, table_comment FROM information_schema.tables WHERE engine LIKE ‘InnoDB’ AND table_comment RLIKE ‘InnoDB free: ([0-9]{6,}).*’;
AND
SELECT table_schema, table_name, data_free/1024/1024 AS data_free_MB FROM information_schema.tables WHERE engine LIKE ‘InnoDB’ AND data_free > 100*1024*1024;
gives the freespace available but not the Fragmentation !!!
youcan caliculate fragmentation with the help of
( (data length + index length) – (rows*avg row length ) )/1024/1024/1024 GB
For what it’s worth, you can optimize tables if you’re not using innodb_file_per_table. It’s just harder to figure out whether or not the database is fragmented.
Wow … nice tip … i would never have thought of that.
thanks
It’s a good post for InnoDB tablespace fragmentation. But be careful, while running the command.
ALTER TABLE foo ENGINE=InnoDB;
If the table size is very big (TBs). It will leave you in trouble.
Regards,
Krishna