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.

  • what are the actual results for the query? after you run mysqlcheck --optimize – Evan Carroll Jan 13 '18 at 22:26
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/

  • The former is not an option because of the size, and the latter I've already done (via mysqlcheck) – l0b0 Mar 14 '17 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 '17 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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