Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.1.18
Description
Let's say that we create a table that does not use page compression, and we insert some data:
CREATE TABLE `tab` (
|
`id` int(11) NOT NULL,
|
`str` varchar(50) DEFAULT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
INSERT INTO tab VALUES (1, 'str1');
|
And then let's say that we want to enable page compression for this table:
SET GLOBAL innodb_compression_algorithm=zlib;
|
ALTER TABLE tab PAGE_COMPRESSED=1;
|
As pointed out in MDEV-11089, the table's existing uncompressed pages are not going to be compressed at this point:
MariaDB [db1]> SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed';
|
+----------------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------------+-------+
|
| Innodb_num_pages_page_compressed | 0 |
|
+----------------------------------+-------+
|
1 row in set (0.00 sec)
|
Since an OPTIMIZE TABLE rewrites the table, I would expect that executing that statement against the table would compress the existing uncompressed pages in the new copy, but it seems that it does not:
MariaDB [db1]> OPTIMIZE TABLE tab;
|
+---------+----------+----------+-------------------------------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+---------+----------+----------+-------------------------------------------------------------------+
|
| db1.tab | optimize | note | Table does not support optimize, doing recreate + analyze instead |
|
| db1.tab | optimize | status | OK |
|
+---------+----------+----------+-------------------------------------------------------------------+
|
2 rows in set (0.03 sec)
|
|
MariaDB [db1]> SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed';
|
+----------------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------------+-------+
|
| Innodb_num_pages_page_compressed | 0 |
|
+----------------------------------+-------+
|
1 row in set (0.00 sec)
|
Copying the table with ALTER TABLE has similar results:
MariaDB [db1]> ALTER TABLE tab ENGINE=InnoDB;
|
Query OK, 0 rows affected (0.02 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
MariaDB [db1]> SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed';
|
+----------------------------------+-------+
|
| Variable_name | Value |
|
+----------------------------------+-------+
|
| Innodb_num_pages_page_compressed | 0 |
|
+----------------------------------+-------+
|
1 row in set (0.00 sec)
|
Attachments
Issue Links
- relates to
-
MDEV-11089 Enabling page compression with ALTER TABLE does not compress existing pages
-
- Closed
-