MDEV-11937: InnoDB flushes redo log too often

Problem was introduced with the InnoDB 5.7 merge, the code related to
avoiding extra fsync at the end of commit when binlog is enabled. The
MariaDB method for this was removed, but the replacement MySQL method
based on thd_get_durability_property() is not functional in MariaDB.

This commit reverts the offending parts of the merge and adds a test
case, to fix the problem for InnoDB. But other storage engines are
likely to have a similar problem.

2 files changed, 39 insertions, 0 deletions

diff --git a/mysql-test/suite/binlog/r/binlog_innodb.result b/mysql-test/suite/binlog/r/binlog_innodb.result
index 2896706d407..233dda00075 100644
--- a/mysql-test/suite/binlog/r/binlog_innodb.result
+++ b/mysql-test/suite/binlog/r/binlog_innodb.result
@@ -176,4 +176,14 @@ ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
 # There must be no UPDATE query event;
 include/show_binlog_events.inc
 drop table t1, t2;
+*** MDEV-11937: InnoDB flushes redo log too often ***
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
+SET @old_flush = @@GLOBAL.innodb_flush_log_at_trx_commit;
+SET GLOBAL innodb_flush_log_at_trx_commit=1;
+SELECT IF(@num_sync < 100*1.5, "OK",
+CONCAT("ERROR: More than 1 fsync per commit (saw ", @num_sync/100, ")")) AS status;
+status
+OK
+DROP TABLE t1;
+SET GLOBAL innodb_flush_log_at_trx_commit=@old_flush;
 End of tests
diff --git a/mysql-test/suite/binlog/t/binlog_innodb.test b/mysql-test/suite/binlog/t/binlog_innodb.test
index 8191b72d5a9..153dcdd155a 100644
--- a/mysql-test/suite/binlog/t/binlog_innodb.test
+++ b/mysql-test/suite/binlog/t/binlog_innodb.test
@@ -172,4 +172,33 @@ source include/show_binlog_events.inc;
 # cleanup bug#27716
 drop table t1, t2;
 
+--echo *** MDEV-11937: InnoDB flushes redo log too often ***
+
+# Count number of log fsyncs reported by InnoDB per commit.
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
+
+SET @old_flush = @@GLOBAL.innodb_flush_log_at_trx_commit;
+SET GLOBAL innodb_flush_log_at_trx_commit=1;
+
+--let $syncs1 = query_get_value(SHOW STATUS LIKE 'Innodb_os_log_fsyncs', Value, 1)
+--let $ROWS = 100
+--disable_query_log
+let $count = $ROWS;
+while ($count) {
+  eval INSERT INTO t1 VALUES ($count);
+  dec $count;
+}
+--let $syncs2 = query_get_value(SHOW STATUS LIKE 'Innodb_os_log_fsyncs', Value, 1)
+eval SET @num_sync = $syncs2 - $syncs1;
+--enable_query_log
+
+# Allow a bit of slack, in case some background process or something
+# is introducing a few more syncs.
+eval SELECT IF(@num_sync < $ROWS*1.5, "OK",
+    CONCAT("ERROR: More than 1 fsync per commit (saw ", @num_sync/$ROWS, ")")) AS status;
+
+DROP TABLE t1;
+SET GLOBAL innodb_flush_log_at_trx_commit=@old_flush;
+
+
 --echo End of tests