Random insertions or deletions in a secondary index can lead to fragmentation, where the physical order of index pages on disk no longer aligns with the logical order of records. A common sign of fragmentation is that a table occupies more space than expected, though determining the exact amount is challenging. InnoDB stores data and indexes in B-trees, with fill factor ranging from 50% to 100%, contributing to this variability.
Monitoring Fragmentation
Fragmented tables typically consume more disk space than expected. They can also cause query slowness. The following query can help identify fragmentation in tables along with the OPTIMIZE command to be run:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT ENGINE, TABLE_SCHEMA, 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, ( ROUND(DATA_FREE / 1024 / 1024)/( ROUND(DATA_LENGTH / 1024 / 1024) + ROUND(INDEX_LENGTH / 1024 / 1024) ) )* 100 AS Fragmentation_Ratio, CONCAT( 'OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';' ) AS Optimize_Command FROM information_schema.tables WHERE DATA_FREE > 0 ORDER BY data_free; |
Defragmenting Tables
To defragment tables, use the OPTIMIZE TABLE command or rebuild the table using ALTER TABLE. For example:
|
1 2 |
ALTER TABLE <database-name>.<table_name> ENGINE=InnoDB; OPTIMIZE TABLE <database-name>.<table_name>; |
Note: These operations can be time-consuming. If concurrent DML operations are modifying the table, the size of the temporary online log may exceed the innodb_online_alter_log_max_size configuration. When this limit is reached, a DB_ONLINE_LOG_TOO_BIG error occurs, interrupting the operation. It’s essential to monitor and adjust this setting as needed to prevent issues during large table alterations.
Alternative Approach: pt-online-schema-change
To avoid potential errors during table alterations, consider using the pt-online-schema-change tool. This tool modifies a copy of the table while allowing the original table to remain accessible for reads and writes. It works by creating a new table, applying the desired changes, and then migrating data from the original table to the new one. Once the process is complete, the original table is swapped with the new version. By default, the original table is dropped. Here’s an example:
|
1 |
pt-online-schema-change --user=root --password=your_password --alter "ENGINE=InnoDB" D=test,t=your_table --execute |
Conclusion
Regularly monitoring and optimizing fragmented tables is essential to maintaining MySQL performance and avoiding wasting disk usage.