Choosing the Compressed Page Size

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 the discussion of compression internals later in this section.

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.

User Comments
Sign Up Login You must be logged in to post a comment.