The CREATE TABLE attribute page_compressed that was introduced in MariaDB 10.1.3 has no effect when innodb_doublewrite=0. This is because the actual compression only takes place within the doublewrite buffer code path.
The purpose of the doublewrite buffer is to guarantee crash-safety in the event that InnoDB is killed in the middle of a page write. Because it could become a performance bottleneck for write-heavy workloads, some users may choose to set innodb_doublewrite=0 and take the risk that crash recovery may fail. (The page should be referred to by the redo log, and a log checkpoint should not be made while a page write is still in progress, so the page corruption should be detected at InnoDB startup.)
Attachments
Issue Links
relates to
MDEV-15529IMPORT TABLESPACE unnecessarily uses the doublewrite buffer
Marko Mäkelä
added a comment - To repeat this, run:
./mtr --mysqld=--skip-innodb-doublewrite encryption.innodb-discard-import
And observe the test failure:
CURRENT_TEST: encryption.innodb-discard-import
--- /mariadb/10.2/mysql-test/suite/encryption/r/innodb-discard-import.result 2017-05-10 14:14:26.228207275 +0300
+++ /mariadb/10.2/mysql-test/suite/encryption/r/innodb-discard-import.reject 2018-03-09 14:32:57.500763121 +0200
@@ -129,6 +129,6 @@
# t3 yes on expecting NOT FOUND
NOT FOUND /tmpres/ in t3.ibd
# t4 yes on expecting NOT FOUND
-NOT FOUND /mysql/ in t4.ibd
+FOUND 484 /mysql/ in t4.ibd
DROP PROCEDURE innodb_insert_proc;
DROP TABLE t1,t2,t3,t4;
mysqltest: Result content mismatch
MDEV-15529 will disable this part of the test.
insert into t1 values(current_num, repeat('maria', 30));
set current_num = current_num + 1;
end while;
end//
delimiter ;//
commit;
set autocommit=0;
call innodb_insert_proc(2000);
commit;
set autocommit=1;
SELECT COUNT(*) from t1;
-- let SEARCH_PATTERN=maria
-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t1.ibd
--source include/search_pattern_in_file.inc
FLUSH TABLES t1 FOR EXPORT;
let MYSQLD_DATADIR=`select @@datadir`;
perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("test", "t1");
EOF
UNLOCK TABLES;
SELECT SPACE FROM INFORMATION_SCHEMA.innodb_sys_tables where name = "test/t1";
drop table t1;
create table t1(c1 bigint not null, b char(200)) engine=innodb page_compressed=1;
ALTER TABLE t1 DISCARD TABLESPACE;
--list_files $MYSQLD_DATADIR/test
perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("test", "t1");
ib_restore_tablespaces("test", "t1");
EOF
--list_files $MYSQLD_DATADIR/test
SELECT SPACE FROM INFORMATION_SCHEMA.innodb_sys_tables where name ="test/t1";
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT COUNT(*) from t1;
SHOW CREATE TABLE t1;
-- let SEARCH_PATTERN=maria
-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t1.ibd
--source include/search_pattern_in_file.inc
drop procedure innodb_insert_proc;
drop table t1;
The above test case can able to repeat the scenario.
String value("maria") found only in root page of clustered index. (3rd page)
err = btr_root_adjust_on_import(index);
The above function call is omitted then t1.ibd doesn't contain maria. Need to check.
Thirunarayanan Balathandayuthapani
added a comment -
--source include/have_innodb.inc
let $innodb_compression_algo = `SELECT @@innodb_compression_algorithm`;
SET GLOBAL innodb_compression_algorithm = 1;
--let $MYSQLD_DATADIR = `SELECT @@datadir`
create table t1(c1 bigint not null, b char(200)) engine=innodb page_compressed=1;
show create table t1;
delimiter //;
create procedure innodb_insert_proc (repeat_count int)
begin
declare current_num int;
set current_num = 0;
while current_num < repeat_count do
insert into t1 values(current_num, repeat('maria', 30));
set current_num = current_num + 1;
end while;
end//
delimiter ;//
commit;
set autocommit=0;
call innodb_insert_proc(2000);
commit;
set autocommit=1;
SELECT COUNT(*) from t1;
-- let SEARCH_PATTERN=maria
-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t1.ibd
--source include/search_pattern_in_file.inc
FLUSH TABLES t1 FOR EXPORT;
let MYSQLD_DATADIR=`select @@datadir`;
perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_backup_tablespaces("test", "t1");
EOF
UNLOCK TABLES;
SELECT SPACE FROM INFORMATION_SCHEMA.innodb_sys_tables where name = "test/t1";
drop table t1;
create table t1(c1 bigint not null, b char(200)) engine=innodb page_compressed=1;
ALTER TABLE t1 DISCARD TABLESPACE;
--list_files $MYSQLD_DATADIR/test
perl;
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
ib_discard_tablespaces("test", "t1");
ib_restore_tablespaces("test", "t1");
EOF
--list_files $MYSQLD_DATADIR/test
SELECT SPACE FROM INFORMATION_SCHEMA.innodb_sys_tables where name ="test/t1";
ALTER TABLE t1 IMPORT TABLESPACE;
SELECT COUNT(*) from t1;
SHOW CREATE TABLE t1;
-- let SEARCH_PATTERN=maria
-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t1.ibd
--source include/search_pattern_in_file.inc
drop procedure innodb_insert_proc;
drop table t1;
The above test case can able to repeat the scenario.
String value("maria") found only in root page of clustered index. (3rd page)
err = btr_root_adjust_on_import(index);
The above function call is omitted then t1.ibd doesn't contain maria. Need to check.
Commit a6ea7b353b5143f5a61fd37c246c49072ba8c421 looks OK to push (with minor changes), but I do not think that it qualifies as a full fix. We should punch holes to the page_compressed files even if the doublewrite buffer is disabled.
Marko Mäkelä
added a comment - Commit a6ea7b353b5143f5a61fd37c246c49072ba8c421 looks OK to push (with minor changes), but I do not think that it qualifies as a full fix. We should punch holes to the page_compressed files even if the doublewrite buffer is disabled.
It only writes zero in remaining uncompressed size of the page with zero.
Thirunarayanan Balathandayuthapani
added a comment - Commit a6ea7b353b5143f5a61fd37c246c49072ba8c421 has been pushed as a workaround of MDEV-15527 .
commit e27535093d1670fde6034c92366e8128b683b903
Author: Thirunarayanan Balathandayuthapani <thiru@mariadb.com>
Date: Mon Mar 26 15:48:27 2018 +0530
- Follow-up fix to MDEV-15229
It only writes zero in remaining uncompressed size of the page with zero.
I posted some comments for the 10.2-based patch. I think that we will also need a 10.5-based version, because the I/O code is quite different there.
Marko Mäkelä
added a comment - I posted some comments for the 10.2-based patch. I think that we will also need a 10.5-based version, because the I/O code is quite different there.
Bug
Major
To repeat this, run:
./mtr --mysqld=--skip-innodb-doublewrite encryption.innodb-discard-importAnd observe the test failure:
CURRENT_TEST: encryption.innodb-discard-import--- /mariadb/10.2/mysql-test/suite/encryption/r/innodb-discard-import.result 2017-05-10 14:14:26.228207275 +0300+++ /mariadb/10.2/mysql-test/suite/encryption/r/innodb-discard-import.reject 2018-03-09 14:32:57.500763121 +0200@@ -129,6 +129,6 @@# t3 yes on expecting NOT FOUNDNOT FOUND /tmpres/ in t3.ibd# t4 yes on expecting NOT FOUND-NOT FOUND /mysql/ in t4.ibd+FOUND 484 /mysql/ in t4.ibdDROP PROCEDURE innodb_insert_proc;DROP TABLE t1,t2,t3,t4;mysqltest: Result content mismatchMDEV-15529will disable this part of the test.