commit 6754e1abe3081b59fde83eace187685195e57562 Author: songhuaxiong.shx Date: Sun Oct 9 17:29:49 2022 +0800 [Feature] shrink the NET::buff to reduce memory usage. Background ========== In the current logic, NET::buff is only free when the thd is cleaned up. When the server receives a large packet, the memory will not be released immediately if the connection is not disconnected after processing, which will cause the current THD to hold too much unnecessary memory. The purpose of this feature is to save the NET memory resource usage under THD, thereby reducing the overall memory. User Interface ============== - net_shrink_count_threshold The shrink operation will be executed only when the number of times it is requested is higher than this value. Implementation ============== Try to shrink NET::buff of current THD when finish one query. The strategy: 1. NET::buff size can not be smaller than original value, and its minimum is equal to IO_SIZE after net_realloc(). If current buff size is less than original value or IO_SIZE, skip shrink. 2. NET::buff should be set to about half of what it used to be when finish shrink. 3. "shrink_request_count" will be set to zero when finish shrink or a new "big" query comes. diff --git a/include/mysql.h.pp b/include/mysql.h.pp index 31cb1bb8097..99764988296 100644 --- a/include/mysql.h.pp +++ b/include/mysql.h.pp @@ -154,6 +154,7 @@ typedef struct NET { char last_error[512]; char sqlstate[5 + 1]; void *extension; + unsigned int shrink_request_count; } NET; enum mysql_enum_shutdown_level { SHUTDOWN_DEFAULT = 0, @@ -209,6 +210,7 @@ unsigned long my_net_read(struct NET *net); void my_net_set_write_timeout(struct NET *net, unsigned int timeout); void my_net_set_read_timeout(struct NET *net, unsigned int timeout); void my_net_set_retry_count(struct NET *net, unsigned int retry_count); +void net_shrink(struct NET *net); struct rand_struct { unsigned long seed1, seed2, max_value; double max_value_dbl; diff --git a/include/mysql_com.h b/include/mysql_com.h index 9ccc476197e..c7103086889 100644 --- a/include/mysql_com.h +++ b/include/mysql_com.h @@ -879,6 +879,12 @@ enum SERVER_STATUS_flags_enum { #define NET_WRITE_TIMEOUT 60 /**< Timeout on write */ #define NET_WAIT_TIMEOUT 8 * 60 * 60 /**< Wait for new query */ +/** + Number of max shrink request times. If shrink_request_count exceeds, + shrink NET::buff will be performed. + */ +#define NET_SHRINK_COUNT_THRESHOLD 10 + /** Flag used by the parser. Kill only the query and not the connection. @@ -940,6 +946,8 @@ typedef struct NET { to maintain the server internal instrumentation for the connection. */ void *extension; + /** Number of times to request shrink NET::buff */ + unsigned int shrink_request_count; } NET; #define packet_error (~(unsigned long)0) @@ -1114,6 +1122,7 @@ unsigned long my_net_read(struct NET *net); void my_net_set_write_timeout(struct NET *net, unsigned int timeout); void my_net_set_read_timeout(struct NET *net, unsigned int timeout); void my_net_set_retry_count(struct NET *net, unsigned int retry_count); +void net_shrink(struct NET *net); struct rand_struct { unsigned long seed1, seed2, max_value; diff --git a/mysql-test/r/feature_shrink_netbuff_of_THD_Aone_39397920.result b/mysql-test/r/feature_shrink_netbuff_of_THD_Aone_39397920.result new file mode 100644 index 00000000000..ae7d8de0633 --- /dev/null +++ b/mysql-test/r/feature_shrink_netbuff_of_THD_Aone_39397920.result @@ -0,0 +1,29 @@ +SHOW VARIABLES LIKE 'net_shrink_count_threshold'; +Variable_name Value +net_shrink_count_threshold 10 +SHOW VARIABLES LIKE 'net_buffer_length'; +Variable_name Value +net_buffer_length 16384 +CREATE USER 'shrink_test_user'@'localhost'; +USE test; +CREATE TABLE test.SHRINK_TABLE (a char(255)); +SET GLOBAL net_shrink_count_threshold = 5; +"Show NET:buff the 1st time. The NET::buff should be "0"." +COUNT_ALLOC COUNT_FREE CURRENT_NUMBER_OF_BYTES_USED +0 0 0 +"Show NET:buff the 2nd time. The NET::buff should be a relatively large value." +COUNT_ALLOC COUNT_FREE CURRENT_NUMBER_OF_BYTES_USED +1 0 40999 +"Show NET:buff the 3rd time. Do "SELECT 1" for 6 times and the NET::buff should be about half of what it used to be or original value." +COUNT_ALLOC COUNT_FREE CURRENT_NUMBER_OF_BYTES_USED +2 1 20519 +"Show NET:buff the 4th time. Do "SELECT 1" for 6 times and the NET::buff should be about half of what it used to be or original value." +COUNT_ALLOC COUNT_FREE CURRENT_NUMBER_OF_BYTES_USED +3 2 16423 +"Show NET:buff the 5th time. Do "SELECT 1" for 6 times and the NET::buff should not be smaller than original value." +COUNT_ALLOC COUNT_FREE CURRENT_NUMBER_OF_BYTES_USED +3 2 16423 +current buff used is equal to net buffer length. +SET GLOBAL net_shrink_count_threshold = 10; +DROP USER 'shrink_test_user'@'localhost'; +DROP TABLE test.SHRINK_TABLE; diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index c3259c88c11..cc5b58e74f0 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -832,6 +832,10 @@ The following options may be given as the first argument: before aborting the read --net-retry-count=# If a read on a communication port is interrupted, retry this many times before giving up + --net-shrink-count-threshold=# + The shrink operation will be executed only when the + number of times it is requested is higher than this + value. --net-write-timeout=# Number of seconds to wait for a block to be written to a connection before aborting the write @@ -2454,6 +2458,7 @@ mysql-native-password-proxy-users FALSE net-buffer-length 16384 net-read-timeout 30 net-retry-count 10 +net-shrink-count-threshold 10 net-write-timeout 60 new FALSE no-dd-upgrade FALSE diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 9606ace3aba..65144426fd9 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -246,24 +246,28 @@ Variable_name Value net_buffer_length 1024 net_read_timeout 300 net_retry_count 10 +net_shrink_count_threshold 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_read_timeout 300 net_retry_count 10 +net_shrink_count_threshold 10 net_write_timeout 200 show session variables like 'net_%'; Variable_name Value net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 +net_shrink_count_threshold 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_read_timeout 30 net_retry_count 10 +net_shrink_count_threshold 10 net_write_timeout 60 set global net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; Warnings: @@ -273,12 +277,14 @@ Variable_name Value net_buffer_length 7168 net_read_timeout 900 net_retry_count 10 +net_shrink_count_threshold 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_read_timeout 900 net_retry_count 10 +net_shrink_count_threshold 10 net_write_timeout 1000 set global net_buffer_length=1; Warnings: diff --git a/mysql-test/suite/sys_vars/r/all_vars.result b/mysql-test/suite/sys_vars/r/all_vars.result index 63b9c064ae6..f9000253956 100644 --- a/mysql-test/suite/sys_vars/r/all_vars.result +++ b/mysql-test/suite/sys_vars/r/all_vars.result @@ -178,6 +178,8 @@ multi_blocks_count multi_blocks_count multi_blocks_ddl_count multi_blocks_ddl_count +net_shrink_count_threshold +net_shrink_count_threshold opt_enable_rds_priv_strategy opt_enable_rds_priv_strategy opt_indexstat diff --git a/mysql-test/t/feature_shrink_netbuff_of_THD_Aone_39397920.test b/mysql-test/t/feature_shrink_netbuff_of_THD_Aone_39397920.test new file mode 100644 index 00000000000..7dd4d864f2d --- /dev/null +++ b/mysql-test/t/feature_shrink_netbuff_of_THD_Aone_39397920.test @@ -0,0 +1,139 @@ +--source include/have_nodebug.inc + +# 1) Check PFS is ON and NET::buff instrument is set. +if (`SELECT @@GLOBAL.performance_schema = FALSE`) { + skip Test requires @@GLOBAL.performance_schema = TRUE.; +} + +let $INSTRUMENT = `SELECT ENABLED FROM performance_schema.setup_instruments WHERE NAME LIKE 'memory/sql/NET::buff'`; +if ($INSTRUMENT != YES) { + skip Test requires instrument set.; +} + + +# 2) Show variables. +SHOW VARIABLES LIKE 'net_shrink_count_threshold'; +SHOW VARIABLES LIKE 'net_buffer_length'; + + +# 3) Prepare to test +# a) Create a big SQL statement to file. +perl; + my $dir = $ENV{'MYSQLTEST_VARDIR'}; + open (OUTPUT, ">$dir/tmp/shrink_test.inc"); + print OUTPUT "INSERT INTO test.SHRINK_TABLE VALUES\n"; + my $prefix = "(\"rds_mysql_rds_mysql_rds_mysql_rds_mysql_rds_mysql_rds_mysql_rds_mysql_rds_mysql_rds_mysql_rds_mysql_rds_mysql_rds_mysql"; + for ( $i = 1; $i < 300; $i = $i + 1 ) { + print OUTPUT "${prefix}_${i}\"),\n"; + } + print OUTPUT "${prefix}_300\");"; + close (OUTPUT); +EOF + +# b) New session to execute above SQL. It should not be in the same session as querying NET::buff +# because "SELECT..." might be counted to "net_shrink_count". +CREATE USER 'shrink_test_user'@'localhost'; +USE test; +CREATE TABLE test.SHRINK_TABLE (a char(255)); +SET GLOBAL net_shrink_count_threshold = 5; +--connect(con1, localhost, shrink_test_user, , ) +--connection default + + +# 4) Test +# a) Show original NET::buff. +--disable_query_log +--echo "Show NET:buff the 1st time. The NET::buff should be \"0\"." +SELECT COUNT_ALLOC, COUNT_FREE, CURRENT_NUMBER_OF_BYTES_USED 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 = +(SELECT ID FROM information_schema.PROCESSLIST where USER = 'shrink_test_user')); + +# b) Execute the big SQL. +--connection con1 +--source $MYSQLTEST_VARDIR/tmp/shrink_test.inc + +# c) Show NET::buff again +--connection default +--echo "Show NET:buff the 2nd time. The NET::buff should be a relatively large value." +SELECT COUNT_ALLOC, COUNT_FREE, CURRENT_NUMBER_OF_BYTES_USED 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 = +(SELECT ID FROM information_schema.PROCESSLIST where USER = 'shrink_test_user')); + +# d) Do "SELECT 1" 6 times and then show NET::buff again. +--connection con1 +--echo "Show NET:buff the 3rd time. Do \"SELECT 1\" for 6 times and the NET::buff should be about half of what it used to be or original value." +--disable_result_log +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +--enable_result_log + +--connection default +SELECT COUNT_ALLOC, COUNT_FREE, CURRENT_NUMBER_OF_BYTES_USED 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 = +(SELECT ID FROM information_schema.PROCESSLIST where USER = 'shrink_test_user')); + +# f) Repeat +--connection con1 +--echo "Show NET:buff the 4th time. Do \"SELECT 1\" for 6 times and the NET::buff should be about half of what it used to be or original value." +--disable_result_log +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +--enable_result_log + +--connection default +SELECT COUNT_ALLOC, COUNT_FREE, CURRENT_NUMBER_OF_BYTES_USED 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 = +(SELECT ID FROM information_schema.PROCESSLIST where USER = 'shrink_test_user')); + +--connection con1 +--echo "Show NET:buff the 5th time. Do \"SELECT 1\" for 6 times and the NET::buff should not be smaller than original value." +--disable_result_log +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +SELECT 1; +--enable_result_log + +--connection default +SELECT COUNT_ALLOC, COUNT_FREE, CURRENT_NUMBER_OF_BYTES_USED 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 = +(SELECT ID FROM information_schema.PROCESSLIST where USER = 'shrink_test_user')); +--enable_query_log + +# g) Show CURRENT_NUMBER_OF_BYTES_USED +let $net_buffer_length = query_get_value(SHOW VARIABLES LIKE 'net_buffer_length', Value, 1); +let $current_buff_used = `SELECT (SELECT CURRENT_NUMBER_OF_BYTES_USED 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 = +(SELECT ID FROM information_schema.PROCESSLIST where USER = 'shrink_test_user'))) - 39`; +if ($current_buff_used == $net_buffer_length) { + --echo current buff used is equal to net buffer length. +} + + +# 5) Cleanup +--disconnect con1 +SET GLOBAL net_shrink_count_threshold = 10; +remove_file $MYSQLTEST_VARDIR/tmp/shrink_test.inc; +DROP USER 'shrink_test_user'@'localhost'; +DROP TABLE test.SHRINK_TABLE; \ No newline at end of file diff --git a/sql-common/net_serv.cc b/sql-common/net_serv.cc index 080b31dd458..db4268e9a37 100644 --- a/sql-common/net_serv.cc +++ b/sql-common/net_serv.cc @@ -168,6 +168,7 @@ bool my_net_init(NET *net, Vio *vio) { net->last_errno = 0; #ifdef MYSQL_SERVER net->extension = nullptr; + net->shrink_request_count = 0; #else NET_EXTENSION *ext = net_extension_init(); ext->net_async_context->cur_pos = net->buff + net->where_b; @@ -2268,3 +2269,37 @@ void my_net_set_retry_count(NET *net, uint retry_count) { net->retry_count = retry_count; if (net->vio) net->vio->retry_count = retry_count; } + +/** + Shrink the memory of NET::buff. + + @param[in, out] net NET structure +*/ +void net_shrink(NET *net) { + assert(net->reading_or_writing == 0); +#ifdef MYSQL_SERVER + THD *thd = current_thd; + /** + NET::buff size can not be smaller than origin one, and its minimum is equal to + IO_SIZE after net_realloc(). + */ + if (net->max_packet == thd->variables.net_buffer_length || + net->max_packet <= IO_SIZE) { + return; + } + + ulong buf_used = thd->get_protocol_classic()->get_packet_length(); + ulong shrink_length = max(thd->variables.net_buffer_length, net->max_packet / 2); + /** The actual usage of buf is much less than the alloced memory */ + if (buf_used <= shrink_length) { + if (++net->shrink_request_count > thd->variables.net_shrink_count_threshold) { + net_realloc(net, shrink_length); + /** Shrink is done, reset shrink_request_count. */ + net->shrink_request_count = 0; + } + } else { + /** Avoid to shrink because more memory may be used next time. */ + net->shrink_request_count = 0; + } +#endif +} \ No newline at end of file diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 0e040baf449..fd7c408425c 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2492,6 +2492,12 @@ done: else thd->mem_root->Clear(); + /* If current length of net::buff is bigger than origin one, shrink. */ + if (thd->is_classic_protocol()) { + NET *net = thd->get_protocol_classic()->get_net(); + net_shrink(net); + } + /* SHOW PROFILE instrumentation, end */ #if defined(ENABLED_PROFILING) thd->profiling->finish_current_query(); diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 63118d30838..88fb4a4f8af 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -3176,6 +3176,13 @@ static Sys_var_ulong Sys_net_retry_count( NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(nullptr), ON_UPDATE(fix_net_retry_count)); +static Sys_var_ulong Sys_net_shrink_count_threshold( + "net_shrink_count_threshold", + "The shrink operation will be executed only when the number of times " + "it is requested is higher than this value.", + SESSION_VAR(net_shrink_count_threshold), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(1, ULONG_MAX), DEFAULT(NET_SHRINK_COUNT_THRESHOLD), BLOCK_SIZE(1)); + static Sys_var_bool Sys_new_mode("new", "Use very new possible \"unsafe\" functions", SESSION_VAR(new_mode), CMD_LINE(OPT_ARG, 'n'), diff --git a/sql/system_variables.h b/sql/system_variables.h index 1aa48b1c1ce..582922e03b1 100644 --- a/sql/system_variables.h +++ b/sql/system_variables.h @@ -229,6 +229,7 @@ struct System_variables { ulong net_retry_count; ulong net_wait_timeout; ulong net_write_timeout; + ulong net_shrink_count_threshold; ulong optimizer_prune_level; ulong optimizer_search_depth; ulonglong parser_max_mem_size;