From a5bcdde5ae078a1cff60d6a766630835ae2b9beb Mon Sep 17 00:00:00 2001 From: Libing Song Date: Tue, 11 Aug 2026 01:04:53 +0800 Subject: [PATCH] Shrink NET::buff to reduce memory usage Problem ======= NET::buff grows to accommodate large incoming packets but does not return to its initial size. Long-lived connections can therefore retain excess memory after processing occasional large requests. Solution ======== Add the net_buffer_shrink_threshold session variable. When the current packet uses no more than half of an expanded buffer, count a shrink request. After the configured number of consecutive requests, halve the buffer without shrinking below net_buffer_length or IO_SIZE. Preserve the existing network error state if the allocation fails. --- include/mysql_com_server.h | 6 +- mysql-test/r/all_persisted_variables.result | 8 +- mysql-test/r/mysqld--help-notwin.result | 7 + mysql-test/r/shrink_net_buffer.result | 36 ++++ mysql-test/r/variables.result | 6 + mysql-test/suite/sys_vars/r/all_vars.result | 2 + mysql-test/t/all_persisted_variables.test | 2 +- mysql-test/t/shrink_net_buffer.test | 173 ++++++++++++++++++ sql-common/net_serv.cc | 30 +++ sql/conn_handler/init_net_server_extension.cc | 1 + sql/sql_class.cc | 50 +++++ sql/sql_class.h | 3 + sql/sql_parse.cc | 8 +- sql/sys_vars.cc | 12 ++ sql/system_variables.h | 1 + 15 files changed, 338 insertions(+), 7 deletions(-) create mode 100644 mysql-test/r/shrink_net_buffer.result create mode 100644 mysql-test/t/shrink_net_buffer.test diff --git a/include/mysql_com_server.h b/include/mysql_com_server.h index aeb212b07df1..c7da4b795377 100644 --- a/include/mysql_com_server.h +++ b/include/mysql_com_server.h @@ -63,6 +63,9 @@ typedef struct NET_SERVER { struct compression_attributes compression; mysql_compress_context compress_ctx; bool timeout_on_full_packet; + + /** Number of consecutive requests to shrink NET::buff. */ + unsigned int shrink_requests; } NET_SERVER; inline void net_server_ext_init(NET_SERVER *ns) { @@ -71,6 +74,7 @@ inline void net_server_ext_init(NET_SERVER *ns) { ns->m_after_header = nullptr; ns->compress_ctx.algorithm = MYSQL_UNCOMPRESSED; ns->timeout_on_full_packet = false; + ns->shrink_requests = 0; } - +bool net_shrink(struct NET *net, size_t length); #endif diff --git a/mysql-test/r/all_persisted_variables.result b/mysql-test/r/all_persisted_variables.result index 57c9777c0fa5..367de83ec142 100644 --- a/mysql-test/r/all_persisted_variables.result +++ b/mysql-test/r/all_persisted_variables.result @@ -48,7 +48,7 @@ include/assert.inc [Expect 500+ variables in the table. Due to open Bugs, we are # Test SET PERSIST -include/assert.inc [Expect 451 persisted variables in the table.] +include/assert.inc [Expect 452 persisted variables in the table.] ************************************************************ * 3. Restart server, it must preserve the persisted variable @@ -56,9 +56,9 @@ include/assert.inc [Expect 451 persisted variables in the table.] ************************************************************ # restart -include/assert.inc [Expect 451 persisted variables in persisted_variables table.] -include/assert.inc [Expect 451 persisted variables shown as PERSISTED in variables_info table.] -include/assert.inc [Expect 451 persisted variables with matching peristed and global values.] +include/assert.inc [Expect 452 persisted variables in persisted_variables table.] +include/assert.inc [Expect 452 persisted variables shown as PERSISTED in variables_info table.] +include/assert.inc [Expect 452 persisted variables with matching peristed and global values.] ************************************************************ * 4. Test RESET PERSIST IF EXISTS. Verify persisted variable diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index 036e914de1d9..b4388f18fa3c 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -775,6 +775,12 @@ The following options may be given as the first argument: --myisam-use-mmap Use memory mapping for reading and writing MyISAM tables --net-buffer-length=# Buffer length for TCP/IP and socket communication + --net-buffer-shrink-threshold=# + If the size currently required for net buffer is less + than half of the allocated size, a shrink is requested. + Once the number of consecutive requests reaches this + threshold, the buffer is halved, but never below + net_buffer_length. A value of 0 disables shrink --net-read-timeout=# Number of seconds to wait for more data from a connection before aborting the read @@ -1867,6 +1873,7 @@ myisam-sort-buffer-size 8388608 myisam-stats-method nulls_unequal myisam-use-mmap FALSE net-buffer-length 16384 +net-buffer-shrink-threshold 5 net-read-timeout 30 net-retry-count 10 net-write-timeout 60 diff --git a/mysql-test/r/shrink_net_buffer.result b/mysql-test/r/shrink_net_buffer.result new file mode 100644 index 000000000000..87d396fbb24a --- /dev/null +++ b/mysql-test/r/shrink_net_buffer.result @@ -0,0 +1,36 @@ +SHOW VARIABLES LIKE 'net_buffer_length'; +Variable_name Value +net_buffer_length 16384 +CREATE USER 'shrink_test_user'@'localhost'; +SET GLOBAL net_buffer_shrink_threshold = 5; +[connection con1] +[connection default] +"NET::buff after a large query; capacity should have grown." +CURRENT_ALLOCATIONS CURRENT_NUMBER_OF_BYTES_USED - 39 +1 36864 +[connection con1] +[connection default] +"NET::buff after four small queries; capacity should not be shrunk." +CURRENT_ALLOCATIONS CURRENT_NUMBER_OF_BYTES_USED - 39 +1 36864 +[connection con1] +[connection default] +"NET::buff after five small queries; capacity should be about half." +CURRENT_ALLOCATIONS CURRENT_NUMBER_OF_BYTES_USED - 39 +1 20480 +[connection con1] +[connection default] +"NET::buff after ten small queries; capacity should reach its minimum." +CURRENT_ALLOCATIONS CURRENT_NUMBER_OF_BYTES_USED - 39 +1 16384 +[connection con1] +[connection default] +"NET::buff remains at its minimum after five more small queries." +CURRENT_ALLOCATIONS CURRENT_NUMBER_OF_BYTES_USED - 39 +1 16384 +[connection con1] +[connection default] +"NET::buff after another large query; capacity should grow again." +CURRENT_ALLOCATIONS CURRENT_NUMBER_OF_BYTES_USED - 39 +1 36864 +DROP USER 'shrink_test_user'@'localhost'; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 7c30d98786ca..a8179c88d87c 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -245,24 +245,28 @@ set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; show global variables like 'net_%'; Variable_name Value net_buffer_length 1024 +net_buffer_shrink_threshold 5 net_read_timeout 300 net_retry_count 10 net_write_timeout 200 select * from performance_schema.global_variables where variable_name like 'net_%' order by 1; VARIABLE_NAME VARIABLE_VALUE net_buffer_length 1024 +net_buffer_shrink_threshold 5 net_read_timeout 300 net_retry_count 10 net_write_timeout 200 show session variables like 'net_%'; Variable_name Value net_buffer_length 16384 +net_buffer_shrink_threshold 5 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 select * from performance_schema.session_variables where variable_name like 'net_%' order by 1; VARIABLE_NAME VARIABLE_VALUE net_buffer_length 16384 +net_buffer_shrink_threshold 5 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 @@ -272,12 +276,14 @@ Warning 1292 Truncated incorrect net_buffer_length value: '8000' show global variables like 'net_%'; Variable_name Value net_buffer_length 7168 +net_buffer_shrink_threshold 5 net_read_timeout 900 net_retry_count 10 net_write_timeout 1000 select * from performance_schema.global_variables where variable_name like 'net_%' order by 1; VARIABLE_NAME VARIABLE_VALUE net_buffer_length 7168 +net_buffer_shrink_threshold 5 net_read_timeout 900 net_retry_count 10 net_write_timeout 1000 diff --git a/mysql-test/suite/sys_vars/r/all_vars.result b/mysql-test/suite/sys_vars/r/all_vars.result index bc7056288965..61b2301e174b 100644 --- a/mysql-test/suite/sys_vars/r/all_vars.result +++ b/mysql-test/suite/sys_vars/r/all_vars.result @@ -72,6 +72,8 @@ master_verify_checksum master_verify_checksum max_length_for_sort_data max_length_for_sort_data +net_buffer_shrink_threshold +net_buffer_shrink_threshold optimizer_max_subgraph_pairs optimizer_max_subgraph_pairs partial_revokes diff --git a/mysql-test/t/all_persisted_variables.test b/mysql-test/t/all_persisted_variables.test index 9cd22d1a0952..b33b0af72930 100644 --- a/mysql-test/t/all_persisted_variables.test +++ b/mysql-test/t/all_persisted_variables.test @@ -58,7 +58,7 @@ let $total_global_vars=`SELECT COUNT(*) AND variable_name NOT LIKE '%pqc%' AND variable_name NOT LIKE '%tls_kex%'`; -let $total_persistent_vars=451; +let $total_persistent_vars=452; --echo *************************************************************** --echo * 0. Verify that variables present in performance_schema.global diff --git a/mysql-test/t/shrink_net_buffer.test b/mysql-test/t/shrink_net_buffer.test new file mode 100644 index 000000000000..579388326ce8 --- /dev/null +++ b/mysql-test/t/shrink_net_buffer.test @@ -0,0 +1,173 @@ +# Test NET::buff shrinking for classic protocol connections. +# +# The test verifies that: +# 1. A query larger than net_buffer_length grows NET::buff. +# 2. Five consecutive small queries shrink the buffer by approximately half. +# 3. Further small queries shrink it to, but never below, net_buffer_length. +# 4. A later large query can grow the buffer again after it was shrunk. +# +# Expected result: PFS allocation statistics show grow, gradual shrink, a +# stable lower bound, and successful regrowth in that order. + +--source include/have_nodebug.inc + +# ============================================================================= +# Prerequisites +# ============================================================================= + +if (`SELECT @@GLOBAL.performance_schema = FALSE`) { + skip Test requires @@GLOBAL.performance_schema = TRUE.; +} + +let $instrument = `SELECT ENABLED FROM performance_schema.setup_instruments + WHERE NAME = 'memory/sql/NET::buff'`; +if ($instrument != YES) { + skip Test requires memory/sql/NET::buff instrumentation.; +} + +# ============================================================================= +# Setup +# ============================================================================= + +let $old_net_buffer_shrink_threshold = query_get_value( + SHOW VARIABLES LIKE "net_buffer_shrink_threshold", Value, 1); +SHOW VARIABLES LIKE 'net_buffer_length'; + +# Generate padding for a query larger than net_buffer_length. The padding is +# produced on the default connection so setup cannot affect con1's shrink count. +let $query_padding = `SELECT REPEAT('x', 32768)`; + +CREATE USER 'shrink_test_user'@'localhost'; +SET GLOBAL net_buffer_shrink_threshold = 5; +--connect(con1, localhost, shrink_test_user, , ) +let $con1_processlist_id = `SELECT CONNECTION_ID()`; +--connection default +--disable_query_log +--eval SET @con1_processlist_id = $con1_processlist_id + +# Extra 39 bytes are allocated for +# PSI header (32 B), net header (4 B), compression header (4 B) +# They are not counted. + +let $pfs_net_buff_stmt = + SELECT COUNT_ALLOC - COUNT_FREE AS CURRENT_ALLOCATIONS, + CURRENT_NUMBER_OF_BYTES_USED - 39 + FROM performance_schema.memory_summary_by_thread_by_event_name + WHERE EVENT_NAME = 'memory/sql/NET::buff' + AND THREAD_ID = (SELECT THREAD_ID + FROM performance_schema.threads + WHERE PROCESSLIST_ID = @con1_processlist_id); + +# ============================================================================= +# Test 1: A large incoming query grows NET::buff +# ============================================================================= + +--connection con1 +--echo [connection con1] +--disable_result_log +--eval SELECT 1 /* $query_padding */ +--enable_result_log + +--connection default +--echo [connection default] +--echo "NET::buff after a large query; capacity should have grown." +--eval $pfs_net_buff_stmt + +# ============================================================================= +# Test 2: Five small queries shrink the grown buffer by about half +# ============================================================================= + +--connection con1 +--echo [connection con1] +--disable_result_log +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +--enable_result_log + +--connection default +--echo [connection default] +--echo "NET::buff after four small queries; capacity should not be shrunk." +--eval $pfs_net_buff_stmt + +--connection con1 +--echo [connection con1] +--disable_result_log +SELECT 1; +--enable_result_log + +--connection default +--echo [connection default] +--echo "NET::buff after five small queries; capacity should be about half." +--eval $pfs_net_buff_stmt + +# ============================================================================= +# Test 3: Five more small queries reach net_buffer_length +# ============================================================================= + +--connection con1 +--echo [connection con1] +--disable_result_log +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +--enable_result_log + +--connection default +--echo [connection default] +--echo "NET::buff after ten small queries; capacity should reach its minimum." +--eval $pfs_net_buff_stmt + +let $net_buffer_length = query_get_value( + SHOW VARIABLES LIKE 'net_buffer_length', Value, 1); +let $current_buffer_capacity = `$pfs_net_buff_stmt`; + +if ($current_buffer_capacity == $net_buffer_length) { + --echo NET::buff capacity is equal to net_buffer_length. +} + +# ============================================================================= +# Test 4: Further small queries do not shrink below net_buffer_length +# ============================================================================= + +--connection con1 +--echo [connection con1] +--disable_result_log +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +--enable_result_log + +--connection default +--echo [connection default] +--echo "NET::buff remains at its minimum after five more small queries." +--eval $pfs_net_buff_stmt + +# ============================================================================= +# Test 5: A later large query grows the buffer again +# ============================================================================= + +--connection con1 +--echo [connection con1] +--disable_result_log +--eval SELECT 1 /* $query_padding */ +--enable_result_log + +--connection default +--echo [connection default] +--echo "NET::buff after another large query; capacity should grow again." +--eval $pfs_net_buff_stmt + +# ============================================================================= +# Cleanup +# ============================================================================= + +--disconnect con1 +--eval SET GLOBAL net_buffer_shrink_threshold = $old_net_buffer_shrink_threshold +--enable_query_log +DROP USER 'shrink_test_user'@'localhost'; diff --git a/sql-common/net_serv.cc b/sql-common/net_serv.cc index 4b904a46b69c..8ee7efb600a6 100644 --- a/sql-common/net_serv.cc +++ b/sql-common/net_serv.cc @@ -258,6 +258,36 @@ bool net_realloc(NET *net, size_t length) { return false; } +#ifdef MYSQL_SERVER +/** + Shrink net buffer. + + @param net NET handler + @param length shrink net buffer to length. length should smaller + than the original buffer size + + @retval true failed to shrink + @retval false Shrunk to length successfully +*/ +bool net_shrink(NET *net, size_t length) { + uchar *buff; + size_t pkt_length; + + assert(length < net->max_packet); + pkt_length = (length + IO_SIZE - 1) & ~(IO_SIZE - 1); + + if (!(buff = (uchar *)my_realloc( + key_memory_NET_buff, (char *)net->buff, + pkt_length + NET_HEADER_SIZE + COMP_HEADER_SIZE, MYF(0)))) + return true; + + net->buff = net->write_pos = buff; + net->buff_end = buff + pkt_length; + net->max_packet = ulong(pkt_length); + return false; +} +#endif + /** Clear (reinitialize) the NET structure for a new command. diff --git a/sql/conn_handler/init_net_server_extension.cc b/sql/conn_handler/init_net_server_extension.cc index 95777baa9bc9..a2d20a94e0c7 100644 --- a/sql/conn_handler/init_net_server_extension.cc +++ b/sql/conn_handler/init_net_server_extension.cc @@ -132,6 +132,7 @@ void init_net_server_extension(THD *thd) { thd->m_net_server_extension.m_after_header = net_after_header_psi; thd->m_net_server_extension.compress_ctx.algorithm = MYSQL_UNCOMPRESSED; thd->m_net_server_extension.timeout_on_full_packet = false; + thd->m_net_server_extension.shrink_requests = 0; /* Activate this private extension for the mysqld server. */ thd->get_protocol_classic()->get_net()->extension = &thd->m_net_server_extension; diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 4eab178d0d87..ca145575259c 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -584,6 +584,56 @@ THD::Attachable_trx_rw::Attachable_trx_rw(THD *thd) thd->get_transaction()->xid_state()->set_state(XID_STATE::XA_NOTR); } +/** + Try to shrink an expanded NET::buff. + + NET::buff grows when the server receives a large packet and otherwise keeps + the expanded capacity until the THD is destroyed. + + Shrinking needs to meet the following conditions: + 1. The currently required size of NET::buff is less than half the allocated + one. + 2. Condition 1 is met 'net_buffer_shrink_threshold' times consecutively. + + @param[in,out] net Network state containing the buffer. + @param[in] input_packet_length Length of the current input packet. + + @return This function does not return a status. If allocation fails, the + original buffer and network error state are preserved. +*/ +void THD::try_shrink_net_buffer(NET *net, ulong input_packet_length) { + if (net->max_packet <= variables.net_buffer_length || + net->max_packet <= IO_SIZE || + net->extension == nullptr || + variables.net_buffer_shrink_threshold == 0) { + return; + } + + auto net_serv = static_cast(net->extension); + + /* Shrink to half the current capacity, but not below net_buffer_length. */ + const ulong target_buffer_capacity = + max(variables.net_buffer_length, net->max_packet / 2); + + /* Avoid shrinking it as additional memory might be needed for future use. */ + if (input_packet_length > target_buffer_capacity) { + net_serv->shrink_requests = 0; + return; + } + + /* Wait for the configured number of consecutive shrink requests. */ + if (++net_serv->shrink_requests < variables.net_buffer_shrink_threshold) { + return; + } + + if (net_shrink(net, target_buffer_capacity)) { + --net_serv->shrink_requests; + return; + } + + net_serv->shrink_requests = 0; +} + void THD::enter_stage(const PSI_stage_info *new_stage, PSI_stage_info *old_stage, const char *calling_func [[maybe_unused]], diff --git a/sql/sql_class.h b/sql/sql_class.h index 0462c6ef28c2..9057b3d95268 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1324,6 +1324,9 @@ class THD : public MDL_context_owner, std::atomic m_cached_rw_status; public: + /** Try to shrink an expanded NET::buff after consecutive candidates. */ + void try_shrink_net_buffer(NET *net, ulong input_packet_length); + /// Locks the query plan of this THD void lock_query_plan() { mysql_mutex_lock(&LOCK_query_plan); } void unlock_query_plan() { mysql_mutex_unlock(&LOCK_query_plan); } diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 8587a1978067..b3a932d13d1a 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2530,7 +2530,13 @@ bool dispatch_command(THD *thd, const COM_DATA *com_data, else thd->mem_root->Clear(); - /* SHOW PROFILE instrumentation, end */ + if (thd->is_classic_protocol()) { + Protocol_classic *protocol = thd->get_protocol_classic(); + NET *net = protocol->get_net(); + thd->try_shrink_net_buffer(net, protocol->get_packet_length()); + } + + /* SHOW PROFILE instrumentation, end */ #if defined(ENABLED_PROFILING) thd->profiling->finish_current_query(); #endif diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 09edc90041a3..95d39f583a9c 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3086,6 +3086,18 @@ static Sys_var_ulong Sys_net_buffer_length( VALID_RANGE(1024, 1024 * 1024), DEFAULT(16384), BLOCK_SIZE(1024), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_net_buffer_length)); +#define DEFAULT_NET_BUFFER_SHRINK_THRESHOLD 5 + +static Sys_var_ulong Sys_net_buffer_shrink_threshold( + "net_buffer_shrink_threshold", + "If the size currently required for net buffer is less than half of the " + "allocated size, a shrink is requested. Once the number of consecutive " + "requests reaches this threshold, the buffer is halved, but never below " + "net_buffer_length. A value of 0 disables shrink", + SESSION_VAR(net_buffer_shrink_threshold), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, ULONG_MAX), DEFAULT(DEFAULT_NET_BUFFER_SHRINK_THRESHOLD), + BLOCK_SIZE(1)); + static bool fix_net_read_timeout(sys_var *self, THD *thd, enum_var_type type) { if (!self->is_global_persist(type)) { // net_buffer_length is a specific property for the classic protocols diff --git a/sql/system_variables.h b/sql/system_variables.h index 2271656a9384..66610b7ce272 100644 --- a/sql/system_variables.h +++ b/sql/system_variables.h @@ -254,6 +254,7 @@ struct System_variables { ulong net_retry_count; ulong net_wait_timeout; ulong net_write_timeout; + ulong net_buffer_shrink_threshold; ulong optimizer_prune_level; ulong optimizer_search_depth; ulong optimizer_max_subgraph_pairs;