From e6045ecdece104b3408541e2bbf918ed153a7e4e Mon Sep 17 00:00:00 2001 From: Tony Chen Date: Thu, 14 May 2026 16:02:06 +0000 Subject: [PATCH] InnoDB: log a warning when history list length exceeds threshold Add a throttled error-log warning (ER_IB_MSG_HLL_WARNING) from the purge coordinator when InnoDB's history list length exceeds a configurable threshold. Three new sysvars expose tuning and a kill switch: innodb_disable_hll_notification (default OFF) innodb_hll_notification_threshold (default 5,000,000) innodb_hll_notification_interval_minutes (default 180) A high HLL is an early indicator of a stuck purge or long-running transaction; surfacing it in the error log makes the symptom discoverable without requiring active monitoring of information_schema.innodb_metrics. This contribution is under the OCA signed by Amazon and covering submissions to the MySQL project. --- mysql-test/r/all_persisted_variables.result | 8 +- .../innodb/r/innodb_hll_notification.result | 85 ++++++++++ .../r/innodb_hll_notification_throttle.result | 15 ++ .../innodb/t/innodb_hll_notification.test | 160 ++++++++++++++++++ .../t/innodb_hll_notification_throttle.test | 53 ++++++ ...nodb_disable_hll_notification_basic.result | 40 +++++ ...notification_interval_minutes_basic.result | 38 +++++ ...db_hll_notification_threshold_basic.result | 44 +++++ ...innodb_disable_hll_notification_basic.test | 46 +++++ ...l_notification_interval_minutes_basic.test | 54 ++++++ ...nodb_hll_notification_threshold_basic.test | 55 ++++++ mysql-test/t/all_persisted_variables.test | 2 +- share/messages_to_error_log.txt | 3 + storage/innobase/handler/ha_innodb.cc | 23 +++ storage/innobase/include/srv0srv.h | 4 + storage/innobase/srv/srv0srv.cc | 23 +++ 16 files changed, 648 insertions(+), 5 deletions(-) create mode 100644 mysql-test/suite/innodb/r/innodb_hll_notification.result create mode 100644 mysql-test/suite/innodb/r/innodb_hll_notification_throttle.result create mode 100644 mysql-test/suite/innodb/t/innodb_hll_notification.test create mode 100644 mysql-test/suite/innodb/t/innodb_hll_notification_throttle.test create mode 100644 mysql-test/suite/sys_vars/r/innodb_disable_hll_notification_basic.result create mode 100644 mysql-test/suite/sys_vars/r/innodb_hll_notification_interval_minutes_basic.result create mode 100644 mysql-test/suite/sys_vars/r/innodb_hll_notification_threshold_basic.result create mode 100644 mysql-test/suite/sys_vars/t/innodb_disable_hll_notification_basic.test create mode 100644 mysql-test/suite/sys_vars/t/innodb_hll_notification_interval_minutes_basic.test create mode 100644 mysql-test/suite/sys_vars/t/innodb_hll_notification_threshold_basic.test diff --git a/mysql-test/r/all_persisted_variables.result b/mysql-test/r/all_persisted_variables.result index dda96f910df3..20909bb137ff 100644 --- a/mysql-test/r/all_persisted_variables.result +++ b/mysql-test/r/all_persisted_variables.result @@ -46,7 +46,7 @@ include/assert.inc [Expect 500+ variables in the table. Due to open Bugs, we are # Test SET PERSIST -include/assert.inc [Expect 445 persisted variables in the table.] +include/assert.inc [Expect 448 persisted variables in the table.] ************************************************************ * 3. Restart server, it must preserve the persisted variable @@ -54,9 +54,9 @@ include/assert.inc [Expect 445 persisted variables in the table.] ************************************************************ # restart -include/assert.inc [Expect 445 persisted variables in persisted_variables table.] -include/assert.inc [Expect 445 persisted variables shown as PERSISTED in variables_info table.] -include/assert.inc [Expect 445 persisted variables with matching peristed and global values.] +include/assert.inc [Expect 448 persisted variables in persisted_variables table.] +include/assert.inc [Expect 448 persisted variables shown as PERSISTED in variables_info table.] +include/assert.inc [Expect 448 persisted variables with matching peristed and global values.] ************************************************************ * 4. Test RESET PERSIST IF EXISTS. Verify persisted variable diff --git a/mysql-test/suite/innodb/r/innodb_hll_notification.result b/mysql-test/suite/innodb/r/innodb_hll_notification.result new file mode 100644 index 000000000000..4c152d6cbd77 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_hll_notification.result @@ -0,0 +1,85 @@ +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; +@@GLOBAL.innodb_hll_notification_interval_minutes +180 +SET GLOBAL innodb_hll_notification_interval_minutes = 60; +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; +@@GLOBAL.innodb_hll_notification_interval_minutes +60 +SET GLOBAL innodb_hll_notification_interval_minutes = 0; +Warnings: +Warning 1292 Truncated incorrect innodb_hll_notification_interval_minutes value: '0' +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; +@@GLOBAL.innodb_hll_notification_interval_minutes +1 +SET GLOBAL innodb_hll_notification_interval_minutes = 99999; +Warnings: +Warning 1292 Truncated incorrect innodb_hll_notification_interval_minutes value: '99999' +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; +@@GLOBAL.innodb_hll_notification_interval_minutes +14400 +SET GLOBAL innodb_hll_notification_interval_minutes = DEFAULT; +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; +@@GLOBAL.innodb_hll_notification_interval_minutes +180 +SELECT @@GLOBAL.innodb_hll_notification_threshold; +@@GLOBAL.innodb_hll_notification_threshold +5000000 +SET GLOBAL innodb_hll_notification_threshold = 10000000; +SELECT @@GLOBAL.innodb_hll_notification_threshold; +@@GLOBAL.innodb_hll_notification_threshold +10000000 +SET GLOBAL innodb_hll_notification_threshold = 0; +SELECT @@GLOBAL.innodb_hll_notification_threshold; +@@GLOBAL.innodb_hll_notification_threshold +0 +SET GLOBAL innodb_hll_notification_threshold = DEFAULT; +SELECT @@GLOBAL.innodb_hll_notification_threshold; +@@GLOBAL.innodb_hll_notification_threshold +5000000 +SET GLOBAL innodb_disable_hll_notification = ON; +SET GLOBAL innodb_hll_notification_threshold = 1; +SET GLOBAL innodb_hll_notification_interval_minutes = 1; +CREATE TABLE hll_test (id INT AUTO_INCREMENT PRIMARY KEY, val INT) ENGINE=InnoDB; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +INSERT INTO hll_test (val) VALUES (1); +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +include/assert_grep.inc [No HLL warning when disable flag is ON] +COMMIT; +DROP TABLE hll_test; +SET GLOBAL innodb_disable_hll_notification = DEFAULT; +SET GLOBAL innodb_hll_notification_threshold = 100000000; +SET GLOBAL innodb_hll_notification_interval_minutes = 1; +CREATE TABLE hll_test (id INT AUTO_INCREMENT PRIMARY KEY, val INT) ENGINE=InnoDB; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +INSERT INTO hll_test (val) VALUES (1); +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +include/assert_grep.inc [No HLL warning when threshold is very high] +COMMIT; +DROP TABLE hll_test; +SET GLOBAL innodb_hll_notification_threshold = 1; +SET GLOBAL innodb_hll_notification_interval_minutes = 1; +SHOW GLOBAL VARIABLES LIKE 'innodb_hll_notification%'; +Variable_name Value +innodb_hll_notification_interval_minutes 1 +innodb_hll_notification_threshold 1 +SHOW GLOBAL VARIABLES LIKE 'innodb_disable_hll_notification'; +Variable_name Value +innodb_disable_hll_notification OFF +CREATE TABLE hll_test (id INT AUTO_INCREMENT PRIMARY KEY, val INT) ENGINE=InnoDB; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +INSERT INTO hll_test (val) VALUES (1); +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +include/assert_grep.inc [HLL warning should be present in error log] +COMMIT; +DROP TABLE hll_test; +SET GLOBAL innodb_hll_notification_threshold = DEFAULT; +SET GLOBAL innodb_hll_notification_interval_minutes = DEFAULT; diff --git a/mysql-test/suite/innodb/r/innodb_hll_notification_throttle.result b/mysql-test/suite/innodb/r/innodb_hll_notification_throttle.result new file mode 100644 index 000000000000..0283fd12f811 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_hll_notification_throttle.result @@ -0,0 +1,15 @@ +SET GLOBAL innodb_hll_notification_interval_minutes = 2; +SET GLOBAL innodb_hll_notification_threshold = 1; +CREATE TABLE hll_test (id INT AUTO_INCREMENT PRIMARY KEY, val INT) ENGINE=InnoDB; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +INSERT INTO hll_test (val) VALUES (1); +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +include/assert_grep.inc [Exactly 1 HLL warning after first interval] +include/assert_grep.inc [Still exactly 1 HLL warning (throttled within interval)] +COMMIT; +DROP TABLE hll_test; +SET GLOBAL innodb_hll_notification_threshold = DEFAULT; +SET GLOBAL innodb_hll_notification_interval_minutes = DEFAULT; diff --git a/mysql-test/suite/innodb/t/innodb_hll_notification.test b/mysql-test/suite/innodb/t/innodb_hll_notification.test new file mode 100644 index 000000000000..aecfdd916d79 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_hll_notification.test @@ -0,0 +1,160 @@ +# Test innodb_hll_notification_* knobs, error-log warning emission, and the +# kill switch innodb_disable_hll_notification. + +--source include/not_parallel.inc + +--disable_query_log +call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* The InnoDB history list length \\(HLL\\) is very high"); +--enable_query_log + +# ============================================================ +# Part 1: Basic knob validation (interval_minutes) +# ============================================================ + +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; + +SET GLOBAL innodb_hll_notification_interval_minutes = 60; +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; + +# Boundary clamping (min = 1, max = 14400) +SET GLOBAL innodb_hll_notification_interval_minutes = 0; +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; +SET GLOBAL innodb_hll_notification_interval_minutes = 99999; +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; + +SET GLOBAL innodb_hll_notification_interval_minutes = DEFAULT; +SELECT @@GLOBAL.innodb_hll_notification_interval_minutes; + +# ============================================================ +# Part 2: innodb_hll_notification_threshold boundary behavior +# ============================================================ + +SELECT @@GLOBAL.innodb_hll_notification_threshold; + +SET GLOBAL innodb_hll_notification_threshold = 10000000; +SELECT @@GLOBAL.innodb_hll_notification_threshold; + +# Lower bound (0 is allowed) +SET GLOBAL innodb_hll_notification_threshold = 0; +SELECT @@GLOBAL.innodb_hll_notification_threshold; + +SET GLOBAL innodb_hll_notification_threshold = DEFAULT; +SELECT @@GLOBAL.innodb_hll_notification_threshold; + +# ============================================================ +# Part 3: kill switch — innodb_disable_hll_notification = ON +# ============================================================ + +SET GLOBAL innodb_disable_hll_notification = ON; +SET GLOBAL innodb_hll_notification_threshold = 1; +SET GLOBAL innodb_hll_notification_interval_minutes = 1; + +CREATE TABLE hll_test (id INT AUTO_INCREMENT PRIMARY KEY, val INT) ENGINE=InnoDB; + +connect (long_trx, localhost, root,,); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +INSERT INTO hll_test (val) VALUES (1); +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; + +# Wait long enough for the purge coordinator to observe HLL and (under normal +# conditions) emit the warning. Kill switch should suppress it. +--sleep 70 + +--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_text= No HLL warning when disable flag is ON +--let $assert_select= The InnoDB history list length \(HLL\) is very high +--let $assert_count= 0 +--let $assert_only_after= CURRENT_TEST: innodb.innodb_hll_notification +--source include/assert_grep.inc + +connection long_trx; +COMMIT; +disconnect long_trx; + +connection default; +DROP TABLE hll_test; +SET GLOBAL innodb_disable_hll_notification = DEFAULT; + +# ============================================================ +# Part 4: very high threshold — no warning emitted +# Drive the HLL up but keep it well below the threshold. +# ============================================================ + +SET GLOBAL innodb_hll_notification_threshold = 100000000; +SET GLOBAL innodb_hll_notification_interval_minutes = 1; + +CREATE TABLE hll_test (id INT AUTO_INCREMENT PRIMARY KEY, val INT) ENGINE=InnoDB; + +connect (long_trx_hi, localhost, root,,); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +INSERT INTO hll_test (val) VALUES (1); +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; + +--sleep 70 + +--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_text= No HLL warning when threshold is very high +--let $assert_select= The InnoDB history list length \(HLL\) is very high +--let $assert_count= 0 +--let $assert_only_after= CURRENT_TEST: innodb.innodb_hll_notification +--source include/assert_grep.inc + +connection long_trx_hi; +COMMIT; +disconnect long_trx_hi; + +connection default; +DROP TABLE hll_test; + +# ============================================================ +# Part 5: warning is emitted when threshold is breached +# ============================================================ + +SET GLOBAL innodb_hll_notification_threshold = 1; +SET GLOBAL innodb_hll_notification_interval_minutes = 1; +SHOW GLOBAL VARIABLES LIKE 'innodb_hll_notification%'; +SHOW GLOBAL VARIABLES LIKE 'innodb_disable_hll_notification'; + +CREATE TABLE hll_test (id INT AUTO_INCREMENT PRIMARY KEY, val INT) ENGINE=InnoDB; + +connect (long_trx2, localhost, root,,); +# CONSISTENT SNAPSHOT pins a read view that blocks purge of the undo records +# generated below — drives the HLL up. +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +INSERT INTO hll_test (val) VALUES (1); +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; + +--sleep 70 + +--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_text= HLL warning should be present in error log +--let $assert_select= The InnoDB history list length \(HLL\) is very high +--let $assert_count= +--let $assert_count_condition= >= 1 +--let $assert_only_after= CURRENT_TEST: innodb.innodb_hll_notification +--source include/assert_grep.inc + +connection long_trx2; +COMMIT; +disconnect long_trx2; + +connection default; +DROP TABLE hll_test; + +SET GLOBAL innodb_hll_notification_threshold = DEFAULT; +SET GLOBAL innodb_hll_notification_interval_minutes = DEFAULT; diff --git a/mysql-test/suite/innodb/t/innodb_hll_notification_throttle.test b/mysql-test/suite/innodb/t/innodb_hll_notification_throttle.test new file mode 100644 index 000000000000..599d6d2390aa --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_hll_notification_throttle.test @@ -0,0 +1,53 @@ +# Verify the HLL warning is throttled: at most one warning per +# innodb_hll_notification_interval_minutes window. + +--source include/not_parallel.inc + +--disable_query_log +call mtr.add_suppression("\\[Warning\\] .*MY-\\d+.* The InnoDB history list length \\(HLL\\) is very high"); +--enable_query_log + +SET GLOBAL innodb_hll_notification_interval_minutes = 2; +SET GLOBAL innodb_hll_notification_threshold = 1; + +CREATE TABLE hll_test (id INT AUTO_INCREMENT PRIMARY KEY, val INT) ENGINE=InnoDB; + +connect (long_trx, localhost, root,,); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +INSERT INTO hll_test (val) VALUES (1); +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; +INSERT INTO hll_test (val) SELECT val FROM hll_test; + +# Wait long enough for the first warning to fire (must exceed interval_minutes +# from the first time the threshold is observed exceeded). +--sleep 130 + +--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_text= Exactly 1 HLL warning after first interval +--let $assert_select= The InnoDB history list length \(HLL\) is very high +--let $assert_count= 1 +--let $assert_only_after= CURRENT_TEST: innodb.innodb_hll_notification_throttle +--source include/assert_grep.inc + +# Stay inside the same 2-minute window — count must not increase. +--sleep 30 + +--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.1.err +--let $assert_text= Still exactly 1 HLL warning (throttled within interval) +--let $assert_select= The InnoDB history list length \(HLL\) is very high +--let $assert_count= 1 +--let $assert_only_after= CURRENT_TEST: innodb.innodb_hll_notification_throttle +--source include/assert_grep.inc + +connection long_trx; +COMMIT; +disconnect long_trx; + +connection default; +DROP TABLE hll_test; +SET GLOBAL innodb_hll_notification_threshold = DEFAULT; +SET GLOBAL innodb_hll_notification_interval_minutes = DEFAULT; diff --git a/mysql-test/suite/sys_vars/r/innodb_disable_hll_notification_basic.result b/mysql-test/suite/sys_vars/r/innodb_disable_hll_notification_basic.result new file mode 100644 index 000000000000..1b79f27cdd0b --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_disable_hll_notification_basic.result @@ -0,0 +1,40 @@ +SET @start_value = @@global.innodb_disable_hll_notification; +SELECT @start_value; +@start_value +0 +SET @@global.innodb_disable_hll_notification = DEFAULT; +SELECT @@global.innodb_disable_hll_notification; +@@global.innodb_disable_hll_notification +0 +SET innodb_disable_hll_notification = ON; +ERROR HY000: Variable 'innodb_disable_hll_notification' is a GLOBAL variable and should be set with SET GLOBAL +SET @@global.innodb_disable_hll_notification = ON; +SELECT @@global.innodb_disable_hll_notification; +@@global.innodb_disable_hll_notification +1 +SET @@global.innodb_disable_hll_notification = OFF; +SELECT @@global.innodb_disable_hll_notification; +@@global.innodb_disable_hll_notification +0 +SET @@global.innodb_disable_hll_notification = 1; +SELECT @@global.innodb_disable_hll_notification; +@@global.innodb_disable_hll_notification +1 +SET @@global.innodb_disable_hll_notification = 0; +SELECT @@global.innodb_disable_hll_notification; +@@global.innodb_disable_hll_notification +0 +SET @@global.innodb_disable_hll_notification = 'foo'; +ERROR 42000: Variable 'innodb_disable_hll_notification' can't be set to the value of 'foo' +SET @@global.innodb_disable_hll_notification = 2; +ERROR 42000: Variable 'innodb_disable_hll_notification' can't be set to the value of '2' +SET @@global.innodb_disable_hll_notification = 1.1; +ERROR 42000: Incorrect argument type to variable 'innodb_disable_hll_notification' +SELECT @@global.innodb_disable_hll_notification = VARIABLE_VALUE +FROM performance_schema.global_variables +WHERE VARIABLE_NAME = 'innodb_disable_hll_notification'; +@@global.innodb_disable_hll_notification = VARIABLE_VALUE +1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'OFF' +SET @@global.innodb_disable_hll_notification = @start_value; diff --git a/mysql-test/suite/sys_vars/r/innodb_hll_notification_interval_minutes_basic.result b/mysql-test/suite/sys_vars/r/innodb_hll_notification_interval_minutes_basic.result new file mode 100644 index 000000000000..1b410bd7a85f --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_hll_notification_interval_minutes_basic.result @@ -0,0 +1,38 @@ +SET @start_value = @@global.innodb_hll_notification_interval_minutes; +SELECT @start_value; +@start_value +180 +SET @@global.innodb_hll_notification_interval_minutes = DEFAULT; +SELECT @@global.innodb_hll_notification_interval_minutes; +@@global.innodb_hll_notification_interval_minutes +180 +SET innodb_hll_notification_interval_minutes = 1; +ERROR HY000: Variable 'innodb_hll_notification_interval_minutes' is a GLOBAL variable and should be set with SET GLOBAL +SET @@global.innodb_hll_notification_interval_minutes = 1; +SELECT @@global.innodb_hll_notification_interval_minutes; +@@global.innodb_hll_notification_interval_minutes +1 +SET @@global.innodb_hll_notification_interval_minutes = 14400; +SELECT @@global.innodb_hll_notification_interval_minutes; +@@global.innodb_hll_notification_interval_minutes +14400 +SET @@global.innodb_hll_notification_interval_minutes = 0; +SELECT @@global.innodb_hll_notification_interval_minutes; +@@global.innodb_hll_notification_interval_minutes +1 +SET @@global.innodb_hll_notification_interval_minutes = 14401; +SELECT @@global.innodb_hll_notification_interval_minutes; +@@global.innodb_hll_notification_interval_minutes +14400 +SET @@global.innodb_hll_notification_interval_minutes = 'T'; +ERROR 42000: Incorrect argument type to variable 'innodb_hll_notification_interval_minutes' +SET @@global.innodb_hll_notification_interval_minutes = 1.1; +ERROR 42000: Incorrect argument type to variable 'innodb_hll_notification_interval_minutes' +SET @@global.innodb_hll_notification_interval_minutes = ON; +ERROR 42000: Incorrect argument type to variable 'innodb_hll_notification_interval_minutes' +SELECT @@global.innodb_hll_notification_interval_minutes = VARIABLE_VALUE +FROM performance_schema.global_variables +WHERE VARIABLE_NAME = 'innodb_hll_notification_interval_minutes'; +@@global.innodb_hll_notification_interval_minutes = VARIABLE_VALUE +1 +SET @@global.innodb_hll_notification_interval_minutes = @start_value; diff --git a/mysql-test/suite/sys_vars/r/innodb_hll_notification_threshold_basic.result b/mysql-test/suite/sys_vars/r/innodb_hll_notification_threshold_basic.result new file mode 100644 index 000000000000..0c3667636cc9 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/innodb_hll_notification_threshold_basic.result @@ -0,0 +1,44 @@ +SET @start_value = @@global.innodb_hll_notification_threshold; +SELECT @start_value; +@start_value +5000000 +SET @@global.innodb_hll_notification_threshold = DEFAULT; +SELECT @@global.innodb_hll_notification_threshold; +@@global.innodb_hll_notification_threshold +5000000 +SET innodb_hll_notification_threshold = 1; +ERROR HY000: Variable 'innodb_hll_notification_threshold' is a GLOBAL variable and should be set with SET GLOBAL +SET @@global.innodb_hll_notification_threshold = 0; +SELECT @@global.innodb_hll_notification_threshold; +@@global.innodb_hll_notification_threshold +0 +SET @@global.innodb_hll_notification_threshold = 1; +SELECT @@global.innodb_hll_notification_threshold; +@@global.innodb_hll_notification_threshold +1 +SET @@global.innodb_hll_notification_threshold = 4294967295; +SELECT @@global.innodb_hll_notification_threshold; +@@global.innodb_hll_notification_threshold +4294967295 +SET @@global.innodb_hll_notification_threshold = 4294967296; +SELECT @@global.innodb_hll_notification_threshold IN (4294967296, 4294967295); +@@global.innodb_hll_notification_threshold IN (4294967296, 4294967295) +1 +SET @@global.innodb_hll_notification_threshold = -1; +Warnings: +Warning 1292 Truncated incorrect innodb_hll_notification_threshold value: '-1' +SELECT @@global.innodb_hll_notification_threshold; +@@global.innodb_hll_notification_threshold +0 +SET @@global.innodb_hll_notification_threshold = 'T'; +ERROR 42000: Incorrect argument type to variable 'innodb_hll_notification_threshold' +SET @@global.innodb_hll_notification_threshold = 1.1; +ERROR 42000: Incorrect argument type to variable 'innodb_hll_notification_threshold' +SET @@global.innodb_hll_notification_threshold = ON; +ERROR 42000: Incorrect argument type to variable 'innodb_hll_notification_threshold' +SELECT @@global.innodb_hll_notification_threshold = VARIABLE_VALUE +FROM performance_schema.global_variables +WHERE VARIABLE_NAME = 'innodb_hll_notification_threshold'; +@@global.innodb_hll_notification_threshold = VARIABLE_VALUE +1 +SET @@global.innodb_hll_notification_threshold = @start_value; diff --git a/mysql-test/suite/sys_vars/t/innodb_disable_hll_notification_basic.test b/mysql-test/suite/sys_vars/t/innodb_disable_hll_notification_basic.test new file mode 100644 index 000000000000..fc3658cd5396 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_disable_hll_notification_basic.test @@ -0,0 +1,46 @@ +################################################################################ +# Variable Name: innodb_disable_hll_notification +# Scope: GLOBAL +# Access Type: Dynamic +# Data Type: Boolean +# Default Value: OFF +################################################################################ + +--source include/load_sysvars.inc + +SET @start_value = @@global.innodb_disable_hll_notification; +SELECT @start_value; + +# Default +SET @@global.innodb_disable_hll_notification = DEFAULT; +SELECT @@global.innodb_disable_hll_notification; + +# Session scope is rejected +--ERROR ER_GLOBAL_VARIABLE +SET innodb_disable_hll_notification = ON; + +# Valid values +SET @@global.innodb_disable_hll_notification = ON; +SELECT @@global.innodb_disable_hll_notification; +SET @@global.innodb_disable_hll_notification = OFF; +SELECT @@global.innodb_disable_hll_notification; +SET @@global.innodb_disable_hll_notification = 1; +SELECT @@global.innodb_disable_hll_notification; +SET @@global.innodb_disable_hll_notification = 0; +SELECT @@global.innodb_disable_hll_notification; + +# Invalid types +--ERROR ER_WRONG_VALUE_FOR_VAR +SET @@global.innodb_disable_hll_notification = 'foo'; +--ERROR ER_WRONG_VALUE_FOR_VAR +SET @@global.innodb_disable_hll_notification = 2; +--ERROR ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_disable_hll_notification = 1.1; + +# Match against performance_schema +SELECT @@global.innodb_disable_hll_notification = VARIABLE_VALUE + FROM performance_schema.global_variables + WHERE VARIABLE_NAME = 'innodb_disable_hll_notification'; + +# Restore +SET @@global.innodb_disable_hll_notification = @start_value; diff --git a/mysql-test/suite/sys_vars/t/innodb_hll_notification_interval_minutes_basic.test b/mysql-test/suite/sys_vars/t/innodb_hll_notification_interval_minutes_basic.test new file mode 100644 index 000000000000..be71ff85bd83 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_hll_notification_interval_minutes_basic.test @@ -0,0 +1,54 @@ +################################################################################ +# Variable Name: innodb_hll_notification_interval_minutes +# Scope: GLOBAL +# Access Type: Dynamic +# Data Type: Numeric (ulong) +# Default Value: 180 +# Range: 1 .. 14400 +################################################################################ + +--source include/load_sysvars.inc + +SET @start_value = @@global.innodb_hll_notification_interval_minutes; +SELECT @start_value; + +# Default +SET @@global.innodb_hll_notification_interval_minutes = DEFAULT; +SELECT @@global.innodb_hll_notification_interval_minutes; + +# Session scope is rejected +--ERROR ER_GLOBAL_VARIABLE +SET innodb_hll_notification_interval_minutes = 1; + +# Valid values +SET @@global.innodb_hll_notification_interval_minutes = 1; +SELECT @@global.innodb_hll_notification_interval_minutes; +SET @@global.innodb_hll_notification_interval_minutes = 14400; +SELECT @@global.innodb_hll_notification_interval_minutes; + +# Out of range — clamped +--disable_warnings +SET @@global.innodb_hll_notification_interval_minutes = 0; +--enable_warnings +SELECT @@global.innodb_hll_notification_interval_minutes; + +--disable_warnings +SET @@global.innodb_hll_notification_interval_minutes = 14401; +--enable_warnings +SELECT @@global.innodb_hll_notification_interval_minutes; + +# Invalid types +--ERROR ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_hll_notification_interval_minutes = 'T'; +--ERROR ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_hll_notification_interval_minutes = 1.1; +--ERROR ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_hll_notification_interval_minutes = ON; + +# Match against performance_schema +SELECT @@global.innodb_hll_notification_interval_minutes = VARIABLE_VALUE + FROM performance_schema.global_variables + WHERE VARIABLE_NAME = 'innodb_hll_notification_interval_minutes'; + +# Restore +SET @@global.innodb_hll_notification_interval_minutes = @start_value; diff --git a/mysql-test/suite/sys_vars/t/innodb_hll_notification_threshold_basic.test b/mysql-test/suite/sys_vars/t/innodb_hll_notification_threshold_basic.test new file mode 100644 index 000000000000..12fadeb4355e --- /dev/null +++ b/mysql-test/suite/sys_vars/t/innodb_hll_notification_threshold_basic.test @@ -0,0 +1,55 @@ +################################################################################ +# Variable Name: innodb_hll_notification_threshold +# Scope: GLOBAL +# Access Type: Dynamic +# Data Type: Numeric (ulong) +# Default Value: 5000000 +# Range: 0 .. UINT32_MAX +################################################################################ + +--source include/load_sysvars.inc + +SET @start_value = @@global.innodb_hll_notification_threshold; +SELECT @start_value; + +# Default +SET @@global.innodb_hll_notification_threshold = DEFAULT; +SELECT @@global.innodb_hll_notification_threshold; + +# Session scope is rejected +--ERROR ER_GLOBAL_VARIABLE +SET innodb_hll_notification_threshold = 1; + +# Valid values +SET @@global.innodb_hll_notification_threshold = 0; +SELECT @@global.innodb_hll_notification_threshold; +SET @@global.innodb_hll_notification_threshold = 1; +SELECT @@global.innodb_hll_notification_threshold; +SET @@global.innodb_hll_notification_threshold = 4294967295; +SELECT @@global.innodb_hll_notification_threshold; + +# Out of range — clamped to UINT32_MAX +--disable_warnings +SET @@global.innodb_hll_notification_threshold = 4294967296; +--enable_warnings +SELECT @@global.innodb_hll_notification_threshold IN (4294967296, 4294967295); + +# Negative value — clamped to 0 +SET @@global.innodb_hll_notification_threshold = -1; +SELECT @@global.innodb_hll_notification_threshold; + +# Invalid types +--ERROR ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_hll_notification_threshold = 'T'; +--ERROR ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_hll_notification_threshold = 1.1; +--ERROR ER_WRONG_TYPE_FOR_VAR +SET @@global.innodb_hll_notification_threshold = ON; + +# Match against performance_schema +SELECT @@global.innodb_hll_notification_threshold = VARIABLE_VALUE + FROM performance_schema.global_variables + WHERE VARIABLE_NAME = 'innodb_hll_notification_threshold'; + +# Restore +SET @@global.innodb_hll_notification_threshold = @start_value; diff --git a/mysql-test/t/all_persisted_variables.test b/mysql-test/t/all_persisted_variables.test index 4e222f45c6c2..0c6c74a09811 100644 --- a/mysql-test/t/all_persisted_variables.test +++ b/mysql-test/t/all_persisted_variables.test @@ -56,7 +56,7 @@ let $total_global_vars=`SELECT COUNT(*) AND variable_name NOT LIKE 'debug_%' AND variable_name NOT LIKE '%telemetry%'`; -let $total_persistent_vars=445; +let $total_persistent_vars=448; --echo *************************************************************** --echo * 0. Verify that variables present in performance_schema.global diff --git a/share/messages_to_error_log.txt b/share/messages_to_error_log.txt index f41f26c312db..778dfd2a79a2 100644 --- a/share/messages_to_error_log.txt +++ b/share/messages_to_error_log.txt @@ -13356,6 +13356,9 @@ ER_AUDIT_LOG_DATABASE_NAME_TOO_LONG ER_AUDIT_LOG_DATABASE_NAME_EMPTY eng "Database name cannot be empty." +ER_IB_MSG_HLL_WARNING + eng "The InnoDB history list length (HLL) is very high: %lu. Take actions to reduce the HLL, such as ending long-running transactions or tuning the purge parameters." + ################################################################################ # Error numbers 50000 to 51999 are reserved. Please do not use them for # other error messages. diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index ee4304598f8e..ac4a9c9347d6 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -22463,6 +22463,26 @@ static MYSQL_SYSVAR_ULONG(max_purge_lag_delay, srv_max_purge_lag_delay, 0L, /* Minimum value */ 10000000UL, 0); /* Maximum value */ +static MYSQL_SYSVAR_BOOL(disable_hll_notification, srv_disable_hll_notification, + PLUGIN_VAR_OPCMDARG, + "Disable the high history-list-length warning written" + " to the error log.", + nullptr, nullptr, false); + +static MYSQL_SYSVAR_ULONG( + hll_notification_threshold, srv_hll_notification_threshold, + PLUGIN_VAR_RQCMDARG, + "InnoDB history list length above which a warning is written to the error" + " log.", + nullptr, nullptr, 5000000UL, 0, UINT32_MAX, 0); + +static MYSQL_SYSVAR_ULONG( + hll_notification_interval_minutes, + srv_hll_notification_interval_minutes, PLUGIN_VAR_RQCMDARG, + "Minimum number of minutes between successive history-list-length warnings" + " in the error log.", + nullptr, nullptr, 180UL, 1UL, 14400UL, 0); + static MYSQL_SYSVAR_BOOL(rollback_on_timeout, innobase_rollback_on_timeout, PLUGIN_VAR_OPCMDARG | PLUGIN_VAR_READONLY, "Roll back the complete transaction on lock wait " @@ -23659,6 +23679,9 @@ static SYS_VAR *innobase_system_variables[] = { MYSQL_SYSVAR(flushing_avg_loops), MYSQL_SYSVAR(max_purge_lag), MYSQL_SYSVAR(max_purge_lag_delay), + MYSQL_SYSVAR(disable_hll_notification), + MYSQL_SYSVAR(hll_notification_threshold), + MYSQL_SYSVAR(hll_notification_interval_minutes), MYSQL_SYSVAR(old_blocks_pct), MYSQL_SYSVAR(old_blocks_time), MYSQL_SYSVAR(open_files), diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h index 602d52e48b88..8716f6517069 100644 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -711,6 +711,10 @@ extern ulong srv_n_free_tickets_to_enter; extern ulong srv_spin_wait_delay; extern bool srv_priority_boost; +extern bool srv_disable_hll_notification; +extern ulong srv_hll_notification_threshold; +extern ulong srv_hll_notification_interval_minutes; + extern ulint srv_truncated_status_writes; #if defined UNIV_DEBUG || defined UNIV_IBUF_DEBUG diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc index d215fc107c03..b789a8936a3c 100644 --- a/storage/innobase/srv/srv0srv.cc +++ b/storage/innobase/srv/srv0srv.cc @@ -578,6 +578,10 @@ ulong srv_n_spin_wait_rounds = 30; ulong srv_spin_wait_delay = 6; bool srv_priority_boost = true; +bool srv_disable_hll_notification = false; +ulong srv_hll_notification_threshold = 5000000; +ulong srv_hll_notification_interval_minutes = 180; + #ifndef UNIV_HOTBACKUP static ulint srv_n_rows_inserted_old = 0; static ulint srv_n_rows_updated_old = 0; @@ -2839,6 +2843,24 @@ void srv_worker_thread() { destroy_internal_thd(thd); } +/** Log a warning to the MySQL error log when the InnoDB history list length +exceeds srv_hll_notification_threshold. The warning is throttled: at most one +message per srv_hll_notification_interval_minutes window. */ +static void check_hll_and_log() { + if (srv_disable_hll_notification) return; + + const auto hll = trx_sys->rseg_history_len.load(); + if (hll <= srv_hll_notification_threshold) return; + + static auto last_warning_time = std::chrono::steady_clock::now(); + const auto now = std::chrono::steady_clock::now(); + if (now - last_warning_time > + std::chrono::minutes{srv_hll_notification_interval_minutes}) { + ib::warn(ER_IB_MSG_HLL_WARNING, (ulong)hll); + last_warning_time = now; + } +} + /** Do the actual purge operation. @param[in,out] n_total_purged Total pages purged in this call @return length of history list before the last purge batch. */ @@ -2912,6 +2934,7 @@ static ulint srv_do_purge(ulint *n_total_purged) { (undo::spaces->find_first_inactive_explicit(nullptr) != nullptr); undo::spaces->s_unlock(); } + check_hll_and_log(); } while (purge_sys->state == PURGE_STATE_RUN && (n_pages_purged > 0 || need_explicit_truncate) && !srv_purge_should_exit(n_pages_purged));