Movatterモバイル変換

Because processors and cache memories have increased in speed more than disk storage devices, many workloads aredisk-bound. Datacompression 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.

AnInnoDB table created withROW_FORMAT=COMPRESSED can use a smallerpage size on disk than the configuredinnodb_page_size value. Smaller pages require less I/O to read from and write to disk, which is especially valuable forSSD devices.

The compressed page size is specified through theCREATE TABLE orALTER TABLEKEY_BLOCK_SIZE parameter. The different page size requires that the table be placed in afile-per-table tablespace orgeneral tablespace rather than in thesystem tablespace, as the system tablespace cannot store compressed tables. For more information, seeSection 17.6.3.2, “File-Per-Table Tablespaces”, andSection 17.6.3.3, “General Tablespaces”.

The level of compression is the same regardless of theKEY_BLOCK_SIZE value. As you specify smaller values forKEY_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 smallKEY_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 theCREATE TABLE orALTER TABLE statement fails.

In the buffer pool, the compressed data is held in small pages, with a page size based on theKEY_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 areevicted from the buffer pool when space is needed, and then uncompressed again on the next access.