Posted inMySQL

InnoDB Page Compression: A Pro Tip to Reduce Storage by 50% and Extend SSD Lifespan for MySQL

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

The 2 AM Full Disk Nightmare

A disk usage alert hitting 90% in the middle of the night is a scenario all too familiar for DBAs. In large systems, data bloat doesn’t just increase AWS EBS or Google Cloud Storage bills; it also directly wears out SSDs due to high Write IOPS.

I once managed a logging system for an e-commerce platform. At the time, the order_histories table exceeded 200 million rows, occupying over 150GB. Constantly upgrading storage was only a temporary fix. After implementing InnoDB Page Compression, the size dropped to less than 70GB. Surprisingly, query performance remained almost identical.

Don’t Confuse the Compression Methods

Before configuring, you need to clearly distinguish between the two compression mechanisms in InnoDB to avoid choosing the wrong tool.

1. InnoDB Row Compression (Legacy)

Introduced in MySQL 5.5, this mechanism compresses data at the row level and fits it into fixed-size pages (e.g., 8KB).

  • Weakness: It puts significant pressure on the CPU because data must be constantly decompressed when loaded into the Buffer Pool. It often causes “node splitting” in the B-tree, leading to serious index fragmentation.

2. InnoDB Page Compression (Transparent)

This is a modern solution that leverages the Sparse Files feature of the operating system and file system (such as EXT4 or XFS).

  • Pros: MySQL compresses a 16KB page down to about 6-7KB. The excess space is then released using the punch hole command.
  • Real-world benefit: The SSD only physically writes the compressed data. This significantly reduces Write Amplification, helping your drive “live” longer.

Why You Should Enable Page Compression Now

When logs or transactions tables exceed 10 million rows, I/O bottlenecks are inevitable. Page Compression solves this elegantly:

  1. Cost Reduction: Saving 40% – 60% of disk space allows you to delay purchasing expensive additional storage.
  2. I/O Optimization: Smaller data means MySQL reads fewer bytes from disk into RAM. Large SELECT queries will run noticeably faster.
  3. Hardware Protection: SSDs have a Total Bytes Written (TBW) limit. Compressing data reduces physical write frequency, extending device lifespan.

Prerequisites for Safe Deployment

This feature isn’t for every system. You must ensure the following technical standards:

  • Operating System: Linux kernel version 3.10 or higher.
  • File System: Must support “hole punching”. I recommend using XFS or EXT4 on modern Ubuntu/CentOS distributions.
  • Version: MySQL 5.7.8+ or MariaDB 10.1+.
  • Configuration: The innodb_file_per_table variable must be set to ON.

Detailed Configuration Steps

Follow the process below on your MySQL server.

Step 1: Check Environment

First, confirm that MySQL is saving each table as a separate file:

SHOW VARIABLES LIKE 'innodb_file_per_table';

If the result is ON, you’re ready.

Step 2: Create a New Table with Compression

Simply add the COMPRESSION="zlib" attribute to your table creation statement. Based on experience, zlib offers a very good compression ratio, while lz4 prioritizes processing speed.

CREATE TABLE logs_thanh_toan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB COMPRESSION="zlib";

Step 3: Compress Existing Tables

For massive active tables, use ALTER TABLE. Note that this process will rebuild the entire table. You should perform this during off-peak hours to avoid system hangs.

-- Enable compression mode
ALTER TABLE logs_thanh_toan COMPRESSION="zlib";

-- Free up physical disk space
OPTIMIZE TABLE logs_thanh_toan;

How to Verify Actual Disk Usage

This is the most confusing part. If you use the ls -lh command, Linux will display the logical size (still the old number). To see the actual size after the “punch hole” operation, you must use the du command:

# Compare logical size and actual size
du -h --apparent-size /var/lib/mysql/db_name/logs_thanh_toan.ibd
du -h /var/lib/mysql/db_name/logs_thanh_toan.ibd

The difference between these two commands is the money you just saved for your company.

Hard-Won Lessons from the Field

Despite its great benefits, Page Compression comes with trade-offs to consider:

  • CPU Overhead: Compression/decompression will consume an additional 5-10% of CPU resources. If your server’s CPU usage is consistently above 80%, reconsider.
  • Backup Issues: Tools like mysqldump will export uncompressed files. If using Percona XtraBackup, ensure that the version supports sparse files to maintain the compression state during restoration.
  • Data Types: Don’t waste effort compressing tables containing images, zip files, or encrypted data. Page Compression is most effective with text, JSON, or log tables with many repeating values.

Database optimization isn’t just about fine-tuning SQL. Understanding how data sits on the disk will make your system more resilient and save significant infrastructure costs.

Share:
Tags: