InnoDB Table Compression This section provides information about the  InnoDB  table compression and  InnoDB  page compression features. The page compression feature is also referred to as transparent page compression. Using the compression features of  InnoDB , you can create tables where the data is stored in compressed form. Compression can help to improve both raw performance and scalability. The compression means less data is transferred between disk and memory, and takes up less space on disk and in memory. The benefits are amplified for tables with secondary indexes, because index data is compressed also. Compression can be especially important for SSD storage devices, because they tend to have lower capacity than HDD devices. This section describes  InnoDB  table compression, which is supported with  InnoDB  tables that reside in file_per_table tablespaces or general tablespaces. Table compression is enabled using the ROW_FORMAT=COMPRESSED  attribute with  CREATE TABLE  or  ALTER TABLE . Because processors and cache memories have increased in speed more than disk storage devices, many workloads are disk-bound. Data compression enables smaller database size, reduced I/O, and improved throughput, at the small cost of increased CPU utilization. Compression is especially valuable for read- intensive applications, on systems with enough RAM to keep frequently used data in memory. An  InnoDB  table created with  ROW_FORMAT=COMPRESSED  can use a smaller page size on disk than the configured  innodb_page_size  value. Smaller pages require less I/O to read from and write to disk, which is especially valuable for SSD devices. The compressed page size is specified through the  CREATE TABLE  or  ALTER TABLE KEY_BLOCK_SIZE parameter. The different page size requires that the table be placed in a file-per-table tablespace or general tablespace rather than in the system tablespace, as the system tablespace cannot store compressed tables. For more information, see Section 15.6.3.2, “File-Per-Table Tablespaces” , and Section 15.6.3.3, “General Tablespaces” . The level of compression is the same regardless of the  KEY_BLOCK_SIZE  value. As you specify smaller values for  KEY_BLOCK_SIZE , you get the I/O benefits of increasingly smaller pages. But if you specify a value that is too small, there is additional overhead to reorganize the pages when data values cannot be compressed enough to fit multiple rows in each page. There is a hard limit on how small KEY_BLOCK_SIZE  can be for a table, based on the lengths of the key columns for each of its indexes. Specify a value that is too small, and the  CREATE TABLE  or  ALTER TABLE  statement fails. In the buffer pool, the compressed data is held in small pages, with a page size based on the KEY_BLOCK_SIZE  value. For extracting or updating the column values, MySQL also creates an uncompressed page in the buffer pool with the uncompressed data. Within the buffer pool, any updates to the uncompressed page are also re-written back to the equivalent compressed page. You might need to size your buffer pool to accommodate the additional data of both compressed and uncompressed pages, although the uncompressed pages are evicted from the buffer pool when space is needed, and then uncompressed again on the next access. Compressed tables can be created in file-per-table tablespaces or in general tablespaces. Table compression is not available for the InnoDB system tablespace. The system tablespace (space 0, the .ibdata files) can contain user-created tables, but it also contains internal system data, which is never compressed. Thus, compression applies only to tables (and indexes) stored in file-per-table or general tablespaces. 3147 15.9.1 InnoDB Table Compression 15.9.1.1 Overview of Table Compression 15.9.1.2 Creating Compressed Tables