In this lesson, you will learn how to leverage InnoDB’s page-level compression to reclaim disk space while maintaining high performance for read-heavy workloads.
The Mechanics of Compression
When we talk about compression in the MySQL/MariaDB ecosystem, we are specifically looking at how InnoDB handles data stored on disk. While standard storage uses 16KB pages by default, enabling compression allows the database to store more data per physical I/O operation. This reduces the footprint on your storage subsystem and, more importantly, can improve throughput because you are fetching more "compressed" data in a single read request from the disk into the Buffer Pool.
Implementation Strategy
To apply compression at the table level, you must use the ROW_FORMAT=COMPRESSED attribute. However, this is only effective if your innodb_page_size is greater than the compression KEY_BLOCK_SIZE.
Consider a scenario where you want to compress a high-volume logs table. You would define the table like this:
CREATE TABLE operational_logs (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
event_data TEXT,
created_at TIMESTAMP
) ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;
In this example, we have set the KEY_BLOCK_SIZE to 8KB. When InnoDB writes this to disk, it attempts to compress the 16KB page into an 8KB physical block.
Balancing Latency and CPU
It is crucial to understand that compression is a trade-off. While it saves disk space and reduces I/O bandwidth, it consumes CPU cycles to perform the compression (on write) and decompression (on read).
Because the data is decompressed once it enters the Buffer Pool, your active working set remains in its raw format. This ensures that query latency is not significantly impacted once the data is cached. If your CPU usage is already high, be cautious; if your bottleneck is I/O throughput or expensive disk costs, this technique is a powerful optimization tool. Always test your workload’s latency profile before and after implementation to ensure the compression ratio justifies the additional CPU overhead.
Key Takeaway: Table-level compression reduces disk I/O and footprint by storing compressed pages on disk, shifting the burden slightly toward CPU while keeping the Buffer Pool latency low.