[MDEV-11254] innodb-use-trim has no effect in 10.2 - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2
Fix Version/s: 10.2.4, 10.3.0
Component/s: Storage Engine - InnoDB
Labels:
- 10.2-rc

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

blocks

MDEV-11203 Test with atomic_writes (FusionIO, shannon ssd)

Stalled

causes

MDEV-12948 ERROR in log file: IOCTL_STORAGE_QUERY_PROPERTY

Closed

Activity

People

Assignee:: Jan Lindström (Inactive)

Reporter:: Elena Stepanova

Votes:: 1 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-11-08 12:47

Updated:: 2020-05-05 06:14

Resolved:: 2017-01-24 12:49

Git Integration

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