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)
Both
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 ?
versions:
mysql 5.1.62-0ubuntu0.10.04.1
percona-toolkit-2.1.1
A) during optimize table, ibdata1 contains data for two tables if only one table gets altered; this data is not released if optimize table finishes (but it is re-used for new data); It may help to use one file per table.
B) OPTIMIZE TABLE fills each data page to 15/16 of its capacity, which is quite efficient. Your table may have had a higher average fill rate due to edits. In the worst case your table had a fill rate of 1 and OPTIMIZE TABLE increases the size by as much as 100% (if the table has only one data page). For large tables, the size may increase by at most 6.25% due to optimize table (=1/16).
Note that an increase in size is unlikely: suppose you start with a table which has 16 kB pages that are all 15/16 full. If you make a small change (< 1 kB), one of the pages reaches an even higher fill rate. However, if the change is larger (say 2 kB), the page gets split to two pages (2 times 16 kB) that contain only 17 kB of data, so the average fill rate becomes less.