MySQL Forums
Forum List  »  Optimizer & Parser

Re: Way to avoid fragmentation of MySQL tables
Posted by: Peter Brawley
Date: July 30, 2017 04:41PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Way to avoid fragmentation of MySQL tables
206
July 30, 2017 04:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.