Re: Is my data fragmented?

[Rick James]

Is your data fragmented? I will counter with "Does it matter"?

There are several ways in which InnoDB will fragment. Most of them are relatively harmless and/or uncontrollable.

InnoDB uses BTrees for structuring data and indexes. BTrees inherently have lost space within the blocks, plus scattering of the blocks on disk. InnoDB tries somewhat to recombine pairs of adjacent, half full, blocks.

If you are using file_per_table (I guess you are), then each table is a file containing one or more BTrees. It will be one BTree for the data and PRIMARY KEY plus one BTree per secondary key.

Blocks are 16K (at least in older versions).

A "block split" can lead to one block in the middle of the table turning into two blocks located separately.

The file is auto-extended in large chunks. This leads to temporary wasted space at the end of the table. (Where "end" is in the Operating Sense -- remember, the OS will _also_ scatter blocks around the disk as it sees fit.)

I cannot explain the 64. Nor can I explain the 4096kB, except to note there a lot of 0s (unexpected), and that the non-zero values are all multiples of 1MB. This disagrees with my comments about 16KB blocks.

Armed with that insight into the inner workings of InnoDB, why do you care about fragmentation?

Blocks are reused. Their locations won't be very well organized after lots of activity on a table.