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

Copying table with OPTIMIZE/ALTER TABLE does not compress existing uncompressed 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;
      

      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

          Activity

            People

              jplindst Jan Lindström (Inactive)
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.