Description:
Hello dear experts,
Reading -> https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html
I have installed MySQL from yum repo and use MySQL Sandbox to start from binary.
On binary setup:
mysql> alter table sbtest1 compression='zlib';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> optimize table sbtest1;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+-------------------------------------------------------------------+
| dbtest.sbtest1 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| dbtest.sbtest1 | optimize | status | OK |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set, 1 warning (19.65 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------+
| Warning | 138 | XPunch hole not supported by the file system. Compression disabled for 'dbtest/#sql-ib40-641472591' |
+---------+------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Warning message -> XPunch hole not supported by the file system is not relevant because I can use compression with my regular MySQL installation, so the problem is not with OS file system.
mysql> alter table sbtest1 compression='zlib';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> optimize table sbtest1;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+-------------------------------------------------------------------+
| dbtest.sbtest1 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| dbtest.sbtest1 | optimize | status | OK |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (29.87 sec)
Same thing can be figured out by querying information_schema:
From binary setup:
mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
| 52 | dbtest/sbtest1 | 4096 | 285212672 | 285212672 |
+-------+---------------------------------+---------------+-----------+----------------+
From regular:
mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES;
+-------+---------------------------------+---------------+-----------+----------------+
| SPACE | NAME | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+---------------------------------+---------------+-----------+----------------+
| 37 | dbtest/sbtest1 | 4096 | 285212672 | 223473664 |
+-------+---------------------------------+---------------+-----------+----------------+
How to repeat:
See description
Suggested fix:
If it is not a bug please update DOC to give this as an "Important Note".
I would like to thank @Sergei Glushchenko giving me this happiness to investigate and report :)
