Enabling Compression for a Table

The default uncompressed size of InnoDB data pages is 16KB. You can use the attributes ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE, or both in the CREATE TABLE and ALTER TABLE statements to enable table compression. Depending on the combination of option values, InnoDB attempts to compress each page to 1KB, 2KB, 4KB, 8KB, or 16KB.


The term KEY_BLOCK_SIZE refers to the size of compressed pages to use for the table. Compression is applicable to tables, not to individual rows, despite the option name ROW_FORMAT. To avoid adding new SQL keywords, the InnoDB storage engine re-uses the clauses originally defined for MyISAM.

To create a compressed table, you might use a statement like this:

 (column1 INT PRIMARY KEY) 

If you specify ROW_FORMAT=COMPRESSED but not KEY_BLOCK_SIZE, the default compressed page size of 8KB is used. If KEY_BLOCK_SIZE is specified, you can omit the attribute ROW_FORMAT=COMPRESSED.

Setting KEY_BLOCK_SIZE=16 typically does not result in much compression, since the normal InnoDB page size is 16KB. However, this setting may be useful for tables with many long BLOB, VARCHAR or TEXT columns, because such values often do compress well, and might therefore require fewer “overflow” pages as described in Section, “Compressing BLOB, VARCHAR and TEXT Columns”.

Note that compression is specified on a table-by-table basis. All indexes of a table (including the clustered index) are compressed using the same page size, as specified on the CREATE TABLE or ALTER TABLE statement. Table attributes such as ROW_FORMAT and KEY_BLOCK_SIZE are not part of the CREATE INDEX syntax, and are ignored if they are specified (although you see them in the output of the SHOW CREATE TABLE statement). Configuration Parameters for Compression

Compressed tables are stored in a format that previous versions of InnoDB cannot process. To preserve downward compatibility of database files, compression can be specified only when the “Barracuda” data file format is enabled using the configuration parameter innodb_file_format.

Table compression is also not available for the InnoDB system tablespace. The system tablespace (space 0, the ibdata* files) may contain user data, but it also contains internal InnoDB system information, and therefore is never compressed. Thus, compression applies only to tables (and indexes) stored in their own tablespaces.

To use compression, enable the “file per table” mode using the configuration parameter innodb_file_per_table and enable the “Barracuda” disk file format using the parameter innodb_file_format. If necessary, you can set these parameters in the MySQL option file my.cnf or my.ini, or with the SET statement without shutting down the MySQL server.

Specifying ROW_FORMAT=COMPRESSED or a KEY_BLOCK_SIZE in the CREATE TABLE or ALTER TABLE statements if the “Barracuda” file format is not enabled produces these warnings that you can view with the SHOW WARNINGS statement:

Warning1478InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
Warning1478InnoDB: KEY_BLOCK_SIZE requires innodb_file_format=1.
Warning1478InnoDB: ignoring KEY_BLOCK_SIZE=4.
Warning1478InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.
Warning1478InnoDB: assuming ROW_FORMAT=COMPACT.

These messages are only warnings, not errors, and the table is created as if the options were not specified. When InnoDB “strict mode” (see Section, “InnoDB Strict Mode”) is enabled, InnoDB generates an error, not a warning, for these cases. In strict mode, the table is not created if the current configuration does not permit using compressed tables.

The “non-strict” behavior is intended to permit you to import a mysqldump file into a database that does not support compressed tables, even if the source database contained compressed tables. In that case, the InnoDB storage engine creates the table in ROW_FORMAT=COMPACT instead of preventing the operation.

When you import the dump file into a new database, if you want to have the tables re-created as they exist in the original database, ensure the server is running the InnoDB storage engine with the proper settings for the configuration parameters innodb_file_format and innodb_file_per_table, SQL Compression Syntax Warnings and Errors

The attribute KEY_BLOCK_SIZE is permitted only when ROW_FORMAT is specified as COMPRESSED or is omitted. Specifying a KEY_BLOCK_SIZE with any other ROW_FORMAT generates a warning that you can view with SHOW WARNINGS. However, the table is non-compressed; the specified KEY_BLOCK_SIZE is ignored).

Warning1478 InnoDB: ignoring KEY_BLOCK_SIZE=n unless ROW_FORMAT=COMPRESSED.

If you are running in InnoDB strict mode, the combination of a KEY_BLOCK_SIZE with any ROW_FORMAT other than COMPRESSED generates an error, not a warning, and the table is not created.

Table 13.6, “Meaning of CREATE TABLE and ALTER TABLE options” summarizes how the various options on CREATE TABLE and ALTER TABLE are handled.

Table 13.6. Meaning of CREATE TABLE and ALTER TABLE options

ROW_FORMAT=​REDUNDANTStorage format used prior to MySQL 5.0.3Less efficient than ROW_FORMAT=COMPACT; for backward compatibility
ROW_FORMAT=​COMPACTDefault storage format since MySQL 5.0.3Stores a prefix of 768 bytes of long column values in the clustered index page, with the remaining bytes stored in an overflow page
ROW_FORMAT=​DYNAMICAvailable only with innodb_file​_format=BarracudaStore values within the clustered index page if they fit; if not, stores only a 20-byte pointer to an overflow page (no prefix)
ROW_FORMAT=​COMPRESSEDAvailable only with innodb_file​_format=BarracudaCompresses the table and indexes using zlib to default compressed page size of 8K bytes; implies ROW_FORMAT=DYNAMIC
KEY_BLOCK_​SIZE=nAvailable only with innodb_file​_format=BarracudaSpecifies compressed page size of 1, 2, 4, 8 or 16K bytes; implies ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED

Table 13.7, “CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF” summarizes error conditions that occur with certain combinations of configuration parameters and options on the CREATE TABLE or ALTER TABLE statements, and how the options appear in the output of SHOW TABLE STATUS.

When InnoDB strict mode is OFF, InnoDB creates or alters the table, but may ignore certain settings, as shown below. You can see the warning messages in the MySQL error log. When InnoDB strict mode is ON, these specified combinations of options generate errors, and the table is not created or altered. You can see the full description of the error condition with SHOW ERRORS. For example:



ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478)

| Level | Code | Message                                   | 
| Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333.     | 
| Error | 1005 | Can't create table 'test.x' (errno: 1478) | 
2 rows in set (0.00 sec)

Table 13.7. CREATE/ALTER TABLE Warnings and Errors when InnoDB Strict Mode is OFF

SyntaxWarning or Error ConditionResulting ROW_FORMAT, as shown in SHOW TABLE STATUS
ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC or KEY_BLOCK_SIZE is specifiedIgnored unless both innodb_file_format=Barracuda and innodb_file_per_table are enabledCOMPACT
Invalid KEY_BLOCK_SIZE is specified (not 1, 2, 4, 8 or 16)KEY_BLOCK_SIZE is ignoredthe requested one, or COMPACT by default
ROW_FORMAT=COMPRESSED and valid KEY_BLOCK_SIZE are specifiedNone; KEY_BLOCK_SIZE specified is used, not the 8K defaultCOMPRESSED
ROW_FORMAT is not one of REDUNDANT, COMPACT, DYNAMIC or COMPRESSEDIgnored if recognized by the MySQL parser. Otherwise, an error is issued.COMPACT or N/A

When InnoDB strict mode is ON (innodb_strict_mode=1), the InnoDB storage engine rejects invalid ROW_FORMAT or KEY_BLOCK_SIZE parameters. For compatibility with earlier versions of InnoDB,, InnoDB strict mode is not enabled by default; instead, InnoDB issues warnings (not errors) for ignored invalid parameters.

Note that it is not possible to see the chosen KEY_BLOCK_SIZE using SHOW TABLE STATUS. The statement SHOW CREATE TABLE displays the KEY_BLOCK_SIZE (even if it was ignored by InnoDB). The real compressed page size inside InnoDB cannot be displayed by MySQL.

Copyright © 2010-2022 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout