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:
CREATE TABLEname(column1 INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
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 13.7.3.4.2.2, “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).
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:
| Level | Code | Message |
|---|---|---|
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires
innodb_file_per_table. |
| Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires
innodb_file_format=1. |
| Warning | 1478 | InnoDB: ignoring
KEY_BLOCK_SIZE= |
| Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires
innodb_file_per_table. |
| Warning | 1478 | InnoDB: 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 13.7.8.4, “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,
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).
| Level | Code | Message |
|---|---|---|
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE= |
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
| Option | Usage | Description |
|---|---|---|
ROW_FORMAT=REDUNDANT | Storage format used prior to MySQL 5.0.3 | Less efficient than ROW_FORMAT=COMPACT; for backward compatibility |
ROW_FORMAT=COMPACT | Default storage format since MySQL 5.0.3 | Stores 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=DYNAMIC | Available only with
innodb_file_format=Barracuda | Store 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=COMPRESSED | Available only with
innodb_file_format=Barracuda | Compresses the table and indexes using zlib to default compressed page
size of 8K bytes; implies ROW_FORMAT=DYNAMIC |
KEY_BLOCK_SIZE= | Available only with
innodb_file_format=Barracuda | Specifies 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:
mysql>CREATE TABLE x (id INT PRIMARY KEY, c INT)->ENGINE=INNODB KEY_BLOCK_SIZE=33333;ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478) mysql>SHOW ERRORS;+-------+------+-------------------------------------------+ | 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
| Syntax | Warning or Error Condition | Resulting ROW_FORMAT, as shown in SHOW TABLE STATUS |
|---|---|---|
ROW_FORMAT=REDUNDANT | None | REDUNDANT |
ROW_FORMAT=COMPACT | None | COMPACT |
ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC or KEY_BLOCK_SIZE is
specified | Ignored unless both innodb_file_format=Barracuda
and innodb_file_per_table are enabled | COMPACT |
Invalid KEY_BLOCK_SIZE is specified (not 1, 2, 4, 8 or 16) | KEY_BLOCK_SIZE is ignored | the requested one, or COMPACT by default |
ROW_FORMAT=COMPRESSED and valid KEY_BLOCK_SIZE are specified | None; KEY_BLOCK_SIZE specified is used, not the 8K default | COMPRESSED |
KEY_BLOCK_SIZE is specified with REDUNDANT, COMPACT or DYNAMIC
row format | KEY_BLOCK_SIZE is ignored | REDUNDANT, COMPACT or DYNAMIC |
ROW_FORMAT is not one of REDUNDANT, COMPACT, DYNAMIC or
COMPRESSED | Ignored 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.