← Live Compression Software

How do I compress old rows in MySQL or MariaDB?

Updated · DBShifts

Short answer

InnoDB can compress a whole table, with ROW_FORMAT=COMPRESSED or transparent page compression, but every read and write of recent rows then pays for it. For old rows only, the options are moving them to an ARCHIVE table, dropping old partitions, or storing just those rows compressed while recent rows stay as they are.

Key facts

  • ROW_FORMAT=COMPRESSED compresses each InnoDB page with zlib down to a KEY_BLOCK_SIZE of 1, 2, 4, 8 or 16 KB and requires innodb_file_per_table.
  • With compressed pages, the buffer pool can hold both the compressed and the uncompressed copy of a page, so memory use goes up, not down.
  • Transparent page compression (COMPRESSION='zlib' or 'lz4' in MySQL, PAGE_COMPRESSED=1 in MariaDB) depends on the file system supporting hole punching in sparse files.
  • The ARCHIVE storage engine stores rows compressed but does not support UPDATE or DELETE, and allows an index only on an AUTO_INCREMENT column.
  • InnoDB keeps the space of deleted rows inside the tablespace; OPTIMIZE TABLE rebuilds the table to return it.
  • Sizes in information_schema.TABLES are statistics: ANALYZE TABLE refreshes them, and MySQL 8 caches them for up to a day by default.

Measure first

DATA_LENGTH and INDEX_LENGTH in information_schema.TABLES give a table's size in bytes, but they come from InnoDB's sampled statistics. After a large delete or load, run ANALYZE TABLE first; on MySQL 8, also set information_schema_stats_expiry to 0 in your session, or you may be reading yesterday's numbers.

For the real footprint on disk, look at the .ibd file of a file-per-table tablespace. The difference between the file and DATA_LENGTH + INDEX_LENGTH is mostly free space that OPTIMIZE TABLE would hand back.

The compression InnoDB already has

ROW_FORMAT=COMPRESSED is the older mechanism: InnoDB compresses each 16 KB page to a smaller block size you pick. Pick it too small and pages fail to compress and split; pick it too large and you save little. Hot pages live in the buffer pool in both forms, which costs memory, and every write recompresses.

Transparent page compression compresses pages when they are written to disk and lets the file system reclaim the unused part of each page. It needs a file system that supports hole punching, and backups and file copies have to handle sparse files, or the savings disappear on restore.

Both work on the whole table. For a table where yesterday's rows are read constantly and last year's rarely, that means paying compression costs on the rows that matter most to latency, to save space on the rows that barely matter.

Archive tables and partitions

The classic pattern copies old rows into an archive table with INSERT ... SELECT and deletes them from the live table, in small batches. With ENGINE=ARCHIVE the copy is compressed, but it can never be updated or deleted from and has no useful indexes, so finding one old order means a full scan.

RANGE partitioning by date makes cleanup cheap: DROP PARTITION removes a month instantly, and EXCHANGE PARTITION swaps one into a separate table. MySQL requires the partitioning column in every unique key, including the primary key, which often means changing the key on an existing table.

Compress only the old rows

What most tables actually need is in between: recent rows untouched and fast, old rows kept, still readable by key, and much smaller. That means a process that takes rows past a certain age, stores them together in compressed form, and reads them back when asked — leaving the hot part of the table as it is.

Checklist

  1. 1

    Run ANALYZE TABLE before trusting information_schema sizes.

  2. 2

    Compare the .ibd file size with DATA_LENGTH + INDEX_LENGTH to see free space inside the tablespace.

  3. 3

    Reclaim that space with OPTIMIZE TABLE (or ALTER TABLE ... FORCE), off-peak.

  4. 4

    Decide whether the cost of compressing every page is acceptable for the hot rows.

  5. 5

    If you partition, check that the date column can be part of every unique key.

  6. 6

    Move or delete old rows in batches of a few thousand per transaction.

Common mistakes

Compressing the hot table to save cold space

Every read and write of current rows pays for compression, and the buffer pool holds extra page copies.

Deleting and expecting a smaller file

InnoDB reuses the space inside the tablespace. The .ibd file stays the same size until the table is rebuilt.

Reading stale sizes

information_schema sizes lag big changes until ANALYZE TABLE runs, and MySQL 8 caches them for a day by default.

An ARCHIVE table for rows people still look up

Without indexes, every lookup scans the whole archive.

How LCS does it

  • LCS works on MySQL and MariaDB InnoDB tables: rows older than an age you choose move into compressed blocks in a table beside the original, while recent rows stay ordinary InnoDB rows.
  • Old rows stay readable by key and key range from your DBShifts account, in one consistent snapshot with the recent rows.
  • Updates and deletes still work on compressed rows; an updated row is written back to the table and given the same UPDATE a recent row would get, so ON UPDATE CURRENT_TIMESTAMP columns behave as usual.
  • Blocks are cut to fit the server's max_allowed_packet, and every block is decoded and compared value by value before it is written.
  • Disable puts every row back into the table exactly and removes LCS's own tables.

Questions people also ask

Is InnoDB page compression worth it?

It can be on large, rarely updated tables with compressible data, if the file system supports hole punching. On busy tables the CPU and buffer-pool cost often outweighs the disk saved.

Does OPTIMIZE TABLE lock the table?

In MySQL, OPTIMIZE TABLE on an InnoDB table maps to ALTER TABLE ... FORCE, which uses online DDL: reads and writes continue for most of the rebuild, with short locks at the start and end. It still needs free disk for the rebuilt copy.

Can MariaDB use lz4 or zstd for InnoDB?

MariaDB's InnoDB page compression supports zlib by default and other algorithms such as lz4, lzo, lzma, bzip2 and snappy when the server was built with them; innodb_compression_algorithm selects one.

Keep old rows in the database, at a fraction of the size

LCS for PostgreSQL, MySQL and MariaDB. Measure a table first; nothing changes until you enable it.

Start free

4 guides on keeping old rows small. Back to Live Compression Software.