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

Comment by Elena Stepanova [ 2016-11-02 ]

Test 1

cnf file
[mysqld]
port=11203
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.

Script to create tables: mdev11203-1.sql

CREATE TABLE `t1` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `k` int(10) unsigned NOT NULL DEFAULT 0,

  `c` char(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB;

INSERT INTO `t1` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

INSERT INTO `t2` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB ENCRYPTED=YES;

INSERT INTO `t3` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=4;

INSERT INTO `t4` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1;

INSERT INTO `t5` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;

INSERT INTO `t6` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1;

INSERT INTO `t7` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;

INSERT INTO `t8` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1;

INSERT INTO `t9` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;

INSERT INTO `t10` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1;

INSERT INTO `t11` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;

INSERT INTO `t12` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1;

INSERT INTO `t13` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;

INSERT INTO `t14` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1;

INSERT INTO `t15` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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(120) NOT NULL DEFAULT '',

  `pad` char(60) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `k` (`k`)

) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES;

INSERT INTO `t16` SELECT NULL, seq%1000, CONCAT('c',seq), CONCAT('pad',seq%100) 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.

MariaDB [test]> select * from t1 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 39199 | 199 | c39199 | pad99 |

+-------+-----+--------+-------+

1 row in set (4.71 sec)

MariaDB [test]> select * from t2 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 23855 | 855 | c23855 | pad55 |

+-------+-----+--------+-------+

1 row in set (4.84 sec)

MariaDB [test]> select * from t3 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 26523 | 523 | c26523 | pad23 |

+-------+-----+--------+-------+

1 row in set (4.62 sec)

MariaDB [test]> select * from t4 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 42478 | 478 | c42478 | pad78 |

+-------+-----+--------+-------+

1 row in set (4.53 sec)

MariaDB [test]> select * from t5 order by rand() limit 1;

+-----+-----+------+-------+

| id  | k   | c    | pad   |

+-----+-----+------+-------+

| 916 | 916 | c916 | pad16 |

+-----+-----+------+-------+

1 row in set (4.51 sec)

MariaDB [test]> select * from t6 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 20242 | 242 | c20242 | pad42 |

+-------+-----+--------+-------+

1 row in set (4.65 sec)

MariaDB [test]> select * from t7 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 75286 | 286 | c75286 | pad86 |

+-------+-----+--------+-------+

1 row in set (4.75 sec)

MariaDB [test]> select * from t8 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 59576 | 576 | c59576 | pad76 |

+-------+-----+--------+-------+

1 row in set (4.54 sec)

MariaDB [test]> select * from t9 order by rand() limit 1;

+-------+----+--------+-------+

| id    | k  | c      | pad   |

+-------+----+--------+-------+

| 79098 | 98 | c79098 | pad98 |

+-------+----+--------+-------+

1 row in set (4.70 sec)

MariaDB [test]> select * from t10 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 36748 | 748 | c36748 | pad48 |

+-------+-----+--------+-------+

1 row in set (4.60 sec)

MariaDB [test]> select * from t11 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 82882 | 882 | c82882 | pad82 |

+-------+-----+--------+-------+

1 row in set (4.51 sec)

MariaDB [test]> select * from t12 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 31198 | 198 | c31198 | pad98 |

+-------+-----+--------+-------+

1 row in set (4.52 sec)

MariaDB [test]> select * from t13 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 85951 | 951 | c85951 | pad51 |

+-------+-----+--------+-------+

1 row in set (4.51 sec)

MariaDB [test]> select * from t14 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 10623 | 623 | c10623 | pad23 |

+-------+-----+--------+-------+

1 row in set (4.49 sec)

MariaDB [test]> select * from t15 order by rand() limit 1;

+-------+-----+--------+-------+

| id    | k   | c      | pad   |

+-------+-----+--------+-------+

| 64817 | 817 | c64817 | pad17 |

+-------+-----+--------+-------+

1 row in set (4.62 sec)

MariaDB [test]> select * from t16 order by rand() limit 1;

+------+-----+-------+-------+

| id   | k   | c     | pad   |

+------+-----+-------+-------+

| 3828 | 828 | c3828 | pad28 |

+------+-----+-------+-------+

1 row in set (4.41 sec)

Comment by Elena Stepanova [ 2016-11-02 ]

Test 2

