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

mariabackup does not preserve "holes punched" for tables with page_compressed=1

    Details

    • Sprint:
      10.1.32

      Description

      When InnoDB compressed table (created with page_compressed=1 option) is backed up, resulting .ibd file becomes big, holes punched are not preserved.

      Consider the following simple test case:

      [openxs@fc23 maria10.1]$ bin/mysql --socket=/tmp/mariadb.sock -uroot test
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 3
      Server version: 10.1.24-MariaDB Source distribution
       
      Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> show variables like 'innodb_use%';
      +-------------------------------------------+-------+
      | Variable_name                             | Value |
      +-------------------------------------------+-------+
      | innodb_use_atomic_writes                  | OFF   |
      | innodb_use_fallocate                      | ON    |
      | innodb_use_global_flush_log_at_trx_commit | ON    |
      | innodb_use_mtflush                        | OFF   |
      | innodb_use_native_aio                     | ON    |
      | innodb_use_stacktrace                     | OFF   |
      | innodb_use_sys_malloc                     | ON    |
      | innodb_use_trim                           | ON    |
      +-------------------------------------------+-------+
      8 rows in set (0.00 sec)
       
      MariaDB [test]> show variables like 'innodb_file%';
      +--------------------------+-----------+
      | Variable_name            | Value     |
      +--------------------------+-----------+
      | innodb_file_format       | Barracuda |
      | innodb_file_format_check | ON        |
      | innodb_file_format_max   | Barracuda |
      | innodb_file_per_table    | ON        |
      +--------------------------+-----------+
      4 rows in set (0.00 sec)
       
      MariaDB [test]> create table t1(id int auto_increment primary key, c1 varchar(200)) engine=InnoDB page_compressed=1;
      Query OK, 0 rows affected (0.20 sec)
       
      MariaDB [test]> insert into t1(c1) values (repeat('a', 200));
      Query OK, 1 row affected (0.04 sec)
       
      MariaDB [test]> insert into t1(c1) values (repeat('b', 200));                   
      Query OK, 1 row affected (0.04 sec)
       
      MariaDB [test]> insert into t1(c1) select c1 from t1;                           
      Query OK, 2 rows affected (0.04 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into t1(c1) select c1 from t1;
      Query OK, 4 rows affected (0.03 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      ...
       
      MariaDB [test]> insert into t1(c1) select c1 from t1;
      Query OK, 1048576 rows affected (40.24 sec)
      Records: 1048576  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> show table status like 't1'\G
      *************************** 1. row ***************************
                 Name: t1
               Engine: InnoDB
              Version: 10
           Row_format: Dynamic
                 Rows: 2014585
       Avg_row_length: 249
          Data_length: 502218752
      Max_data_length: 0
         Index_length: 0
            Data_free: 5242880
       Auto_increment: 2490316
          Create_time: 2017-06-07 10:37:19
          Update_time: NULL
           Check_time: NULL
            Collation: latin1_swedish_ci
             Checksum: NULL
       Create_options: `page_compressed`=1
              Comment:
      1 row in set (0.00 sec)
       
      MariaDB [test]> show create table t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `c1` varchar(200) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=latin1 `page_compressed`=1
      1 row in set (0.00 sec)
       
      MariaDB [test]> show variables like 'innodb_comp%';                             +------------------------------------------+-------+
      | Variable_name                            | Value |
      +------------------------------------------+-------+
      | innodb_compression_algorithm             | zlib  |
      | innodb_compression_failure_threshold_pct | 5     |
      | innodb_compression_level                 | 6     |
      | innodb_compression_pad_pct_max           | 50    |
      +------------------------------------------+-------+
      4 rows in set (0.01 sec)
       
      MariaDB [test]> show status like 'innodb_page%';
      +----------------------------------------+-----------+
      | Variable_name                          | Value     |
      +----------------------------------------+-----------+
      | Innodb_page_size                       | 16384     |
      | Innodb_pages_created                   | 32929     |
      | Innodb_pages_read                      | 19731     |
      | Innodb_pages0_read                     | 4         |
      | Innodb_pages_written                   | 33225     |
      | Innodb_page_compression_saved          | 385253376 |
      | Innodb_page_compression_trim_sect512   | 0         |
      | Innodb_page_compression_trim_sect1024  | 0         |
      | Innodb_page_compression_trim_sect2048  | 0         |
      | Innodb_page_compression_trim_sect4096  | 94056     |
      | Innodb_page_compression_trim_sect8192  | 0         |
      | Innodb_page_compression_trim_sect16384 | 0         |
      | Innodb_page_compression_trim_sect32768 | 0         |
      +----------------------------------------+-----------+
      13 rows in set (0.00 sec)
       
      MariaDB [test]> \! ls -ls data/test
      total 125992
           4 -rw-rw----. 1 openxs openxs        65 Jun  7 10:14 db.opt
           4 -rw-rw----. 1 openxs openxs      1168 Jun  7 10:37 t1.frm
      125984 -rw-rw----. 1 openxs openxs 528482304 Jun  7 10:41 t1.ibd
      

      So, we have a simple table where page compression helped a lot to save space on disk. Now, let's create a backup like this:

      [openxs@fc23 maria10.1]$ bin/mariabackup --no-defaults --port=3306 --socket=/tmp/mariadb.sock --innodb_use_fallocate=ON --innodb_use_trim=ON --innodb_file_format=Barracuda --user=root --backup --target-dir=/home/openxs/backup
      ...
      xtrabackup: Transaction log of lsn (828462583) to (828462583) was copied.
      170607 11:08:02 completed OK!
       
      [openxs@fc23 maria10.1]$ ls -ls /home/openxs/backup/test/
      total 516108
           4 -rw-rw----. 1 openxs openxs        65 Jun  7 11:07 db.opt
           4 -rw-rw----. 1 openxs openxs      1168 Jun  7 11:07 t1.frm
      516100 -rw-rw----. 1 openxs openxs 528482304 Jun  7 11:07 t1.ibd
       
      [openxs@fc23 maria10.1]$ du -hc  /home/openxs/backup/test/*.ibd
      505M    /home/openxs/backup/test/t1.ibd
      505M    total
      [openxs@fc23 maria10.1]$ du -hc data/test/*.ibd
      124M    data/test/t1.ibd
      124M    total
      

      So, we have 505M now instead of 124M for the compressed table in the datadir.

        Attachments

          Activity

            People

            • Assignee:
              wlad Vladislav Vaintroub
              Reporter:
              valerii Valerii Kravchuk
            • Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: