Compression mode of Innodb table

Shulou Source: shulou.com Published: 2022-06-01 06:25:39 09月13日 Update

Innodb currently supports two file formats (innodb_file_format) Antelope and Barracuda

The default is Antelope (antelope), and there are two "data table formats" (row_format): Redundant (redundant) and Compact (compact)

5.7The default is that Barracuda (Barracuda) adds support for two data table formats: Dynamic and Compressed

1. How to set the compression of mysql innodb table

Set compression for innodb tables

First, the version of mysql needs to be greater than 5.5

Second, set up innodb_file_format=barracuda

Innodb_file_format=barracuda

Innodb_file_per_table = 1

Innodb_strict_mode=1 # suggest adding

Innodb_default_row_format = COMPRESSED # is set when row compression format is enabled by default for the entire library without changing this value

Third, create table or alter talble add ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Given that the maximum row size of the InnoDB table is about 8K, usually a safe choice.

In the buffer pool, compressed data is saved in small pages based on the KEY_BLOCK_SIZE value

KEY_BLOCK_SIZE defaults to half of the innodb_page_ size value, which is 8k