I inherited an archive schema once where a predecessor had proudly set ROW_FORMAT=COMPRESSED and KEY_BLOCK_SIZE=8 on every table, expecting the disk footprint to halve. The footprint dropped about a third. CPU went up measurably. And the buffer pool, which nobody had touched, suddenly behaved as if it had been shrunk, because hot pages were being kept in two copies at once. Three separate surprises, all from one line of DDL, and all traceable to the same root cause: MySQL has two completely different features that both answer to the name InnoDB compression, and almost nobody can tell you which one their tables use.
So here is the disambiguation I wish someone had handed me: what ROW_FORMAT=COMPRESSED really does under the hood, what transparent page compression does instead, the honest CPU-versus-I/O math, the counters that prove whether compression is working, and the narrow case where compression still earns its keep in 2026.
Why do people conflate the two InnoDB compressions?
Both features are documented under the heading InnoDB table compression, both default to zlib, and both promise smaller tables on disk. That is where the similarity ends. The classic one, ROW_FORMAT=COMPRESSED with its KEY_BLOCK_SIZE knob, dates from the InnoDB Plugin era of MySQL 5.1 and compresses individual B-tree pages inside InnoDB. Transparent page compression arrived in MySQL 5.7.8, is enabled per table with COMPRESSION='zlib' or COMPRESSION='lz4', and compresses pages at flush time, leaning on filesystem hole punching to reclaim the space. They are mutually exclusive on a table, they fail in different ways, and they are monitored with different tools. If you remember one sentence: the classic format compresses pages for the buffer pool, the transparent one compresses pages for the disk.
How does ROW_FORMAT=COMPRESSED actually work?
With ROW_FORMAT=COMPRESSED, every page in the table's B-tree is stored in a compressed form sized by KEY_BLOCK_SIZE. On a default 16 KB InnoDB page, KEY_BLOCK_SIZE=8 tells InnoDB to zlib-compress each page until it fits in 8 KB. On disk and in the system tablespace of your memory budget, the page occupies the smaller size. Reads decompress on the way in. The part that catches operators off guard is the buffer pool behavior: for frequently accessed pages, InnoDB keeps both the compressed and the uncompressed copy resident, because decompressing on every single access is too expensive. A hot compressed table can therefore cost more buffer pool memory than the same table uncompressed, right when you can least afford it. If the pool is already tight, that trade is covered from the memory side in the InnoDB buffer pool field guide.
Writes have their own machinery. InnoDB does not recompress a page on every modification; it logs changes into a reserved modification log area inside the compressed page. When that area fills, the page is recompressed, and if the data no longer fits in KEY_BLOCK_SIZE, the page splits. Each split means more pages, more recompression, and B-tree fragmentation that compounds. KEY_BLOCK_SIZE is a guess you make once at CREATE TABLE time about how compressible your rows are, and a wrong guess is permanent until you rebuild the table. Guessing 8 for rows that compress to 55 percent of original is how you get a table that splits its way to a larger footprint than an honest uncompressed one.
How is transparent page compression different?
Transparent page compression leaves InnoDB's in-memory world alone. Pages live in the buffer pool uncompressed, full size, always. The compression happens at the boundary: when a dirty page is flushed, InnoDB compresses it, writes the compressed bytes at the start of the page's 16 KB slot, and then asks the filesystem to punch a hole in the rest of the slot, returning those blocks to the filesystem as sparse space. On the way back in, the page is read, decompressed, and materialized at full size. The buffer pool never pays the double-copy tax, which is the single biggest practical advantage over the classic format.
The dependency is the filesystem. Hole punching needs fallocate with FALLOC_FL_PUNCH_HOLE, which modern ext4 and XFS on Linux provide, along with NTFS sparse files on Windows. Some network filesystems, older kernels, and certain storage appliances do not support it, and on those you get compressed bytes written into a full-size slot with zero space saved and the CPU cost still paid. The table must also live in a file-per-table tablespace, and the savings are invisible in the file's apparent size: ls will still report the logical length, and only an allocated-blocks view tells the truth. I have watched a team declare the whole feature broken from du --apparent-size, reading the apparent length of a sparse file and concluding nothing had been saved, when the allocated-block count said otherwise.
What is the honest CPU versus I/O math?
Compression is a trade of CPU for bytes, and it pays only when bytes are the expensive side of the ledger. The classic format earned its reputation in the spinning-disk era: a random 16 KB read on a hard drive cost milliseconds, and zlib decompression of a page costs tens of microseconds, so buying fewer IOPS with CPU was an obvious win, often two orders of magnitude in your favor. On modern NVMe, a random read costs tens of microseconds too, and the arithmetic collapses into a wash at best. Add the classic format's buffer pool double-buffering and page-split pathologies, and for a hot OLTP working set on fast local storage, compression usually loses. That is the real reason it fell out of fashion: not a bug, just arithmetic that stopped working when storage got fast and memory got relatively expensive.
Transparent page compression changes the math modestly in compression's favor: lz4 is cheap, the buffer pool keeps one copy, and there is no KEY_BLOCK_SIZE to guess wrong. But it still spends CPU on every flush and every read of a cold page, and it still only wins when the saved I/O or the saved capacity is worth more than the cycles. If your working set fits in the buffer pool and your disk is bored, both features are solutions looking for a problem.
Which counters tell you compression is working?
For the classic format, INFORMATION_SCHEMA.INNODB_CMP is the scoreboard, one row per KEY_BLOCK_SIZE in use:
SELECT page_size, compress_ops, compress_ops_ok,
uncompress_ops, compress_time, uncompress_time,
compress_ops - compress_ops_ok AS recompress_failures
FROM information_schema.innodb_cmp
ORDER BY page_size;The ratio that matters is compress_ops_ok divided by compress_ops. A healthy compressed table runs that close to one; a table whose pages keep failing to fit and splitting drags it down, and a high recompress_failures count on a hot table is your cue that KEY_BLOCK_SIZE was guessed wrong. INNODB_CMPMEM tells the companion story of buffer pool blocks reserved for decompression, and relocation failures there mean the pool is struggling to even stage compressed pages. INNODB_CMP_PER_INDEX breaks the same counters down per index when you need to find the one offending table.
None of those counters see transparent page compression at all, which confuses everyone the first time. For the transparent kind, the truth lives in the tablespace files themselves, and information_schema.INNODB_TABLESPACES exposes it:
SELECT name, file_size, allocated_size,
file_size - allocated_size AS punched_bytes
FROM information_schema.innodb_tablespaces
WHERE name LIKE 'archive/%'
ORDER BY allocated_size DESC;file_size is the logical length, allocated_size is what the filesystem actually holds after hole punching. If punched_bytes is near zero on a table you created with COMPRESSION='zlib', your filesystem did not punch holes, and you are paying CPU for nothing. That single comparison settles most arguments about transparent page compression in one query.
When does compression still pay?
Cold, append-mostly archive tables are the case that survives the math. Think audit logs, event history, message archives: rows written once, read rarely, and heavy on JSON or text, which typically compresses two to four times. The CPU cost is amortized because reads are rare, the capacity savings are real money on large volumes, and the write path cost is a predictable tax on an insert stream you can measure. For those tables I reach for transparent page compression with lz4 first: no KEY_BLOCK_SIZE to guess, no buffer pool double-pay, cheap decompression for the occasional audit query. The classic ROW_FORMAT=COMPRESSED I treat as legacy: worth understanding because fleets are full of it, not something I deploy on new tables. One honorable mention: physical copies of the datadir — the file-copy backup tools and storage-level replicas — inherit the savings, and a smaller .ibd is cheaper to ship and restore, which occasionally matters more than the disk savings themselves. Logical dumps see none of it: they serialize row values, and replication ships row events, so the savings evaporate the moment data leaves the storage layer. When disk pressure is the actual emergency, though, compression is a slow fix; the fast triage is in MySQL disk-full emergencies.
Where MonPG stands on MySQL
Full disclosure, since I build it: MonPG monitors PostgreSQL today, and MySQL support is still being built, coming soon rather than shipping. The compression debates in this note are exactly the kind of thing the MySQL work should make boring: buffer pool footprint pressure, INNODB_CMP recompress failure ratios, and punched-versus-logical tablespace sizes are all trendable counters, and a monitor that graphs them turns is compression working into a question you answer with a chart instead of an argument. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it does, the same evidence-first philosophy already runs on the PostgreSQL side, and the rest of these field notes are on the blog.