InnoDB Dynamic vs Compressed Row Format Differences

Choosing the right row format in MySQL’s InnoDB storage engine is crucial for optimizing database performance and storage efficiency. This article explains the structural differences between the DYNAMIC and COMPRESSED row formats, focusing on how they store overflow columns, handle disk storage, and impact memory and CPU utilization.

The Common Foundation

Both DYNAMIC and COMPRESSED are modern InnoDB row formats that inherit characteristics from the older COMPACT format. The defining shared feature of both formats is how they handle long variable-length columns (such as VARCHAR, TEXT, and BLOB).

When a row is too large to fit within the standard InnoDB page size (typically 16KB), both formats store long columns entirely off-page in overflow pages. They place only a 20-byte pointer in the clustered index record pointing to the overflow pages. This is a significant improvement over legacy formats like COMPACT, which forced the first 768 bytes of the column to remain in the main index page. By keeping the primary index pages lean, both formats allow more rows to fit per page, maximizing cache efficiency for index lookups.

Structural Characteristics of DYNAMIC

The DYNAMIC row format is the default setting in modern MySQL installations. Structurally, it focuses on high performance and efficient memory mapping:

• No Disk Compression: Data is written to disk in its raw, uncompressed state.
• Standard Page Size: It utilizes the default 16KB InnoDB page size both on disk and in memory (the buffer pool).
• Zero CPU Overhead for Compression: Because data is not compressed, CPU cycles are not wasted on compression and decompression algorithms during read and write operations.

Structural Characteristics of COMPRESSED

The COMPRESSED row format is designed to minimize disk space at the expense of CPU and memory. Structurally, it differs from DYNAMIC in the following ways:

• Page-Level Compression: It uses the zlib compression algorithm to compress the table data and index pages.
• Key Block Size: It introduces the KEY_BLOCK_SIZE parameter, which determines the physical page size on disk (commonly configured to 4KB or 8KB, compared to the standard 16KB uncompressed memory page).
• Dual Representation in Buffer Pool: To read or modify compressed tables, the InnoDB buffer pool must often hold two versions of the same page: the compressed page (matching the disk structure) and an uncompressed copy (enabling active CPU operations). This structure can severely limit the available space in the InnoDB buffer pool.

Summary of Differences

While both formats keep index pages small by utilizing 20-byte pointers for overflow data, they serve different operational goals. DYNAMIC optimizes for raw CPU throughput, low latency, and memory efficiency by keeping data uncompressed. COMPRESSED optimizes for disk space and I/O-bound environments by packing pages using zlib compression, though this introduces a higher CPU load and requires more buffer pool memory.