The InnoDB compression supports different compression levels. The innodb_compression_level server variable is an integer value in the range between 0 and 9. A higher value represents a more efficient, but slower, compression level. The default value is 6. This variable is dynamic and can be changed at any time.

As mentioned previously, when an index entry is read from the disk, it is written into the buffer pool in both its compressed and uncompressed forms. InnoDB tries to avoid performing a huge number of compression and uncompression operations in several ways. For example, it can modify a compressed page without compressing the new data until the page needs to be flushed onto the disk. To do this, it keeps a track of such changes in a page area called the modification log, which is uncompressed. Updates and small row insertions can often be applied without recreating the page.

Of course, each page's modification log has a limited space. If InnoDB tries to write a change into the log but it runs out of space, the page is uncompressed, logged changes are applied, and the page is compressed again. In some cases, the page becomes too large after the changes take place, and so, the data needs to be reorganized to fit the page size. When the new page results are too large, a compression failure happens.

Rebuilding the compressed pages takes time, thus InnoDB tries to avoid compression failures if they happen too often. If the ratio between compression failures and changes applied exceeds the value of the innodb_compression_failure_threshold_pct server variable, InnoDB leaves an empty space at the end of each new compressed page. The innodb_compression_failure_threshold_pct server variable is a percentage and its default value is 5. The innodb_compression_pad_pct_max server variable specifies the maximum percentage of the free space that can be left in each compressed page. The allowed range is between 0 and 75 and the default value is 50. If any of these variables is set to 0, this optimization is disabled.

If compression failures happen too often for a single table, that table's KEY_BLOCK_SIZE is probably too low. If the performance of the compressed tables is slow and is caused by compression failures, innodb_compression_failure_threshold_pct should probably be increased. If a typical update can dramatically increase the size of a row, innodb_compression_pad_pct_max should be set to a high value.

The next section shows how to monitor the compressed table's performance, both globally and on a table basis.