How can I *actually* defragment my InnoDB tables?

Posted on

Question :

After a recommendation by mysqltuner I’ve run mysqlcheck --optimize --all-databases successfully (all of the tables reported “note : Table does not support optimize, doing recreate + analyze instead” and “status : OK”), but my tables are still fragmented. I ran this query:

SELECT engine,
       ROUND(data_length/1024/1024) AS data_length,
       ROUND(index_length/1024/1024) AS index_length,
       ROUND(data_free/1024/1024) AS data_free
  FROM information_schema.tables;

and found that about half of my tables are still fragmented. Several of them are even more than 100% fragmented (i.e., data_free > data_length + index_length). Why would they still be fragmented, and how can I ensure defragmentation without recreating the entire database?

ibdata1 is just 82 MB (the entire DB is > 100 GB), and I’ve got innodb_file_per_table = on set. I also tried ALTER TABLE schema_name.table_name ENGINE=InnoDB; on one table to check whether mysqlcheck was at fault, but this didn’t change the file size noticeably.

Running MariaDB 5.5.

Answer :

You can either recreate your table or use “optimize table” command to rebuild your table. Highly recommend making a backup just in case.

Leave a Reply

Your email address will not be published. Required fields are marked *