RQG combinations file
$combinations = [
[
'
--no-mask
--seed=time
--threads=8
--duration=600
--queries=100M
--reporters=QueryTimeout,Backtrace,ErrorLog,Deadlock,RestartConsistency
--restart-timeout=30
--mysqld=--log_output=FILE
--grammar=conf/mariadb/innodb_compression_encryption.yy
--gendata=conf/mariadb/innodb_compression_encryption.zz
--mysqld=--innodb-use-atomic-writes
--mysqld=--innodb-use-trim
--mysqld=--innodb_flush_method=O_DIRECT
--mysqld=--innodb_doublewrite=0
--mysqld=--plugin-load-add=file_key_management.so
--mysqld=--loose-file-key-management-filename=$RQG_HOME/conf/mariadb/encryption_keys.txt
--mysqld=--innodb-encryption-threads=4
'],
[ '','--mysqld=--innodb_use_fallocate' ],
[ '','--mysqld=--innodb-encrypt-log' ],
[ '','--mysqld=--innodb-encrypt-tables' ],
];

RQG combinations, grammar and data template:
innodb_compression_encryption.zz innodb_compression_encryption.yy innodb_compression_encryption.cc

It means that 8 test runs are configured.
Options from the first section apply to every test run. Options from other sections are combined:

none

--mysqld=--innodb-encrypt-tables

--mysqld=--innodb-encrypt-log

--mysqld=--innodb-encrypt-log --mysqld=--innodb-encrypt-tables

--mysqld=--innodb_use_fallocate

--mysqld=--innodb_use_fallocate --mysqld=--innodb-encrypt-log

--mysqld=--innodb-encrypt-tables --mysqld=--innodb-encrypt-tables

--mysqld=--innodb_use_fallocate --mysqld=--innodb-encrypt-log --mysqld=--innodb-encrypt-tables

Initially each test creates 8 tables with the structure as in the test 1, without any table options, and populates them with different number of values (from 1000 to 50,000).
The test runs 8 client threads for 10 min.
The test flow randomly

deletes single rows from initial tables;
updates single rows in initial tables;
inserts single rows into initial tables;
selects from initial tables;
creates additional tables LIKE initial tables;
creates additional tables as CREATE ... SELECT from initial tables;
drops additional tables;
flushes tables;
- runs empty ALTER on initial tables (ALTER TABLE <table>;
- runs ALTER .. FORCE on initial tables;
alters initial tables using combinations of table options (some combinations are invalid). The following options are combined randomly:
- ENCRYPTED=YES|NO
- ENCRYPTION_KEY_ID=1|2|33|4|5|6
- ROW_FORMAT=COMPRESSED|COMPACT|DYNAMIC
- PAGE_COMPRESSED=0|1
changes the value of innodb_compression_algorithm to zlib | lz4 | lzo | lzma | bzip2 | snappy | none

After the test flow has finished, the test

takes a data dump from the server;
restarts the server on the same datadir;
checks that it got restarted;
takes another data dump from the server;
compares dumps.

Pass criteria

there are no crashes, deadlocks or detected corruption during test flow;
server gets shut down normally;
server gets restarted without errors;
data dumps before and after restart are identical.

RESULT:

Out of 8 tests, 2 tests failed with the same kind of failure. Each time one of tables had different AUTO_INCREMENT value in SHOW CREATE before and after test run, like this:

-) ENGINE=InnoDB AUTO_INCREMENT=655556688 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC `ENCRYPTED`=YES `PAGE_COMPRESSED`=1 `ENCRYPTION_KEY_ID`=2;

+) ENGINE=InnoDB AUTO_INCREMENT=2122842114 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC `ENCRYPTED`=YES `PAGE_COMPRESSED`=1 `ENCRYPTION_KEY_ID`=2;

Preliminary analysis
Based on the actual table data, the after value seems to be correct. It can also be a test problem (a race condition of some sort).
While it needs to be eventually investigated, it does not seem to be related to the scope of this task.

Comment by Jan Lindström (Inactive) [ 2016-11-03 ]

Did trim work? Innodb_num_page_compressed_trim_op should be > 0 eventually or something is not correct. I have used following setup :

--loose-innodb-use-native-aio=1

--loose-innodb-use-trim=1

--loose-innodb-use-fallocate=1

Comment by Elena Stepanova [ 2016-11-03 ]

In the first test, apparently trim didn't work, although I'll re-run it just in case (I've realized it should have been show GLOBAL status, maybe there will be a difference).
But maybe there was nothing to trim, since the test only does INSERTs? Or maybe I didn't wait long enough, if it's a background job?

The second test does not check status variables, but I can make it happen.

Comment by Elena Stepanova [ 2016-11-03 ]

Test 3

I've modified "Test 1"to hopefully get more chance for trim. The tables now have this structure:

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))

And INSERTs do this:

INSERT INTO `t1` SELECT NULL, seq%1000, REPEAT('c',255), CONCAT('pad',seq%100,REPEAT('*',1024)) FROM seq_1_to_100000;

The config was also modified a bit:

[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

And I'm still getting 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)

So, it seems the core problem is that trim does not work at all on 10.2?

Comment by Elena Stepanova [ 2017-04-23 ]

jplindst, marko, do you feel like it's time to resume the tests? And given the overall status of the feature, is it to be done by 10.2 GA?
They were stalled due to ~~MDEV-11254~~, which has now been fixed, but I see that ~~MDEV-11232~~ which is also linked to this task is still open.

Comment by Marko Mäkelä [ 2021-10-26 ]

elenst, sorry, I had missed your question back then. I encountered this ticket while searching for something else.

The logic to detect atomic writes should now be automatic and actually work correctly (~~MDEV-26042~~). But, special hardware is still needed for testing that, and additional access permissions directly to the block device are needed as well, for ioctl() to work. A sysfs interface similar to /sys/block/*/queue/rotational (~~MDEV-17380~~) would be much nicer.

I last tested this related to ~~MDEV-26029~~ and ~~MDEV-26192~~ some months ago.

Should we acquire and use suitable hardware in some of our internal stress tests?

Comment by Elena Stepanova [ 2021-11-02 ]

marko, mleich, maybe it would better belong to the InnoDB team then?
Matthias, do you want to take over and request the needed hardware to use in your internal stress tests?

Comment by Marko Mäkelä [ 2021-11-03 ]

elenst, yes, I think that this belongs to our team. I think that it suffices to run a one-time test campaign. I think that the hardware will be most useful in an environment that is primarily used for performance tests.

[MDEV-11203] Test with atomic_writes (FusionIO, shannon ssd) Created: 2016-11-01 Updated: 2021-11-03
Status:	Stalled
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB, Tests
Fix Version/s:	N/A