How to check fragmentation in MySQL tables
MySQL tables, including MyISAM and InnoDB, two of the most common types,
experience fragmentation as data is inserted, updated and deleted randomly.
Fragmentation can leave large holes in your table, blocks which must be
read when scanning the table. Optimizing your table can therefore make
full table scans and range scans more efficient.
We will mention how to check fragmentation in MySQL tables using SQL queries.
USE <put_your_dbname_here>
The DATA_LENGTH and INDEX_LENGTH are the space your data and indexes are using, and DATA_FREE is the total amount of bytes unused in all the table pages (fragmentation).
If the ratio of DATA_FREE and (DATA_LENGTH + INDEX_LENGTH) is low then it is not beneficial to optimize the table.
Please note that the data_free is a stat for the whole table space, not for the respective table. If you store multiple tables together in one table space, the data_free can mislead you to believe the table needs de-fragmenting, when it just means there are free extents in the table space. Running optimize table won't reduce the free extents. De-fragmenting the table may even increase the free extents.
Optimize the table to resolve the issue. Make sure that other sessions are not accessing the table that is being optimized. You can stop application and other external processes to accomplish the same. Optimizing a large table can be time consuming. You can get a timeline by executing the same on a cloned staging environment.
NOTE
The queries generate approximate results for InnoDB as it does not store exact statistics.
We will mention how to check fragmentation in MySQL tables using SQL queries.
USE <put_your_dbname_here>
SELECT TABLE_NAME,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE,
CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE
FROM information_schema.TABLES
where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00
ORDER BY DATA_FREE DESC;
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') TOTAL_SIZE,
CONCAT(ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2), 'M') DATA_FREE
FROM information_schema.TABLES
where table_schema = database() and ROUND(( DATA_FREE ) / ( 1024 * 1024 ), 2) > 0.00
ORDER BY DATA_FREE DESC;
SELECT TABLE_NAME,
ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent
FROM information_schema.TABLES
where table_schema = database() and TABLE_ROWS > 0 and Avg_row_length > 0 and ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) > 2.00
ORDER BY FRAG_Percent DESC;
ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) FRAG_Percent
FROM information_schema.TABLES
where table_schema = database() and TABLE_ROWS > 0 and Avg_row_length > 0 and ROUND(((Data_length - (TABLE_ROWS * Avg_row_length))/Data_length) * 100, 2) > 2.00
ORDER BY FRAG_Percent DESC;
The DATA_LENGTH and INDEX_LENGTH are the space your data and indexes are using, and DATA_FREE is the total amount of bytes unused in all the table pages (fragmentation).
If the ratio of DATA_FREE and (DATA_LENGTH + INDEX_LENGTH) is low then it is not beneficial to optimize the table.
Please note that the data_free is a stat for the whole table space, not for the respective table. If you store multiple tables together in one table space, the data_free can mislead you to believe the table needs de-fragmenting, when it just means there are free extents in the table space. Running optimize table won't reduce the free extents. De-fragmenting the table may even increase the free extents.
Optimize the table to resolve the issue. Make sure that other sessions are not accessing the table that is being optimized. You can stop application and other external processes to accomplish the same. Optimizing a large table can be time consuming. You can get a timeline by executing the same on a cloned staging environment.
NOTE
The queries generate approximate results for InnoDB as it does not store exact statistics.
Comments
Post a Comment