Initial InnoDB Compression Test Results

Initial InnoDB Compression Test Results: "I've completed an initial round of testing InnoDB with data compression and the results are extremely promising. The testing was performed by setting up two shadows of a production database. The shadows execute the exact same queries as the production database to collect performance statistics but the query results are ignored.

The production server uses Fusion-io PCI-Express flash storage and had 290GB of data. Both shadows were running on traditional spinning disks, so I wasn't sure they would be able to keep up with the load. The first shadow was configured with MySQL 5.0.84 with the uncompressed 'compact' InnoDB storage format. The second shadow was configured with MySQL 5.1.38 and the InnoDB 1.0.4 plugin with 2x compression. With compression enabled on all tables except 3 containing pre-compressed data, the InnoDB data size shrunk to 170GB, a 41% reduction.

When the shadows were first started up with empty caches, the compressed shadow ran noticeably slower as decompression slowed down every read from disk. Once the cache was warm, however, the compressed shadow ran significantly faster. InnoDB can store both compressed and uncompressed pages in its cache. When the cache is full, it can discard uncompressed pages but retain the compressed pages, allowing a much larger number of pages to stay into memory. For a spinning disk-based server, decompressing a page is much faster than re-reading it from disk.

The numbers speak for themselves; With compression enabled the cache miss rate dropped from 0.51% to 0.36% and the time spent waiting for IO dropped from 19% to 7.6%. The CPU load increased from 5% to 9% but our workload is rarely limited by CPU speed. The average time spent performing queries for the profile page, one of our most important metrics, was 4x faster with compression enabled.

Unexpectedly the amount of write IO went up with compression enabled. Because we have more pages in memory, insertions into secondary indexes are more often performed immediately instead of being deferred and combined in the insert buffer. Overall though the reduced read IO dramatically outweighed the increase in write IO. The net results look like a very promising way to make better use of our server hardware.
PlanetMySQL Voting:
Vote UP /
Vote DOWN"