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.
