diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2022-02-09 08:36:41 +0200 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2022-02-09 08:36:41 +0200 |
commit | 5c46751f238ee8dcef1e718ac5f63952bff5d09d (patch) | |
tree | 4bd3f72b4c6746324c9e9e1a69731fc2cf735798 | |
parent | f7704d74cb99f3b5cdd106ef76bd9cd8cef6eb8a (diff) | |
download | mariadb-git-5c46751f238ee8dcef1e718ac5f63952bff5d09d.tar.gz |
MDEV-27734 Set innodb_change_buffering=none by default
The aim of the InnoDB change buffer is to avoid delays when a leaf page
of a secondary index is not present in the buffer pool, and a record needs
to be inserted, delete-marked, or purged. Instead of reading the page into
the buffer pool for making such a modification, we may insert a record to
the change buffer (a special index tree in the InnoDB system tablespace).
The buffered changes are guaranteed to be merged if the index page
actually needs to be read later.
The change buffer could be useful when the database is stored on a
rotational medium (hard disk) where random seeks are slower than
sequential reads or writes.
Obviously, the change buffer will cause write amplification, due to
potentially large amount of metadata that is being written to the
change buffer. We will have to write redo log records for modifying
the change buffer tree as well as the user tablespace. Furthermore,
in the user tablespace, we must maintain a change buffer bitmap page
that uses 2 bits for estimating the amount of free space in pages,
and 1 bit to specify whether buffered changes exist. This bitmap needs
to be updated on every operation, which could reduce performance.
Even if the change buffer were free of bugs such as MDEV-24449
(potentially causing the corruption of any page in the system tablespace)
or MDEV-26977 (corruption of secondary indexes due to a currently
unknown reason), it will make diagnosis of other data corruption harder.
Because of all this, it is best to disable the change buffer by default.
7 files changed, 13 insertions, 9 deletions
diff --git a/mysql-test/suite/innodb/r/ibuf_not_empty.result b/mysql-test/suite/innodb/r/ibuf_not_empty.result index d1b8203b063..f2da89990b0 100644 --- a/mysql-test/suite/innodb/r/ibuf_not_empty.result +++ b/mysql-test/suite/innodb/r/ibuf_not_empty.result @@ -5,6 +5,7 @@ c INT, INDEX(b)) ENGINE=InnoDB STATS_PERSISTENT=0; SET GLOBAL innodb_change_buffering_debug = 1; +SET GLOBAL innodb_change_buffering=all; INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_1024; # restart: --innodb-force-recovery=6 --innodb-change-buffer-dump check table t1; diff --git a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result index 678c8c67be5..f676d15b134 100644 --- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result +++ b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result @@ -13,6 +13,7 @@ c INT, INDEX(b)) ENGINE=InnoDB STATS_PERSISTENT=0; SET GLOBAL innodb_change_buffering_debug = 1; +SET GLOBAL innodb_change_buffering = all; INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_8192; BEGIN; SELECT b FROM t1 LIMIT 3; diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.test b/mysql-test/suite/innodb/t/ibuf_not_empty.test index 545a78c887e..96ceb81ac00 100644 --- a/mysql-test/suite/innodb/t/ibuf_not_empty.test +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.test @@ -24,6 +24,7 @@ ENGINE=InnoDB STATS_PERSISTENT=0; # change buffering is possible, so that the change buffer will be used # whenever possible. SET GLOBAL innodb_change_buffering_debug = 1; +SET GLOBAL innodb_change_buffering=all; # Create enough rows for the table, so that the change buffer will be # used for modifying the secondary index page. There must be multiple diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test index a12ca43cec1..129037e783b 100644 --- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test +++ b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test @@ -33,6 +33,7 @@ ENGINE=InnoDB STATS_PERSISTENT=0; # change buffering is possible, so that the change buffer will be used # whenever possible. SET GLOBAL innodb_change_buffering_debug = 1; +SET GLOBAL innodb_change_buffering = all; let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err; # Create enough rows for the table, so that the change buffer will be diff --git a/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result b/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result index 92e22c6aa34..c11f4ee617c 100644 --- a/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result @@ -1,28 +1,28 @@ SET @start_global_value = @@global.innodb_change_buffering; SELECT @start_global_value; @start_global_value -all +none Valid values are 'all', 'deletes', 'changes', 'inserts', 'none', 'purges' select @@global.innodb_change_buffering in ('all', 'deletes', 'changes', 'inserts', 'none', 'purges'); @@global.innodb_change_buffering in ('all', 'deletes', 'changes', 'inserts', 'none', 'purges') 1 select @@global.innodb_change_buffering; @@global.innodb_change_buffering -all +none select @@session.innodb_change_buffering; ERROR HY000: Variable 'innodb_change_buffering' is a GLOBAL variable show global variables like 'innodb_change_buffering'; Variable_name Value -innodb_change_buffering all +innodb_change_buffering none show session variables like 'innodb_change_buffering'; Variable_name Value -innodb_change_buffering all +innodb_change_buffering none select * from information_schema.global_variables where variable_name='innodb_change_buffering'; VARIABLE_NAME VARIABLE_VALUE -INNODB_CHANGE_BUFFERING all +INNODB_CHANGE_BUFFERING none select * from information_schema.session_variables where variable_name='innodb_change_buffering'; VARIABLE_NAME VARIABLE_VALUE -INNODB_CHANGE_BUFFERING all +INNODB_CHANGE_BUFFERING none set global innodb_change_buffering='none'; select @@global.innodb_change_buffering; @@global.innodb_change_buffering @@ -62,4 +62,4 @@ ERROR 42000: Variable 'innodb_change_buffering' can't be set to the value of 'so SET @@global.innodb_change_buffering = @start_global_value; SELECT @@global.innodb_change_buffering; @@global.innodb_change_buffering -all +none diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index ecd107f67c8..c333b56fd2e 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -311,7 +311,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_CHANGE_BUFFERING SESSION_VALUE NULL -DEFAULT_VALUE all +DEFAULT_VALUE none VARIABLE_SCOPE GLOBAL VARIABLE_TYPE ENUM VARIABLE_COMMENT Buffer changes to secondary indexes. diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 646e2619348..2962e6a4a22 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -19623,7 +19623,7 @@ static MYSQL_SYSVAR_BOOL(numa_interleave, srv_numa_interleave, static MYSQL_SYSVAR_ENUM(change_buffering, innodb_change_buffering, PLUGIN_VAR_RQCMDARG, "Buffer changes to secondary indexes.", - NULL, NULL, IBUF_USE_ALL, &innodb_change_buffering_typelib); + NULL, NULL, IBUF_USE_NONE, &innodb_change_buffering_typelib); static MYSQL_SYSVAR_UINT(change_buffer_max_size, srv_change_buffer_max_size, |