MySQL Fragmentation Levels

Fragmentation in InnoDB tables quietly eats disk space and can slow down reads over time. The good news is that a couple of quick queries against information_schema give you a clear picture of how much free space each table is carrying, so you can decide whether an OPTIMIZE TABLE is actually warranted.

Checking Fragmentation

The first query pulls the data length, index length, and free space for every table that has any free space at all:

SELECT ENGINE,
       TABLE_NAME,
       ROUND(DATA_LENGTH / 1024 / 1024) AS data_length,
       ROUND(INDEX_LENGTH / 1024 / 1024) AS index_length,
       ROUND(DATA_FREE / 1024 / 1024) AS data_free
FROM information_schema.tables
WHERE DATA_FREE > 0;

A second, slightly more readable variant formats the output in megabytes and excludes the system schemas:

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CONCAT(ROUND(data_length / (1024 * 1024), 2), 'MB') AS DATA,
       CONCAT(ROUND(data_free / (1024 * 1024), 2), 'MB') AS FREE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql')
  AND DATA_FREE > 0;

Example Output

Here is what a real production table might look like:

Interpreting the Ratio

In this case the table uses (896 + 316) = 1212 MB of actual data and index space, with 5 MB of free space. The fragmentation ratio is:

5 / 1212 = 0.0041

That is a very low fragmentation ratio, well under 1 percent. In practice, a ratio below 0.10 (10 percent) is generally considered healthy. Once the ratio climbs above 0.20 (20 percent) or you notice query performance degrading, running OPTIMIZE TABLE on the affected table is a reasonable next step.

• Bigdata

• Sql
• SysAdmin
• Mysql