MySQL Bugs: #72636: Implications of innodb_page_size on compression are undocumented

Bug #72636	Implications of innodb_page_size on compression are undocumented
Submitted:	13 May 2014 19:24	Modified:	27 May 2014 19:22
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	compression, innodb, innodb_page_size, key_block_size, page size, ROW_FORMAT

Description:
The manual fails to document the implications of setting innodb_page_size on compression; namely that the maximum KEY_BLOCK_SIZE is based on innodb_page_size, so:

  * For a configuration with innodb_page_size=8192, use of KEY_BLOCK_SIZE=16 is rejected.
  * For a configuration with innodb_page_size=4096, use of KEY_BLOCK_SIZE=8 and KEY_BLOCK_SIZE=4 are rejected.

They produce the following warning:

Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=N

How to repeat:
mysql> show global variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 8192  |
+------------------+-------+
1 row in set (0.00 sec)

mysql> create table test.s (a int) engine=innodb row_format=compressed key_block_size=16;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> show warnings;
+---------+------+-------------------------------------+
| Level   | Code | Message                             |
+---------+------+-------------------------------------+
| Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=16. |
+---------+------+-------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Update the documentation to explain the interplay between these two features.

Oops, I meant "For a configuration with innodb_page_size=4096, use of KEY_BLOCK_SIZE=8 and KEY_BLOCK_SIZE=16 are rejected."

Note that the page in question is this one:

http://dev.mysql.com/doc/refman/5.6/en/innodb-compression-usage.html

It may also be worth noting there that when an unsupported KEY_BLOCK_SIZE is used (and thus the warning is generated) the actual KEY_BLOCK_SIZE used to create the table is half of innodb_page_size.

Hello Jeremy,

Thank you for the report.

Thanks,
Umesh

The documentation has been updated. The revised content should appear soon, with the next published documentation build.

http://dev.mysql.com/doc/refman/5.6/en/innodb-compression-usage.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-compression-usage.html

http://dev.mysql.com/doc/refman/5.6/en/create-table.html
http://dev.mysql.com/doc/refman/5.7/en/create-table.html

Thank you for the bug report.