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

innodb-use-trim has no effect in 10.2

    XMLWordPrintable

    Details

    • Sprint:
      10.2.4-5

      Description

      Tests were initially described in MDEV-11203 (test 3), I'm copying the description here for the convenience.

      cnf file

      [mysqld]
      innodb-use-atomic-writes
      innodb_use_fallocate
      innodb_flush_method=O_DIRECT
      innodb-use-trim
      innodb_doublewrite=0
      plugin-load-add=file_key_management.so
      file-key-management-filename=/home/elenst/10.2/mysql-test/std_data/keys.txt
      innodb-encryption-threads=4
      

      MariaDB [test]> select @@datadir;
      +------------------------------+
      | @@datadir                    |
      +------------------------------+
      | /mnt/dfs/elenst/mdev11203-2/ |
      +------------------------------+
      1 row in set (0.00 sec)
      

      Create and populate tables.
      All tables have the same structure.
      The difference is in table options and compression algorithm where applicable.
      Every table is populated with 100,000 rows.

      CREATE TABLE `t1` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB;
       
      INSERT INTO `t1` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t2` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
       
      INSERT INTO `t2` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t3` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB ENCRYPTED=YES;
       
      INSERT INTO `t3` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t4` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=4;
       
      INSERT INTO `t4` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      SET GLOBAL innodb_compression_algorithm = zlib;
       
      CREATE TABLE `t5` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1;
       
      INSERT INTO `t5` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t6` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;
       
      INSERT INTO `t6` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      SET GLOBAL innodb_compression_algorithm = lz4;
       
      CREATE TABLE `t7` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1;
       
      INSERT INTO `t7` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t8` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;
       
      INSERT INTO `t8` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      SET GLOBAL innodb_compression_algorithm = lzo;
       
      CREATE TABLE `t9` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1;
       
      INSERT INTO `t9` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t10` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;
       
      INSERT INTO `t10` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      SET GLOBAL innodb_compression_algorithm = lzma;
       
      CREATE TABLE `t11` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1;
       
      INSERT INTO `t11` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t12` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;
       
      INSERT INTO `t12` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      SET GLOBAL innodb_compression_algorithm = bzip2;
       
      CREATE TABLE `t13` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1;
       
      INSERT INTO `t13` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t14` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;
       
      INSERT INTO `t14` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      SET GLOBAL innodb_compression_algorithm = snappy;
       
      CREATE TABLE `t15` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1;
       
      INSERT INTO `t15` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      CREATE TABLE `t16` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `k` int(10) unsigned NOT NULL DEFAULT 0,
        `c` char(255) NOT NULL DEFAULT '',
        `pad` varchar(2048) NOT NULL DEFAULT '',
        PRIMARY KEY (`id`),
        KEY `k` (`k`),
        KEY `pad` (`pad`(128))
      ) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;
       
      INSERT INTO `t16` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;
      

      Wait for a bit, check the status:

      MariaDB [test]> show status like 'Innodb_%compress%';
      +------------------------------------------+-----------+
      | Variable_name                            | Value     |
      +------------------------------------------+-----------+
      | Innodb_page_compression_saved            | 177160192 |
      | Innodb_page_compression_trim_sect512     | 0         |
      | Innodb_page_compression_trim_sect1024    | 0         |
      | Innodb_page_compression_trim_sect2048    | 0         |
      | Innodb_page_compression_trim_sect4096    | 0         |
      | Innodb_page_compression_trim_sect8192    | 0         |
      | Innodb_page_compression_trim_sect16384   | 0         |
      | Innodb_page_compression_trim_sect32768   | 0         |
      | Innodb_num_pages_page_compressed         | 15784     |
      | Innodb_num_page_compressed_trim_op       | 0         |
      | Innodb_num_page_compressed_trim_op_saved | 0         |
      | Innodb_num_pages_page_decompressed       | 30920     |
      | Innodb_num_pages_page_compression_error  | 3934      |
      | Innodb_defragment_compression_failures   | 0         |
      +------------------------------------------+-----------+
      14 rows in set (0.01 sec)
      

      MariaDB [test]> show status like 'Innodb_%encrypt%';
      +--------------------------------------------------+-------+
      | Variable_name                                    | Value |
      +--------------------------------------------------+-------+
      | Innodb_num_pages_encrypted                       | 13111 |
      | Innodb_encryption_rotation_pages_read_from_cache | 0     |
      | Innodb_encryption_rotation_pages_read_from_disk  | 0     |
      | Innodb_encryption_rotation_pages_modified        | 0     |
      | Innodb_encryption_rotation_pages_flushed         | 0     |
      | Innodb_encryption_rotation_estimated_iops        | 0     |
      +--------------------------------------------------+-------+
      6 rows in set (0.01 sec)
      

      +-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
      | SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID |
      +-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
      |     2 | mysql/innodb_table_stats |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |     3 | mysql/innodb_index_stats |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |     4 | mysql/gtid_slave_pos     |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |     6 | test/t1                  |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |     7 | test/t2                  |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |     8 | test/t3                  |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
      |     9 | test/t4                  |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              4 |
      |    10 | test/t5                  |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |    11 | test/t6                  |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
      |    12 | test/t7                  |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |    13 | test/t8                  |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
      |    14 | test/t9                  |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |    15 | test/t10                 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
      |    16 | test/t11                 |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |    17 | test/t12                 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
      |    18 | test/t13                 |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |    19 | test/t14                 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
      |    20 | test/t15                 |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              0 |
      |    21 | test/t16                 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
      |     0 | NULL                     |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |              1 |
      +-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
      20 rows in set (0.00 sec)
      

      Restart the server.
      Select from tables.

      RESULT:

      Server restarted without errors.
      All tables are readable.
      No crashes.

      However, there is no trim at all on 10.2:

      10.2 status

      MariaDB [(none)]> show global status like '%compress%';
      +------------------------------------------+------------+
      | Variable_name                            | Value      |
      +------------------------------------------+------------+
      | Compression                              | OFF        |
      | Innodb_page_compression_saved            | 1437753344 |
      | Innodb_page_compression_trim_sect512     | 0          |
      | Innodb_page_compression_trim_sect1024    | 0          |
      | Innodb_page_compression_trim_sect2048    | 0          |
      | Innodb_page_compression_trim_sect4096    | 0          |
      | Innodb_page_compression_trim_sect8192    | 0          |
      | Innodb_page_compression_trim_sect16384   | 0          |
      | Innodb_page_compression_trim_sect32768   | 0          |
      | Innodb_num_pages_page_compressed         | 119847     |
      | Innodb_num_page_compressed_trim_op       | 0          |
      | Innodb_num_page_compressed_trim_op_saved | 0          |
      | Innodb_num_pages_page_decompressed       | 238921     |
      | Innodb_num_pages_page_compression_error  | 26827      |
      | Innodb_defragment_compression_failures   | 0          |
      +------------------------------------------+------------+
      

      On 10.1, the exact same test with the same config (with additional innodb_file_format=Barracuda, which is default in 10.2) clearly causes trimming:

      10.1 status

      MariaDB [(none)]> show global status like '%compress%';
      +------------------------------------------+------------+
      | Variable_name                            | Value      |
      +------------------------------------------+------------+
      | Compression                              | OFF        |
      | Innodb_page_compression_saved            | 1449443328 |
      | Innodb_page_compression_trim_sect512     | 0          |
      | Innodb_page_compression_trim_sect1024    | 0          |
      | Innodb_page_compression_trim_sect2048    | 0          |
      | Innodb_page_compression_trim_sect4096    | 302313     |
      | Innodb_page_compression_trim_sect8192    | 0          |
      | Innodb_page_compression_trim_sect16384   | 0          |
      | Innodb_page_compression_trim_sect32768   | 0          |
      | Innodb_num_pages_page_compressed         | 120871     |
      | Innodb_num_page_compressed_trim_op       | 101120     |
      | Innodb_num_page_compressed_trim_op_saved | 33582      |
      | Innodb_num_pages_page_decompressed       | 240934     |
      | Innodb_num_pages_page_compression_error  | 27398      |
      | Innodb_defragment_compression_failures   | 0          |
      +------------------------------------------+------------+
      15 rows in set (0.01 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              jplindst Jan Lindström
              Reporter:
              elenst Elena Stepanova
              Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: