The optimal setting of the compressed page size depends on the type and distribution of data that the table and its indexes contain. The compressed page size should always be bigger than the maximum record size, or operations may fail as noted in Section 3.4.2.1, “Compression of B-Tree Pages”.
Setting the compressed page size too large wastes some space, but the pages do not have to be compressed as often. If the compressed page size is set too small, inserts or updates may require time-consuming recompression, and the B-tree nodes may have to be split more frequently, leading to bigger data files and less efficient indexing.
Typically, one would set the compressed page size to 8K
or 4K bytes. Given that the maximum InnoDB record size is
around 8K, KEY_BLOCK_SIZE=8
is usually a
safe choice.
This is the User’s Guide for InnoDB Plugin 1.0.6 for MySQL 5.1, generated on March 4, 2010 (rev 673:680M).