MariaDB Page Compression reduces physical storage requirements by compressing InnoDB pages before they are written to disk, thereby significantly reducing I/O volumes. I’ll show you how to save storage space and keep latency low, what prerequisites apply, and which settings are most effective in practice.
Key points
These concise bullet points provide an introduction to the most important aspects.
- Page by page Compression reduces storage requirements and I/O.
- Uncompressed The buffer pool limits CPU load in RAM.
- Flexible Enable PAGE_COMPRESSED per table.
- file system-Support for sparse/hole punching is required.
- Algorithm Selection Controls the rate, latency, and CPU cost.
How InnoDB Page Compression Works Technically
I compress each InnoDB page just before it is written to disk, so that the tablespace occupies only the actual number of bytes after compression, and the file system marks the free areas as sparse. In the Buffer Pool I continue to keep pages uncompressed, which keeps CPU overhead in RAM low and ensures that frequent read operations remain fast. By default, InnoDB pages are 16K, but the stored blocks end up being variably smaller after compression, which saves a lot of space, especially for text or JSON fields. When reading, I decompress the page immediately after loading it into RAM—that is, right at the I/O boundary, where the savings in data transfer matter most. This way, I shift the load from I/O to the CPU, but only in places where it is reasonable to do so.
Page Compression vs. Traditional InnoDB Table Compression
Classic compression relies on ROW_FORMAT=COMPRESSED plus KEY_BLOCK_SIZE, which creates a fixed compressed page format and introduces an additional decision-making burden when writing or updating. I prefer the Page Compression, because it remains flexible: If compression fails, InnoDB can store the page uncompressed without changing the entire file format. The buffer pool continues to work with uncompressed 16K pages, which speeds up cache hits and keeps CPU paths simple. For typical OLTP workloads with many inserts and moderate updates, page compression provides a better balance between space savings and latency. As a result, I often get a noticeable I/O benefit without incurring high overhead on every Update to risk.
Prerequisites and Basic Configuration
For page compression, I require InnoDB and enable innodb_file_per_table, so that each table uses its own tablespace. The file system is crucial: It must support sparse files and hole-punching, which ext4 and XFS do and which is generally available in modern cloud volumes. To select the algorithm, I set `innodb_compression_algorithm`, typically to `zlib`, `lz4`, or `lzo`, depending on the desired compression ratio and CPU profile. Those who carefully consider the storage layer will benefit from a compact File system comparison and also takes driver and volume options into account. This results in a Configuration, which saves space, reduces I/O, and operates reliably.
Table-Level Activation
I enable page compression on a per-table basis after ensuring that the global variables are set correctly, so that I can specifically target the rows that provide the greatest benefit. For new tables, I set the options directly in the DDL; for existing tables, an `ALTER TABLE` statement handles the conversion by rewriting the data. I define the compression level using `PAGE_COMPRESSION_LEVEL`; the behavior depends on the Algorithm ... Because the transition takes time, I schedule maintenance windows and check the space requirements with and without compression using actual data samples. This is how I verify Expenditure and a result with no surprises.
CREATE TABLE log_entries (
id BIGINT UNSIGNED PRIMARY KEY,
created_at DATETIME NOT NULL,
level VARCHAR(20),
message TEXT
) ENGINE=InnoDB
PAGE_COMPRESSED=1
PAGE_COMPRESSION_LEVEL=6;
ALTER TABLE log_entries
ENGINE=InnoDB,
PAGE_COMPRESSED=1;
Storage Savings in Practice
The more homogeneous and text-heavy the data is, the more effective the Compression; Log and reporting tables usually yield significant improvements. In typical workloads, I often see 40–60 % less memory usage with zlib, while lz4 handles many cases with 30–50 % and leaves more throughput available. Highly distributed binary data yields smaller gains, but even there, I/O volumes and costs are often noticeably reduced. I always test using production snapshots on staging to obtain meaningful metrics and identify latency spikes. The result: less data on the storage medium, shorter transfer times, and better Scaling.
Performance: Accurately Evaluating I/O vs. CPU
First, I check whether the bottleneck is on the storage device or on the CPU This is important because the choice of compression method depends on it. In I/O-limited environments, read and write volumes decrease significantly, resulting in improved effective performance—often with only a 5–10 % increase in load compared to uncompressed tables when using fast algorithms. CPU-limited systems benefit from lz4 or lzo, which operate very quickly and achieve only slightly lower compression ratios. Additionally, I take into account the Double-write buffer, because it influences write behavior and, together with page compression, shapes the I/O characteristics. Since the buffer pool remains uncompressed, frequent cache hits have little effect on the Latency from.
Algorithm Selection and Compression Level
I decide the Algorithm-I make my decision based on data patterns, read/write rates, and CPU headroom, rather than relying solely on the compression ratio. Zlib often delivers the greatest space savings with moderate computational overhead, while lz4/lzo excel in terms of low latency. I primarily use LZMA or bzip2 for archives or tables that are rarely modified, since the CPU overhead is higher. The compression level (PAGE_COMPRESSION_LEVEL) controls the balance between compression ratio and computational cost, though the marginal benefit diminishes beyond medium levels. A quick round of measurements using the actual dataset will quickly identify the best Level.
| Algorithm | Typical rate | CPU costs | Suitability | Notes |
|---|---|---|---|---|
| zlib | 40–60 % | Medium | Many OLTP/reporting tables | Good Balance from Rate/Latency |
| lz4 | 30–50 % | Low | High throughput requirements | Very fast Decompression |
| lzo | 30–50 % | Low | Write-intensive workloads | Low latency for inserts |
| lzma | 50–70 % | High | Archives/Historical Data | For rare Changes |
| bzip2 | 50–70 % | High | Selective Histories | Slow, good ratings |
Keeping an Eye on Monitoring and Metrics
I measure throughput, latency, CPU usage, and Buffer Pool-Hit rate, because only the big picture reveals the actual effect. A decrease in I/O volumes while latency remains the same or improves indicates that the configuration is working. If CPU utilization rises above a healthy level, I check the algorithm and level and switch to lz4 if necessary. I also monitor the size of the redo log and checkpoint behavior, as both influence the write profile. Over the long term, I identify trends and can proactively respond to changes in Workloads react.
Plan Backups and Maintenance Carefully
Full and incremental file backups benefit from the lower amount of data, because fewer bytes are copied, whereas logical dumps typically retain their size. I test restore times with real data so that I can weigh the space savings against the actual restore time. I document changes to the algorithm or level and verify the compatibility of the backup tools with the MariaDB version in use. I also validate integrity after large ALTER TABLE operations, especially when many tables have been converted to page compression. This ensures that the Restart time predictable and the hedging strategy reliable.
Understanding the File System and Storage Layer
For sparse files to work, the file system requires Hole punching, which is available for ext4 and XFS and is widely used in hosting setups. I pay close attention to mount options and queue depth, as they significantly influence I/O characteristics. For ext4, for example, I check commit intervals and journal modes and consider how garbage collection affects SSD/NVMe performance. A look at suitable ext4 Options helps to align the page compression effects with the characteristics of the file system. This is how I use the physical Storage is effective and prevents side effects.
Practical guide for the introduction
I'll start with a test environment and copy representative production data to obtain initial metrics for throughput, latency, and Throughput to obtain. After that, I first enable page compression for large tables or archives that are primarily read-only and have few updates. I evaluate the results using clear metrics and compare them to the baseline before applying the change to additional tables. Early communication with application teams prevents surprises during maintenance windows and ensures clear expectations. After each expansion, I adjust the level and Algorithm until memory savings and latency fall within the target range.
Combination with Additional Optimizations
Good indexes reduce the number of pages read, so I check Index Coverage and cardinalities on a regular basis. Well-formulated queries, appropriate joins, and the targeted use of EXPLAIN reduce I/O and keep the cache hit rate high. A sufficiently large buffer pool size prevents unnecessary disk reads and makes the compression overhead in the hotset practically invisible. On the hardware side, SSDs and NVMe pay off with high IOPS and low latency, which amplifies the benefits of page compression. Overall, compression works in tandem with query design, indexing, and Storage Expansion combined to form a streamlined data path.
Compatibility, Versions, and Limitations
I keep track of which environments support page compression and where the limitations lie. On common Linux file systems such as ext4 and XFS, hole punching works reliably. ZFS behaves differently: Since punching isn't available in the same way there, I tend to use the native I enable ZFS compression and disable page compression. In container setups using OverlayFS, I prefer to mount the data directory as a bind mount from the host so that punching and sparse files work reliably. In addition, I do not combine page compression with InnoDB file-level table encryption: Encryption makes the data largely random for compression algorithms and can also partially block punching. If you need both, use volume- or filesystem-level encryption at the level below InnoDB.
For the InnoDB page size (innodb_page_size), I usually stick with 16K. Smaller page sizes can make compression more difficult and increase administrative overhead. Temporary tables or MEMORY/work tables are not affected by page compression—the benefit is limited to the respective .ibd tablespace.
Activation, deactivation, and rebuilds without any surprises
The conversion using ALTER TABLE always triggers a table rebuild. Therefore, I plan to:
- Maintenance windows with clear SLAs and sufficient storage space for the temporary copy.
- Perform a preliminary check using EXPLAIN for ALTER to see the expected behavior (INPLACE/COPY, LOCK level).
- Optional batching strategy: start with large tables that rarely change, then move on to medium-sized tables, and finally the "hot" tables—if any.
To disable this, I take a symmetrical approach and set PAGE_COMPRESSED=0. I then run an OPTIMIZE TABLE or another ALTER REBUILD so that the tablespace is written without gaps again and the physical space usage is accurately reflected.
Bulk Loads, Hot Updates, and Defragmentation
For bulk data, I either load it directly in compressed form when I/O is limited, or I speed up the import by loading it uncompressed and then switching to PAGE_COMPRESSED using ALTER TABLE. The rebuild then enforces the optimal layout with maximum hole-punching efficiency. For tables with a very high volume of in-place updates, I schedule regular rewrites (OPTIMIZE TABLE or partition rollovers), because repeated changes can diminish the benefits of compression over time. For BLOB/TEXT columns, I use a modern row format (e.g., DYNAMIC) so that large off-page data is handled efficiently and the page neighborhood doesn’t grow unnecessarily.
Test and Demonstrate Effectiveness
I verify whether page compression is enabled using simple system commands and MariaDB views:
# Compare apparent size vs. allocated blocks
ls -ls --block-size=1 *.ibd
du -h --apparent-size *.ibd
du -h *.ibd
# Check the extent of fragmentation (punching) per file
filefrag -v your_table.ibd | tail -n +1
# In MariaDB: Check table status and DDL options
SHOW TABLE STATUS LIKE 'log_entries'\G
SHOW CREATE TABLE log_entries\G
The apparent size (ls) remains the same as the logical data volume, while ls shows the blocks that are actually occupied. A noticeable difference indicates that hole-punching is working. I correlate this measurement with I/O metrics (reads/writes per second, queue depth, latency) and CPU utilization to evaluate the overall effect.
Backup Details: Backing Up and Restoring Sparse Data Correctly
To ensure that backups preserve the space savings, I make sure the tools support sparse files. When copying physical files, I use the appropriate options to prevent gaps from being „filled in“:
# Copying while preserving sparse areas
cp --sparse=always source.ibd dest.ibd
rsync -S --progress source.ibd dest.ibd
tar --sparse -cvf backup.tar /var/lib/mysql/datadir
# Check whether the destination is still sparse
du -h dest.ibd
ls -ls dest.ibd
For snapshot backups (e.g., at the block-device level), the extent of the savings varies depending on the provider. For logical dumps (mysqldump, mariadb-dump), the export size remains virtually unchanged; however, restore times are reduced if the subsequent rebuild re-enables page compression, thereby reducing I/O volumes during the rebuild.
Replication, HA, and Rollouts in Production
Page compression works transparently for replication and binlogs, because it is the SQL changes that are replicated, not the compressed pages. I prefer to roll out DDL changes to replicas first and monitor latency and I/O before making the change on the primary server. In multi-source or cascade topologies, I make sure that the appropriate algorithm (innodb_compression_algorithm) is set everywhere so that identical DDL results in the same behavior. For zero-downtime rollouts, I combine the migration with switchover/failover plans.
Advanced Tuning: I/O Profiles and Checkpoints
Because compression changes the number and size of the blocks to be written, I tune InnoDB's I/O parameters to match the new profile. A realistic `innodb_io_capacity` (and `*_max`) helps generate clean checkpoints without sudden flush spikes. I check whether the doublewrite buffer aligns with the new write characteristics and monitor the ratio of dirty pages to fsync rate. On devices with high parallelism (NVMe), I scale the write threads and the queue depth of the block device so that the reduced data volume translates into a genuine reduction in latency.
Troubleshooting and Common Pitfalls
- CPU Peaks After Activation: Switch the algorithm to lz4/lzo or moderately lower PAGE_COMPRESSION_LEVEL; increase the size of the hot sets in the buffer pool.
- I/O is decreasing, but latency fluctuates: Check for checkpointing and the dirty page ratio; redo logs that are too small result in frequent flushes.
- Unexpectedly small space savings: Check the data structure (many binary/random fields), force a rebuild, analyze BLOB/TEXT patterns, and switch to zlib if necessary.
- No effect on file size: Check the file system's support for hole punching, avoid container layers, and do not „densify“ sparse copies.
- Popular tables: Use page compression selectively; consider alternatives (compress only archive and log tables).
Practical Configuration Examples
To get off to a clean start, I keep the global configuration concise and manageable:
[mysqld]
innodb_file_per_table=1
innodb_compression_algorithm=zlib # or lz4/lzo depending on the profile
# Adjust other I/O parameters to suit the platform
# innodb_io_capacity=...
# innodb_io_capacity_max=...
I explicitly define the compression for each table to avoid any unintended side effects. After large imports or numerous updates, I specifically use `OPTIMIZE TABLE` to recalibrate gaps and reduce the fragmentation that has built up over time.
Briefly summarized
InnoDB Page Compression significantly reduces memory usage and offloads the load from I/O to the CPU without altering the buffer pool. Well-chosen algorithms such as lz4 or zlib deliver 30–60 % savings in many workloads while keeping latency within acceptable limits. Key factors include a file system with hole-punching, `innodb_file_per_table`, and proper activation at the table level. Those who run tests with real data, incorporate monitoring, and fine-tune the level and algorithm will achieve consistently low costs with reliable Performance. This way, you save space, keep your systems running smoothly, and free up capacity for growing datasets.
