Re: fragmentation in innodb index

[Rick James]

Data_free means one of two things...

If the table had been created with innodb_file_per_table = 0 (uniform tablespace), then Data_free says how much space is free in shared ibdata1. This value has very little use. In particular, it does not indicate that tables are "fragmented".

If the table had been created with innodb_file_per_table = 1 (table has its own tablespace), then Data_free indicates how much is free in the .ibd file for the table. For 'small' tables, it will be a multiple of 16KB. A small multiple of 16K is normal; a large multiple indicates some wasted space.

For a 'large' table, it will usually be 4M, 5M, 6M, or 7M. Any of those values means that the .ibd is as full as it can be. A larger value means that a lot of deletes (etc) have led to free blocks.

Try the Oracle SELECT I gave you; it applies to Oracle's MySQL version 5.6 (at least). Older versions have not further clues.

Another problem with chasing "fragmentation" in InnoDB is as follows. All data and indexes are stored in 16KB blocks; these blocks are scattered around the tablespace (ibdata1 or .ibd). There is essentially no way to rearrange these blocks to make them "consecutive", which is a common definition of "defragmenting".

On the other hand, DELETEing (and UPDATING, etc) rows _may_ lead to blocks being "less full" than they had been. This form of fragmentation is hard to measure; the SELECTs I provided are the only ones I know of. The values they give are hard to interpret.

One (costly) way to measure the "fragmentation" is to first do SHOW TABLE STATUS (or the equivalent information_schema query), then do
ALTER TABLE foo ENGINE=InnoDB;
After that, another SHOW TABLE STATUS will show new values for Data_length and Index_length (don't bother with Data_free); the decreases in these values will say how much free space has been recovered. I repeat that that does not help the order of the blocks, but may make the blocks less-empty.