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.