Re: Way to avoid fragmentation of MySQL tables

[Peter Brawley]

You can track 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;

I don't often see values north of 5%, never worry about values > 10%. If you have some, have a look at https://www.percona.com/blog/2009/11/05/innodb-look-after-fragmentation/.



Edited 1 time(s). Last edit at 08/11/2017 09:07AM by Peter Brawley.