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

mariabackup breaks page compression

    XMLWordPrintable

    Details

      Description

      As InnoDB page compression uses “hole punching,” and it seems like ignored by somehow. mariabackup SST method leaves compressed data from donor to decompressed on joiner node. mariabackup SST not copying compressed files so page compression seems not beneficial in this case.

      MariaDB [tt]> show create table t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        `msg` varchar(100) DEFAULT NULL,
        UNIQUE KEY `id` (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=34209256 DEFAULT CHARSET=latin1 `PAGE_COMPRESSED`='ON'
      1 row in set (0.005 sec)
       
      +--------------+------------+------------+
      | TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT |
      +--------------+------------+------------+
      | tt           | t1         | Dynamic    |
      +--------------+------------+------------+
       
      MariaDB [tt]> show global variables like '%compression%';
      +------------------------------------------+------------------+
      | Variable_name                            | Value            |
      +------------------------------------------+------------------+
      | column_compression_threshold             | 100              |
      | column_compression_zlib_level            | 6                |
      | column_compression_zlib_strategy         | DEFAULT_STRATEGY |
      | column_compression_zlib_wrap             | OFF              |
      | innodb_compression_algorithm             | zlib             |
      | innodb_compression_default               | ON               |
      | innodb_compression_failure_threshold_pct | 5                |
      | innodb_compression_level                 | 1                |
      | innodb_compression_pad_pct_max           | 50               |
      +------------------------------------------+------------------+
      9 rows in set (0.001 sec)
      

      page compression is enabled. Where size of tablespace file is:

      $ ls -lrth /var/lib/mysql/tt/t1.ibd
      2.1G /var/lib/mysql/tt/t1.ibd
       
      $ du -hs --block-size=1 /var/lib/mysql/tt/t1.ibd
      1098792960	/var/lib/mysql/tt/t1.ibd
       
      $ du -hs /var/lib/mysql/tt/t1.ibd
      1.1G	/var/lib/mysql/tt/t1.ibd
      

      Now, let's try mariabackup:

      mariabackup --backup --target-dir=/home/vagrant/backup/ -u root -p'xxxx'
      .
      [00] 2021-05-19 20:08:13 completed OK!
      

      Let's verify backup table t1 tablespace file size again:

      $ ls -la /home/vagrant/backup/tt/t1.ibd
      -rw-r-----. 1 root root 2231369728 May 19 20:08 /home/vagrant/backup/tt/t1.ibd
       
      $ du -hs --block-size=1 /home/vagrant/backup/tt/t1.ibd
      2214154240	/home/vagrant/backup/tt/t1.ibd
       
      $ du -hs /home/vagrant/backup/tt/t1.ibd
      2.1G	/home/vagrant/backup/tt/t1.ibd
      

      As per https://dev.mysql.com/doc/refman/5.7/en/innodb-page-compression.html
      Preserving page compression when moving a page-compressed tablespace file from one host to another requires a utility that preserves sparse files

      I think that is related xtrabackup report https://jira.percona.com/browse/PXB-1557

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              muhammad.irfan Muhammad Irfan
              Votes:
              5 Vote for this issue
              Watchers:
              12 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration