[MDEV-11254] innodb-use-trim has no effect in 10.2 Created: 2016-11-08  Updated: 2020-05-05  Resolved: 2017-01-24

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2
Fix Version/s: 10.2.4, 10.3.0

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 1
Labels: 10.2-rc

Issue Links:
Blocks
blocks MDEV-11203 Test with atomic_writes (FusionIO, sh... Stalled
Problem/Incident
causes MDEV-12948 ERROR in log file: IOCTL_STORAGE_QUER... Closed
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)



 Comments   
Comment by Jan Lindström (Inactive) [ 2017-01-10 ]

bb-10.2-mdev11254

Comment by Marko Mäkelä [ 2017-01-11 ]

I think that this needs some more work.

Comment by Jan Lindström (Inactive) [ 2017-01-23 ]

Tried to address your review comments and clean up the implementation (Windows
implementation by ~wlad)

http://lists.askmonty.org/pipermail/commits/2017-January/010482.html

Comment by Marko Mäkelä [ 2017-01-23 ]

It is somewhat better, but I think that some more clean-up is needed.

Comment by Jan Lindström (Inactive) [ 2017-01-24 ]

commit 6495806e59cc27313375fa8d431b7b8e777f73ff
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Tue Jan 24 14:40:58 2017 +0200

MDEV-11254: innodb-use-trim has no effect in 10.2

Problem was that implementation merged from 10.1 was incompatible
with InnoDB 5.7.

buf0buf.cc: Add functions to return should we punch hole and
how big.

buf0flu.cc: Add written page to IORequest

fil0fil.cc: Remove unneeded status call and add test is
sparse files and punch hole supported by file system when
tablespace is created. Add call to get file system
block size. Used file node is added to IORequest. Added
functions to check is punch hole supported and setting
punch hole.

ha_innodb.cc: Remove unneeded status variables (trim512-32768)
and trim_op_saved. Deprecate innodb_use_trim and
set it ON by default. Add function to set innodb-use-trim
dynamically.

dberr.h: Add error code DB_IO_NO_PUNCH_HOLE
if punch hole operation fails.

fil0fil.h: Add punch_hole variable to fil_space_t and
block size to fil_node_t.

os0api.h: Header to helper functions on buf0buf.cc and
fil0fil.cc for os0file.h

os0file.h: Remove unneeded m_block_size from IORequest
and add bpage to IORequest to know actual size of
the block and m_fil_node to know tablespace file
system block size and does it support punch hole.

os0file.cc: Add function punch_hole() to IORequest
to do punch_hole operation,
get the file system block size and determine
does file system support sparse files (for punch hole).

page0size.h: remove implicit copy disable and
use this implicit copy to implement copy_from()
function.

buf0dblwr.cc, buf0flu.cc, buf0rea.cc, fil0fil.cc, fil0fil.h,
os0file.h, os0file.cc, log0log.cc, log0recv.cc:
Remove unneeded write_size parameter from fil_io
calls.

srv0mon.h, srv0srv.h, srv0mon.cc: Remove unneeded
trim512-trim32678 status variables. Removed
these from monitor tests.

Comment by Marko Mäkelä [ 2020-05-05 ]

I finally removed the data field buf_page_t::write_size, which became unused in MariaDB Server 10.2.4 due to this fix.

Generated at Thu Feb 08 07:48:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.