Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11089

Enabling page compression with ALTER TABLE does not compress existing pages

    XMLWordPrintable

    Details

      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)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              jplindst Jan Lindström
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: