When InnoDB compresses a page it needs the result to fit into its
predetermined compressed page size (specified with
KEY_BLOCK_SIZE). When the result does not fit we call that a
compression failure. In this case InnoDB needs to split up the
page and try to compress again. That said, compression failures
are bad for performance and should be minimized.
Whether the result of the compression will fit largely depends on
the data being compressed and some tables and/or indexes may
contain more compressible data than others. And so it would be
nice if the compression failure rate, along with other
compression stats, could be monitored on a per table or even on a
per index basis, wouldn't it?
This is where the new INFORMATION_SCHEMA table in MySQL 5.6 kicks
in. INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX provides exactly this
helpful information. It contains the following fields:
…[Read more]