From 32174f671f004428cded4ed8bfaa28f43d793656 Mon Sep 17 00:00:00 2001 From: Fariha Shaikh Date: Fri, 6 Mar 2026 18:31:54 +0000 Subject: [PATCH] Bug #119990 Proactive handling of InnoDB tablespace full condition InnoDB write failures occur when tablespace files exceed filesystem size limits. Current behavior logs errors but continues accepting transactions, causing repeated failures and potential data integrity issues. Add proactive monitoring by emitting warnings when InnoDB tablespaces approach a configurable size threshold. Warnings use a tiered frequency approach to balance early notification with log spam prevention. Key features: - Three new system variables: * innodb_tablespace_size_warning_threshold (default 16TB): Maximum tablespace size in bytes before warnings begin * innodb_tablespace_size_warning_pct (default 70%): Percentage of threshold at which to start emitting warnings * innodb_tablespace_size_warning_enabled (default ON): Andon cord to enable/disable the warning feature - Tiered warning frequency: * Below warning_pct: No warnings * Between warning_pct and 90%: At most twice per 10% interval with minimum 5% gap (e.g., 70%, 77%, 81%, 89%) * Above 90%: Every 1% increase (90%, 91%, 92%, etc.) - Per-tablespace tracking with automatic reset on TRUNCATE/DROP or threshold changes - Zero overhead when disabled - Progressive warnings capped at 100% Implementation hooks into fsp_try_extend_data_file() for O(1) size checking during tablespace extension. Adds 11 bytes per tablespace (m_last_size_warning_pct, m_last_warning_threshold, m_warning_count_in_decade) to fil_space_t structure. This contribution is under the OCA signed by Amazon and covering submissions to the MySQL project. --- .../innodb/r/tablespace_size_warning.result | 64 ++++++++ .../innodb/t/tablespace_size_warning.test | 146 ++++++++++++++++++ storage/innobase/fsp/fsp0fsp.cc | 86 +++++++++++ storage/innobase/handler/ha_innodb.cc | 25 +++ storage/innobase/include/fil0fil.h | 9 ++ storage/innobase/include/srv0srv.h | 9 ++ storage/innobase/srv/srv0srv.cc | 9 ++ 7 files changed, 348 insertions(+) create mode 100644 mysql-test/suite/innodb/r/tablespace_size_warning.result create mode 100644 mysql-test/suite/innodb/t/tablespace_size_warning.test diff --git a/mysql-test/suite/innodb/r/tablespace_size_warning.result b/mysql-test/suite/innodb/r/tablespace_size_warning.result new file mode 100644 index 000000000000..3794caf5ab07 --- /dev/null +++ b/mysql-test/suite/innodb/r/tablespace_size_warning.result @@ -0,0 +1,64 @@ +# +# Bug #119990 Proactive handling of InnoDB tablespace full condition +# +SET @old_threshold = @@global.innodb_tablespace_size_warning_threshold; +SET @old_enabled = @@global.innodb_tablespace_size_warning_enabled; +SET @old_pct = @@global.innodb_tablespace_size_warning_pct; +# Test system variables +SHOW VARIABLES LIKE 'innodb_tablespace_size_warning_threshold'; +Variable_name Value +innodb_tablespace_size_warning_threshold 17592186044416 +SHOW VARIABLES LIKE 'innodb_tablespace_size_warning_enabled'; +Variable_name Value +innodb_tablespace_size_warning_enabled ON +SHOW VARIABLES LIKE 'innodb_tablespace_size_warning_pct'; +Variable_name Value +innodb_tablespace_size_warning_pct 70 +# Test basic warning emission +SET GLOBAL innodb_tablespace_size_warning_threshold = 10485760; +SET GLOBAL innodb_tablespace_size_warning_pct = 70; +CREATE TABLE t1 ( +id INT AUTO_INCREMENT PRIMARY KEY, +data LONGBLOB +) ENGINE=InnoDB; +Pattern "Tablespace 'test/t1' size .* bytes .* exceeds warning threshold" found +DROP TABLE t1; +# Test configurable warning percentage +SET GLOBAL innodb_tablespace_size_warning_threshold = 10485760; +SET GLOBAL innodb_tablespace_size_warning_pct = 80; +CREATE TABLE t1b ( +id INT AUTO_INCREMENT PRIMARY KEY, +data LONGBLOB +) ENGINE=InnoDB; +Pattern "Tablespace 'test/t1b' size .* bytes .* exceeds warning threshold" found +DROP TABLE t1b; +# Test threshold set to zero disables warnings +SET GLOBAL innodb_tablespace_size_warning_threshold = 0; +CREATE TABLE t2 ( +id INT AUTO_INCREMENT PRIMARY KEY, +data LONGBLOB +) ENGINE=InnoDB; +DROP TABLE t2; +# Test andon cord disable/enable +SET GLOBAL innodb_tablespace_size_warning_threshold = 10485760; +SET GLOBAL innodb_tablespace_size_warning_pct = 70; +SET GLOBAL innodb_tablespace_size_warning_enabled = FALSE; +CREATE TABLE t3 ( +id INT AUTO_INCREMENT PRIMARY KEY, +data LONGBLOB +) ENGINE=InnoDB; +SET GLOBAL innodb_tablespace_size_warning_enabled = TRUE; +Pattern "Tablespace 'test/t3' size .* bytes .* exceeds warning threshold" found +DROP TABLE t3; +# Test TRUNCATE TABLE resets warning state +CREATE TABLE t4 ( +id INT AUTO_INCREMENT PRIMARY KEY, +data LONGBLOB +) ENGINE=InnoDB; +Pattern "Tablespace 'test/t4' size .* bytes .* exceeds warning threshold" found +TRUNCATE TABLE t4; +Pattern "Tablespace 'test/t4' size .* bytes .* exceeds warning threshold" found +DROP TABLE t4; +SET GLOBAL innodb_tablespace_size_warning_threshold = @old_threshold; +SET GLOBAL innodb_tablespace_size_warning_enabled = @old_enabled; +SET GLOBAL innodb_tablespace_size_warning_pct = @old_pct; diff --git a/mysql-test/suite/innodb/t/tablespace_size_warning.test b/mysql-test/suite/innodb/t/tablespace_size_warning.test new file mode 100644 index 000000000000..d6856e93025a --- /dev/null +++ b/mysql-test/suite/innodb/t/tablespace_size_warning.test @@ -0,0 +1,146 @@ +--disable_query_log +call mtr.add_suppression("Tablespace .* size .* bytes .* exceeds warning threshold"); +--enable_query_log + +--echo # +--echo # Bug #119990 Proactive handling of InnoDB tablespace full condition +--echo # + +# Save original values +SET @old_threshold = @@global.innodb_tablespace_size_warning_threshold; +SET @old_enabled = @@global.innodb_tablespace_size_warning_enabled; +SET @old_pct = @@global.innodb_tablespace_size_warning_pct; + +--echo # Test system variables +SHOW VARIABLES LIKE 'innodb_tablespace_size_warning_threshold'; +SHOW VARIABLES LIKE 'innodb_tablespace_size_warning_enabled'; +SHOW VARIABLES LIKE 'innodb_tablespace_size_warning_pct'; + +--echo # Test basic warning emission +SET GLOBAL innodb_tablespace_size_warning_threshold = 10485760; +SET GLOBAL innodb_tablespace_size_warning_pct = 70; + +CREATE TABLE t1 ( + id INT AUTO_INCREMENT PRIMARY KEY, + data LONGBLOB +) ENGINE=InnoDB; + +--disable_query_log +let $i = 10; +while ($i) { + eval INSERT INTO t1 (data) VALUES (REPEAT('a', 1024*1024)); + dec $i; +} +--enable_query_log + +let SEARCH_FILE=$MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_PATTERN=Tablespace 'test/t1' size .* bytes .* exceeds warning threshold; +--source include/search_pattern.inc + +DROP TABLE t1; + +--echo # Test configurable warning percentage +SET GLOBAL innodb_tablespace_size_warning_threshold = 10485760; +SET GLOBAL innodb_tablespace_size_warning_pct = 80; + +CREATE TABLE t1b ( + id INT AUTO_INCREMENT PRIMARY KEY, + data LONGBLOB +) ENGINE=InnoDB; + +--disable_query_log +let $i = 8; +while ($i) { + eval INSERT INTO t1b (data) VALUES (REPEAT('x', 1024*1024)); + dec $i; +} +--enable_query_log + +let SEARCH_PATTERN=Tablespace 'test/t1b' size .* bytes .* exceeds warning threshold; +--source include/search_pattern.inc + +DROP TABLE t1b; + +--echo # Test threshold set to zero disables warnings +SET GLOBAL innodb_tablespace_size_warning_threshold = 0; + +CREATE TABLE t2 ( + id INT AUTO_INCREMENT PRIMARY KEY, + data LONGBLOB +) ENGINE=InnoDB; + +--disable_query_log +let $i = 5; +while ($i) { + eval INSERT INTO t2 (data) VALUES (REPEAT('b', 1024*1024)); + dec $i; +} +--enable_query_log + +DROP TABLE t2; + +--echo # Test andon cord disable/enable +SET GLOBAL innodb_tablespace_size_warning_threshold = 10485760; +SET GLOBAL innodb_tablespace_size_warning_pct = 70; +SET GLOBAL innodb_tablespace_size_warning_enabled = FALSE; + +CREATE TABLE t3 ( + id INT AUTO_INCREMENT PRIMARY KEY, + data LONGBLOB +) ENGINE=InnoDB; + +--disable_query_log +let $i = 10; +while ($i) { + eval INSERT INTO t3 (data) VALUES (REPEAT('c', 1024*1024)); + dec $i; +} +--enable_query_log + +SET GLOBAL innodb_tablespace_size_warning_enabled = TRUE; + +--disable_query_log +INSERT INTO t3 (data) VALUES (REPEAT('d', 1024*1024)); +--enable_query_log + +let SEARCH_PATTERN=Tablespace 'test/t3' size .* bytes .* exceeds warning threshold; +--source include/search_pattern.inc + +DROP TABLE t3; + +--echo # Test TRUNCATE TABLE resets warning state +CREATE TABLE t4 ( + id INT AUTO_INCREMENT PRIMARY KEY, + data LONGBLOB +) ENGINE=InnoDB; + +--disable_query_log +let $i = 10; +while ($i) { + eval INSERT INTO t4 (data) VALUES (REPEAT('e', 1024*1024)); + dec $i; +} +--enable_query_log + +let SEARCH_PATTERN=Tablespace 'test/t4' size .* bytes .* exceeds warning threshold; +--source include/search_pattern.inc + +TRUNCATE TABLE t4; + +--disable_query_log +let $i = 10; +while ($i) { + eval INSERT INTO t4 (data) VALUES (REPEAT('f', 1024*1024)); + dec $i; +} +--enable_query_log + +let SEARCH_PATTERN=Tablespace 'test/t4' size .* bytes .* exceeds warning threshold; +--source include/search_pattern.inc + +DROP TABLE t4; + +# Restore original values +SET GLOBAL innodb_tablespace_size_warning_threshold = @old_threshold; +SET GLOBAL innodb_tablespace_size_warning_enabled = @old_enabled; +SET GLOBAL innodb_tablespace_size_warning_pct = @old_pct; diff --git a/storage/innobase/fsp/fsp0fsp.cc b/storage/innobase/fsp/fsp0fsp.cc index 2ce007224781..c58a1d9c2042 100644 --- a/storage/innobase/fsp/fsp0fsp.cc +++ b/storage/innobase/fsp/fsp0fsp.cc @@ -1218,6 +1218,89 @@ page_no_t fsp_header_get_tablespace_size(void) { return (size); } +/** Check if tablespace size exceeds warning threshold. +@param[in,out] space Tablespace +@param[in] new_size New size in pages +@return true if warning was emitted */ +static bool fsp_check_size_warning(fil_space_t *space, + page_no_t new_size) { + /* Named constant for high-resolution warning threshold */ + constexpr uint8_t high_resolution_pct = 90; + + if (!srv_tablespace_size_warning_enabled) { + return false; + } + + if (srv_tablespace_size_warning_threshold == 0) { + return false; + } + + /* Reset state if threshold changed */ + if (space->m_last_warning_threshold != + srv_tablespace_size_warning_threshold) { + space->m_last_size_warning_pct = 0; + space->m_last_warning_threshold = srv_tablespace_size_warning_threshold; + space->m_warning_count_in_decade = 0; + } + + const page_size_t page_size(space->flags); + uint64_t current_bytes = + static_cast(new_size) * page_size.physical(); + uint64_t current_pct = + (current_bytes * 100) / srv_tablespace_size_warning_threshold; + uint64_t display_pct = std::min(current_pct, static_cast(100)); + + if (display_pct < srv_tablespace_size_warning_pct) { + return false; + } + + bool should_warn = false; + + if (display_pct >= high_resolution_pct) { + /* Above high_resolution_pct: print on every 1% increase */ + should_warn = (display_pct > space->m_last_size_warning_pct); + } else { + /* Between tablespace_size_warning_pct and high_resolution_pct: + print at most twice per 10% (e.g., 70%, 77%, 81%, 89%) */ + uint8_t current_decade = static_cast(display_pct / 10); + uint8_t last_decade = space->m_last_size_warning_pct / 10; + + /* If we've moved to a new decade, reset the counter */ + if (current_decade > last_decade) { + space->m_warning_count_in_decade = 0; + } + + /* Warn if we haven't warned twice yet in this decade, percentage + increased, and there's at least a 5% gap since last warning + (or it's the first warning) */ + if (space->m_warning_count_in_decade < 2 && + display_pct > space->m_last_size_warning_pct && + (space->m_warning_count_in_decade == 0 || + display_pct >= + static_cast(space->m_last_size_warning_pct + 5))) { + should_warn = true; + } + } + + if (should_warn) { + ib::warn() << "Tablespace '" << space->name << "' size " << current_bytes + << " bytes (" << display_pct << "%)" + << " exceeds warning threshold of " + << srv_tablespace_size_warning_threshold << " bytes"; + + space->m_last_size_warning_pct = static_cast(display_pct); + + /* Increment counter only for the tiered warning range */ + if (display_pct < high_resolution_pct) { + space->m_warning_count_in_decade++; + } + + return true; + } + + return false; +} + /** Try to extend a single-table tablespace so that a page would fit in the data file. @param[in,out] space Tablespace @@ -1382,6 +1465,9 @@ static UNIV_COLD bool fsp_try_extend_data_file(fil_space_t *space, fsp_header_size_update(header, space->size_in_header, mtr); + /* Check if tablespace size exceeds warning threshold */ + fsp_check_size_warning(space, space->size_in_header); + return true; } diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index ee4304598f8e..f16e0bc8626e 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -22350,6 +22350,28 @@ static MYSQL_SYSVAR_ULONG( 1, /* Minimum value */ 5000, 0); /* Maximum value */ +static MYSQL_SYSVAR_ULONGLONG( + tablespace_size_warning_threshold, srv_tablespace_size_warning_threshold, + PLUGIN_VAR_RQCMDARG, + "Threshold in bytes for tablespace size warnings (0 = disabled)", nullptr, + nullptr, 17592186044416ULL, /* Default setting */ + 0, /* Minimum value */ + ULLONG_MAX, 0); /* Maximum value */ + +static MYSQL_SYSVAR_UINT( + tablespace_size_warning_pct, srv_tablespace_size_warning_pct, + PLUGIN_VAR_RQCMDARG, + "Percentage at which to start emitting tablespace size warnings", nullptr, + nullptr, 70, /* Default setting */ + 0, /* Minimum value */ + 100, 0); /* Maximum value */ + +static MYSQL_SYSVAR_BOOL(tablespace_size_warning_enabled, + srv_tablespace_size_warning_enabled, + PLUGIN_VAR_OPCMDARG, + "Enable/disable tablespace size warning feature", + nullptr, nullptr, true); + /* Default value is updated later in innodb_init_params due to the dependency on --container_aware startup option */ static MYSQL_SYSVAR_ULONG( @@ -23718,6 +23740,9 @@ static SYS_VAR *innobase_system_variables[] = { MYSQL_SYSVAR(monitor_reset_all), MYSQL_SYSVAR(purge_threads), MYSQL_SYSVAR(purge_batch_size), + MYSQL_SYSVAR(tablespace_size_warning_threshold), + MYSQL_SYSVAR(tablespace_size_warning_pct), + MYSQL_SYSVAR(tablespace_size_warning_enabled), #ifdef UNIV_DEBUG MYSQL_SYSVAR(background_drop_list_empty), MYSQL_SYSVAR(purge_run_now), diff --git a/storage/innobase/include/fil0fil.h b/storage/innobase/include/fil0fil.h index c4fa365b2036..539e2f511804 100644 --- a/storage/innobase/include/fil0fil.h +++ b/storage/innobase/include/fil0fil.h @@ -329,6 +329,15 @@ struct fil_space_t { becomes bigger than both this and srv_max_undo_log_size. */ page_no_t m_undo_initial{}; + /** Last percentage at which we emitted a size warning (0-100) */ + uint8_t m_last_size_warning_pct{0}; + + /** Threshold value used for the last warning */ + uint64_t m_last_warning_threshold{0}; + + /** Count of warnings emitted in the current decade (0-2) */ + uint8_t m_warning_count_in_decade{0}; + /** Tablespace name */ char *name{}; diff --git a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h index 602d52e48b88..1801983352bc 100644 --- a/storage/innobase/include/srv0srv.h +++ b/storage/innobase/include/srv0srv.h @@ -633,6 +633,15 @@ to treat NULL value when collecting statistics. It is not defined as enum type because the configure option takes unsigned integer type. */ extern ulong srv_innodb_stats_method; +/** Threshold in bytes for tablespace size warnings (0 = disabled) */ +extern unsigned long long srv_tablespace_size_warning_threshold; + +/** Percentage at which to start emitting tablespace size warnings */ +extern unsigned int srv_tablespace_size_warning_pct; + +/** Enable/disable tablespace size warning feature */ +extern bool srv_tablespace_size_warning_enabled; + /** Returns current value of the "innodb_open_files" configuration variable. */ long innobase_get_open_files_limit(); /** Sets new value of the "innodb_open_files" configuration variable to present diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc index d215fc107c03..cb7305528214 100644 --- a/storage/innobase/srv/srv0srv.cc +++ b/storage/innobase/srv/srv0srv.cc @@ -507,6 +507,15 @@ NULL value when collecting statistics. By default, it is set to SRV_STATS_NULLS_EQUAL(0), ie. all NULL value are treated equal */ ulong srv_innodb_stats_method = SRV_STATS_NULLS_EQUAL; +/* Threshold in bytes for tablespace size warnings (0 = disabled) */ +unsigned long long srv_tablespace_size_warning_threshold = 17592186044416ULL; + +/* Percentage at which to start emitting tablespace size warnings */ +unsigned int srv_tablespace_size_warning_pct = 70; + +/* Enable/disable tablespace size warning feature */ +bool srv_tablespace_size_warning_enabled = true; + bool tbsp_extend_and_initialize = true; #ifndef UNIV_HOTBACKUP