Bug #108912 Optimize the memory of NET::buff of THD
Submitted: 28 Oct 2022 12:09 Modified: 7 Dec 2022 11:17
Reporter: Huaxiong Song (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Connection Handling Severity:S4 (Feature request)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[28 Oct 2022 12:09] Huaxiong Song
Description:
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.

How to repeat:
variables:
  net_buffer_length = 16k
  net_shrink_count_threshold = 5(new variable, default value is 10)

Step:
Do the following sqls on both server(without/with the patch) by user "xxx".
  T1. Execute a long sql which needs more memory the origin.
  T2. Do "SELECT 1" for 6 times;
  T3. Do "SELECT 1" for 6 times;

Result:
Show NET::buff memory by another user.
A. Without the patch.
# After T1.
MySQL [(none)]> 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 = 'xxx'));
+-------------+------------+------------------------------+
| COUNT_ALLOC | COUNT_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+-------------+------------+------------------------------+
|           1 |          0 |                        40999 |
+-------------+------------+------------------------------+
1 row in set (0.00 sec)

# After T2.
MySQL [(none)]> 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 = 'xxx'));
+-------------+------------+------------------------------+
| COUNT_ALLOC | COUNT_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+-------------+------------+------------------------------+
|           1 |          0 |                        40999 |
+-------------+------------+------------------------------+
1 row in set (0.00 sec)

# After T3.
MySQL [(none)]> 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 = 'xxx'));
+-------------+------------+------------------------------+
| COUNT_ALLOC | COUNT_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+-------------+------------+------------------------------+
|           1 |          0 |                        40999 |
+-------------+------------+------------------------------+
1 row in set (0.00 sec)

B. With the patch.
# After T1.
MySQL [(none)]> 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 = 'songhuaxiong'));
+-------------+------------+------------------------------+
| COUNT_ALLOC | COUNT_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+-------------+------------+------------------------------+
|           1 |          0 |                        40999 |
+-------------+------------+------------------------------+
1 row in set (0.00 sec)

# After T2.
MySQL [(none)]> 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 = 'songhuaxiong'));
+-------------+------------+------------------------------+
| COUNT_ALLOC | COUNT_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+-------------+------------+------------------------------+
|           2 |          1 |                        20519 |
+-------------+------------+------------------------------+
1 row in set (0.00 sec)

# After T3.
MySQL [(none)]> 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 = 'songhuaxiong'));
+-------------+------------+------------------------------+
| COUNT_ALLOC | COUNT_FREE | CURRENT_NUMBER_OF_BYTES_USED |
+-------------+------------+------------------------------+
|           3 |          2 |                        16423 |
+-------------+------------+------------------------------+

Suggested fix:
See the patch.
[28 Oct 2022 12:12] Huaxiong Song
Optimize NET::buff of THD

Attachment: NET_buff_shrink.diff (application/octet-stream, text), 16.74 KiB.

[11 Nov 2022 2:11] Huaxiong Song
,
[28 Nov 2022 9:53] Huaxiong Song
It is a feature request.
[7 Dec 2022 11:17] MySQL Verification Team
Hello Huaxiong Song,

Thank you for the feature request and supplying contribution.
May I request you to  please re-send the patch via "contribution" tab?  Otherwise we would not be able to accept it. Thank you.

regards,
Umesh
[7 Dec 2022 14:56] Huaxiong Song
Optimize NET::buff of THD

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: NET_buff_shrink.diff (application/octet-stream, text), 17.09 KiB.

[8 Dec 2022 6:49] MySQL Verification Team
Thank you for re-uploading.

regards,
Umesh