MySQL Bugs: #105969: The value in the column current_memory of table sys.host

Bug #105969	The value in the column current_memory of table sys.host_summary is not accurate
Submitted:	24 Dec 2021 7:20	Modified:	25 Dec 2021 7:37
Reporter:	fander chan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: SYS Schema	Severity:	S3 (Non-critical)
Version:	5.7.36	OS:	CentOS (7.5)
Assigned to:		CPU Architecture:	x86
Tags:	sys

Description:
The value in the column current_memory of table sys.host_summary is not accurate.

This value means "the current amount of allocated memory for the user" according to the official document:
https://dev.mysql.com/doc/refman/8.0/en/sys-user-summary.html

But I found that it is not accurate, it will become larger and larger, and can increase infinitely.

How to repeat:
linux 192.168.199.131 console 1:
for i in `seq 1 1000000`; do mysql -uroot -proot -h192.168.199.198 -P3307 -e "select * from  sys.host_summary" >/dev/null 2>&1;done

linux 192.168.199.198 console 2:
for i in `seq 1 1000000`; do mysql -root -proot -h192.168.199.198 -P3307 -e "select 1;">/dev/null 2>&1;done

linux 192.168.199.198 console 3:
for i in `seq 1 1000000`; do mysql -root -proot -S /tmp/mysql3307.sock -e "select 1;">/dev/null 2>&1;done

then,Let's watch the results

mysql> select * from  sys.host_summary;
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host            | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| 192.168.199.131 |    2137678 | 6.17 m            | 173.26 us             |       19446 |   116739 | 328.30 ms       |                   0 |             40010 |            1 | 627.58 MiB     | 76.87 GiB              |
| 192.168.199.198 |     119018 | 4.95 s            | 41.61 us              |           0 |        0 | 0 ps            |                   0 |             59509 |            1 | 931.66 MiB     | 1.83 GiB               |
| localhost       |    1195128 | 2.72 m            | 136.68 us             |       19126 |   113732 | 376.05 ms       |                   1 |            170927 |            1 | 2.61 GiB       | 70.41 GiB              |
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
3 rows in set (0.02 sec)

mysql> select * from  sys.host_summary;
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host            | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| 192.168.199.131 |    2629270 | 7.70 m            | 175.67 us             |       24059 |   144417 | 405.18 ms       |                   0 |             44623 |            1 | 698.68 MiB     | 94.92 GiB              |
| 192.168.199.198 |     140004 | 5.83 s            | 41.65 us              |           0 |        0 | 0 ps            |                   0 |             70001 |            1 | 1.07 GiB       | 2.15 GiB               |
| localhost       |    1247460 | 2.75 m            | 132.39 us             |       19127 |   113732 | 376.05 ms       |                   1 |            188336 |            1 | 2.88 GiB       | 70.95 GiB              |
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
3 rows in set (0.02 sec)

mysql> mysql> select * from  sys.host_summary;
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host            | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| 192.168.199.131 |   27630969 | 1.09 h            | 142.20 us             |      264146 |  1584939 | 4.37 s          |                   1 |            284710 |            1 | 4.35 GiB       | 1.01 TiB               |
| 192.168.199.198 |    1069176 | 44.91 s           | 42.00 us              |           0 |        0 | 0 ps            |                   0 |            534587 |            1 | 8.17 GiB       | 16.40 GiB              |
| localhost       |    3743129 | 4.13 m            | 66.18 us              |       19128 |   113732 | 376.05 ms       |                   1 |           1020192 |            1 | 15.58 GiB      | 96.47 GiB              |
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
3 rows in set (0.12 sec)

mysql> select * from  sys.host_summary;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3086195
Current database: *** NONE ***

+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host            | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| 192.168.199.131 |  103061584 | 3.81 h            | 133.06 us             |     1010006 |  6060099 | 17.09 s         |                   0 |           1030570 |            1 | 15.76 GiB      | 3.83 TiB               |
| 192.168.199.198 |    2020276 | 1.41 m            | 42.01 us              |           0 |        0 | 0 ps            |                   0 |           1010138 |            1 | 15.44 GiB      | 30.98 GiB              |
| localhost       |    3819093 | 4.17 m            | 65.57 us              |       19129 |   113738 | 376.09 ms       |                   1 |           1045481 |            1 | 15.97 GiB      | 97.25 GiB              |
+-----------------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
3 rows in set (0.02 sec)

begin:
              total        used        free	 shared  buff/cache   available
Mem:          15866        3180       10291         788        2395	  11566
Swap:             0           0           0

end:
Every 2.0s: free -m                                                                                                                                                                                                                                  Fri Dec 24 12:21:44 2021

              total        used        free	 shared  buff/cache   available
Mem:          15866        3309        9969         836        2586	  11382
Swap:             0           0           0

My connection is a short connection. When I finish my query, I release memory. In fact, my memory does not grow. So I think "The value in the column current_memory of table sys.host_summary is not accurate."

Suggested fix:
Make the value in the column current_memory of table sys.host_summary correct.

Hello fander chan,

Thank you for the report and test case.
I tried quickly using 5.7.36 build but not seeing any issues with the exact steps provided in the report. Could you please check on latest GA and confirm at your end? Thank you.

regards,
Umesh

[root@fander ~]# mysql -uroot -proot -S /tmp/mysql3307.sock -e "select @@version"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@version  |
+------------+
| 5.7.36-log |
+------------+
[root@fander ~]# for i in `seq 1 10000`; do mysql -uroot -proot -S /tmp/mysql3307.sock -e "select * from  sys.host_summary;" ;done
mysql> select * from  sys.host_summary;
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host      | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| localhost |          1 | 252.77 us         | 252.77 us             |           0 |       58 | 194.90 us       |                   1 |                 1 |            1 | 1.93 MiB       | 2.17 MiB               |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
1 row in set (0.01 sec)

...

mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host      | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| localhost |      29574 | 6.09 s            | 206.06 us             |         767 |     4657 | 14.94 ms        |                   2 |               766 |            1 | 14.34 MiB      | 2.64 GiB               |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host      | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| localhost |      29612 | 6.10 s            | 206.11 us             |         768 |     4663 | 14.96 ms        |                   2 |               767 |            1 | 14.35 MiB      | 2.64 GiB               |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host      | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| localhost |      29650 | 6.11 s            | 206.15 us             |         769 |     4669 | 14.98 ms        |                   2 |               768 |            1 | 14.37 MiB      | 2.65 GiB               |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| host      | statements | statement_latency | statement_avg_latency | table_scans | file_ios | file_io_latency | current_connections | total_connections | unique_users | current_memory | total_memory_allocated |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
| localhost |      29688 | 6.12 s            | 206.17 us             |         770 |     4675 | 15.04 ms        |                   2 |               769 |            1 | 14.39 MiB      | 2.65 GiB               |
+-----------+------------+-------------------+-----------------------+-------------+----------+-----------------+---------------------+-------------------+--------------+----------------+------------------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
^C

Here is my my.cnf

Attachment: my.cnf (application/octet-stream, text), 8.37 KiB.

Hello fander chan,

Thank you for the details, verified as described.

regards,
Umesh