4

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.

Improve this question
1
  • what are the actual results for the query? after you run mysqlcheck --optimize
    – Evan Carroll
    Jan 13, 2018 at 22:26

1 Answer 1

Reset to default
1

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/

Improve this answer
2
  • The former is not an option because of the size, and the latter I've already done (via mysqlcheck)
    – l0b0
    Mar 14, 2017 at 22:08
  • 1
    After optimization, the free space is contiguous within the InnoDB tablespace and available for future growth (MySQL/MariaDB does not reduce the size of the .ibd file once space has been allocated). This free space does not degrade performance and does not constitute table fragmentation. If you need the disk space, the only choice you have is by creating a new table (which creates a new tablespace). Complexity is more of a concern than size. You need to revalidate/recreate everything associated with the table (i.e., referencing constraints, triggers, functions, procedures, etc...).
    – RMathis
    Mar 15, 2017 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.