Getting InnoDB Compression Ready for Facebook Scale

The MySQL manual states that InnoDB compression works well for read-mostly
workloads. We want to use it for OLTP workloads. We improved the
monitoring and performance of it to make that possible.

We added monitoring to track the number of compressions and compression
failures per table. This allowed us to efficiently determine how well each
table compresses and decide which ones to compress in production.

Another improvement was the reduction in the amount of logging performed
by InnoDB. By default, the entire compressed page image is written to the
transaction log whenever a page is compressed. This means that
transaction logs become full sooner, log files get rotated faster and
more page writes are performed by the fuzzy checkpoint IO. We eliminated
this by introducing new log record types to represent compression operations that
store the compression level used by InnoDB during runtime. This reduced
the amount of logging by 30-40% on our workloads. In order to make sure
that we did not break anything, we extended mysql's innodb_plugin test
suite with crash-recovery stress tests. These stress tests later became an
important part of our benchmark which we use for testing MySQL for
production readiness.

Our main performance contribution to InnoDB compression was the
implementation of an algorithm that adaptively restricts the amount of
data placed on a page in order to reduce the number of compression failures. Using
this algorithm, we were able to reduce the ratio of compression failures
from 40-45% to 2-3%.