Mysql – How to *actually* defragment the InnoDB tables

fragmentationinnodbmariadbMySQL

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,
       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
  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.

Best Answer

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

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-storage-layout.html https://mariadb.com/kb/en/mariadb/optimize-table/

Related Question