Using Table Compression With ROW_FORMAT=COMPRESSED in Practice

1. Why table compression is a topic in InnoDB at all

InnoDB organizes data in pages of 16 kilobytes by default, moved as a unit between disk and buffer pool. For tables with very many rows, such as log, event, or archive tables, the sheer page count quickly adds up to significant storage consumption, even when individual row values are small and highly redundant, for example status fields, recurring IDs, or timestamps within a narrow range.

ROW_FORMAT=COMPRESSED addresses exactly that: the physical page on disk is stored smaller than 16 kilobytes, because InnoDB compresses the page content before writing it. That not only reduces disk space but, with the right configuration, also the amount of data that has to move through the buffer pool and over the network to replicas for the same page count.

2. How InnoDB page compression technically works

When creating a table with ROW_FORMAT=COMPRESSED and a chosen KEY_BLOCK_SIZE, InnoDB compresses every 16-kilobyte page with zlib before writing it to disk, storing the result in a block of the chosen target size. On read, the compressed page is loaded from disk and decompressed again into the buffer pool, where InnoDB often keeps the compressed version around alongside the decompressed working copy to avoid repeated decompression on subsequent eviction and reload.

If an update changes an already-compressed page so it no longer fits into the chosen target block, InnoDB has to reorganize and recompress the page, which costs extra CPU time. This reorganization cost is exactly the lever that the choice of KEY_BLOCK_SIZE later has to work with.

-- Create a table with page compression
CREATE TABLE report_event_archive (
    event_id   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_type SMALLINT UNSIGNED NOT NULL,
    object_id  INT UNSIGNED NOT NULL,
    logged_at  DATETIME NOT NULL,
    KEY idx_logged (logged_at)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

3. Choosing KEY_BLOCK_SIZE: 1, 2, 4, or 8 kilobytes

KEY_BLOCK_SIZE specifies the target size of the compressed page in kilobytes and accepts the values 1, 2, 4, or 8, each relative to the uncompressed baseline size of 16 kilobytes. A smaller target size forces stronger compression and saves more space, but simultaneously increases the risk that an update overflows the target block and triggers an expensive reorganization plus recompression.

In practice, an iterative approach works well: start with a moderate value like 8 kilobytes, measure the actual compression ratio under realistic load, and only reduce the target size further if the reorganization rate stays low. For rows with strongly variable length, such as text of differing sizes, an overly aggressive value is especially risky.

4. CPU overhead: what happens extra on every page access

Every read access to a page not already sitting decompressed in the buffer pool costs extra CPU time for decompression, and every write access that modifies a page costs CPU time for recompression. On heavily used tables written by many concurrent connections at once, this overhead can become noticeable under load, especially when many CPU cores are already busy with other queries. On systems with tightly sized CPU capacity, such as smaller virtual machines with few cores, this extra cost adds up to a real bottleneck faster than on generously sized database servers with plenty of spare compute.

In addition, InnoDB uses more buffer pool memory than with uncompressed tables, because compressed and decompressed copies of an active page can be kept in memory simultaneously. If the buffer pool is already tightly sized, compression can even shrink the effectively usable cache for other tables instead of relieving memory pressure.

5. The trade-off: storage savings against CPU and cache consumption

Compression pays off mainly for I/O-bound workloads, where disk access time or the network to replicas is the actual bottleneck while CPU capacity is plentiful. Typical examples are rarely written but occasionally fully scanned reporting or archive tables, where fewer pages on disk directly translate into less read time.

For CPU-bound workloads written heavily in parallel with limited compute capacity, the trade-off reverses: the extra decompression and reorganization overhead can worsen response times even though fewer bytes sit on disk. A blanket decision without measurement under realistic load is therefore risky.

6. Practical Magento example: which tables qualify as candidates

In Magento installations, the main candidates are tables that are rarely updated but occasionally read at larger scale: event logs such as report_event, historical price index archives, customer activity logs, or admin action logs. These tables grow continuously but are predominantly append-only and rarely modified afterward, keeping the risk of expensive reorganizations low.

Compressing hot, heavily written tables such as quote_item, sales_order_grid, or the catalog's core index tables is generally not advisable, because constant, often row-modifying write access there directly increases the reorganization risk that makes compression expensive.

7. Alternative: transparent page compression via punch hole

Besides classic ROW_FORMAT=COMPRESSED compression, InnoDB offers a second approach with transparent page compression: pages stay uncompressed in the buffer pool but are shrunk on disk via filesystem hole punching when written, provided the underlying filesystem, such as ext4 or XFS, supports it. The buffer pool does not benefit from reduced memory footprint here, since the full, uncompressed page still resides there.

This variant is best suited when disk space alone is the problem but CPU overhead in the buffer pool should be avoided, though it depends more heavily on the filesystem and operating system in use than the classic, universally available KEY_BLOCK_SIZE compression.

8. Monitoring and diagnostics: reading compression statistics correctly

InnoDB exposes aggregated compression statistics through INFORMATION_SCHEMA.INNODB_CMP and INNODB_CMP_RESET: COMPRESS_OPS counts every compression attempt, COMPRESS_OPS_OK the successful ones, where the page fit into the target block without reorganization. If the ratio of OK to total attempts sits well below one, that points to a KEY_BLOCK_SIZE chosen too aggressively for the actual data pattern.

In addition, COMPRESS_TIME and UNCOMPRESS_TIME provide cumulative CPU time spent on compression and decompression, which can be weighed directly against the measured storage gain to reach a well-founded decision instead of a pure gut call.

-- Evaluate compression statistics per tablespace
SELECT page_size, compress_ops, compress_ops_ok,
       ROUND(compress_ops_ok / NULLIF(compress_ops, 0), 3) AS success_ratio,
       compress_time, uncompress_time
FROM information_schema.innodb_cmp;

-- Reset counters after the measurement window
SELECT * FROM information_schema.innodb_cmp_reset;

9. Practical rollout: procedure, testing, and rollback strategy

Converting an existing table happens via ALTER TABLE ... ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=n and runs as an online DDL operation, though large tables need corresponding time and I/O capacity. Before rolling out to production, a trial run on a copy with realistic data volume is recommended, measuring both storage savings and response times under load.

Should compression turn out to be disadvantageous, it can be reverted at any time with ALTER TABLE ... ROW_FORMAT=DYNAMIC. This reversibility makes compression a low-risk experiment, as long as measurements before and after the change are documented cleanly.

-- Trial-compress an existing table
ALTER TABLE report_event_archive
    ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4, ALGORITHM=INPLACE, LOCK=NONE;

-- Roll back on a negative measurement result
ALTER TABLE report_event_archive ROW_FORMAT=DYNAMIC;

10. Summary

11. FAQ: ROW_FORMAT=COMPRESSED: The Essentials at a Glance