Table fragmentation

from the Artful MySQL Tips List


You can track your worst cases of table fragmentation with ...

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, 
  (data_free/(index_length+data_length)) as frag_ratio 
from information_schema.tables  
where data_free > 0 
order by frag_ratio desc limit 20;

Values north of 5% aren't common, > 10% aren't very problematic. If you have high frag ratios, [url]https://www.percona.com/blog/2009/11/05/innodb-look-after-fragmentation/[/url].



Return to the Artful MySQL Tips page