[MDEV-11089] Enabling page compression with ALTER TABLE does not compress existing pages Created: 2016-10-19  Updated: 2022-01-12  Resolved: 2016-10-24

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.1.18
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: innodb, need_feedback, page_compression

Attachments: Text File global_status.txt     Text File session_variables.txt    
Issue Links:
Relates
relates to MDEV-11090 Copying table with OPTIMIZE/ALTER TAB... Closed

 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;

Many users would expect the table's existing pages to be compressed after this point, but it appears that they are not:

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)

Is this a bug, or a known limitation of page compression? If it's the latter, then it would be good to have that documented:

https://mariadb.com/kb/en/mariadb/compression/

I do see that new inserts into the table do result in compressed pages:

MariaDB [db1]> INSERT INTO tab VALUES (2, 'str2');
 
Query OK, 1 row affected (0.00 sec)
 
 
 
MariaDB [db1]> SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed';
 
+----------------------------------+-------+
 
| Variable_name                    | Value |
 
+----------------------------------+-------+
 
| Innodb_num_pages_page_compressed | 3     |
 
+----------------------------------+-------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2016-10-21 ]

Works for me:

MariaDB [test]> CREATE TABLE `tab` (
 
    ->   `id` int(11) NOT NULL,
 
    ->   `str` varchar(50) DEFAULT NULL,
 
    ->   PRIMARY KEY (`id`)
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.38 sec)
 
 
 
MariaDB [test]> INSERT INTO tab VALUES (1, 'str1');
 
Query OK, 1 row affected (0.08 sec)
 
 
 
MariaDB [test]> 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)


MariaDB [test]> SET GLOBAL innodb_compression_algorithm=zlib;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> ALTER TABLE tab PAGE_COMPRESSED=1;
 
Query OK, 1 row affected (0.95 sec)                
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show global status like 'Innodb_num_pages_page_compressed';
 
+----------------------------------+-------+
 
| Variable_name                    | Value |
 
+----------------------------------+-------+
 
| Innodb_num_pages_page_compressed | 3     |
 
+----------------------------------+-------+
 
1 row in set (0.00 sec)


MariaDB [test]> select @@version;
 
+-----------------+
 
| @@version       |
 
+-----------------+
 
| 10.1.18-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Could you please attach the output from SHOW VARIABLES and SHOW GLOBAL STATUS from the session where you are performing your test?

Comment by Geoff Montee (Inactive) [ 2016-10-21 ]

Hi elenst,

It looks as though there is a delay in the pages getting compressed for me:

MariaDB [db1]> CREATE TABLE `tab` (
 
    ->   `id` int(11) NOT NULL,
 
    ->   `str` varchar(50) DEFAULT NULL,
 
    ->   PRIMARY KEY (`id`)
 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [db1]> INSERT INTO tab VALUES (1, 'str1');
 
Query OK, 1 row affected (0.00 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)
 
 
 
MariaDB [db1]> SET GLOBAL innodb_compression_algorithm=zlib;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [db1]> ALTER TABLE tab PAGE_COMPRESSED=1;
 
Query OK, 1 row affected (0.02 sec)
 
Records: 1  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.01 sec)
 
 
 
MariaDB [db1]> SELECT SLEEP(10);
 
+-----------+
 
| SLEEP(10) |
 
+-----------+
 
|         0 |
 
+-----------+
 
1 row in set (10.00 sec)
 
 
 
MariaDB [db1]> SHOW GLOBAL STATUS LIKE 'Innodb_num_pages_page_compressed';
 
+----------------------------------+-------+
 
| Variable_name                    | Value |
 
+----------------------------------+-------+
 
| Innodb_num_pages_page_compressed | 3     |
 
+----------------------------------+-------+
 
1 row in set (0.00 sec)

Is this because it takes time for the pages to be flushed to disk?

I'll attach that information for you.

Comment by Jan Lindström (Inactive) [ 2016-10-24 ]

Dirty pages after compression are not flushed to the disk immediately, when they are flushed depends the size of the buffer pool and setting for percentage of dirty pages. Compression happens only before dirty pages are flushed to the disk thus innodb_num_pages_page_compressed could be zero after alter table.

Comment by VAROQUI Stephane [ 2022-01-12 ]

please innodb_max_dirty_pages_pct=0 if you wan't to see the effect of page compression right away

Generated at Thu Feb 08 07:47:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.