We have some tables that have many deletes.
I used to defragment these tables with the command OPTIMIZE TABLE;
Besides rebuilding the tables, that would always reduce the size of the tables in disk.
I now tried the ‘on-line’ version of OPTIMIZE TABLE with pt-online-schema-change --alter “ENGINE=InnoDB”
All worked fine.
Except one thing… Some tables actually increased in size.
Tools ran without errors, no _temp tables where left behind. (scheme did not change, tables already had Innodb as ENGINE)
A) total diskusage increased on server. (I mean AFTER the tool finished, not during execution).
B) table size as reported by information_schema.tables data_length
I can’t understand why that would happen. Any clue someone ?