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.