Bug #101716 P_S memory_summary_by_account_by_event_name table showing incorrect memory stats
Submitted: 23 Nov 2020 9:30 Modified: 29 Dec 2020 14:10
Reporter: lalit Choudhary Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.22,5.7.32 OS:Any
Assigned to: CPU Architecture:Any
Tags: Memory, performance_schema

[23 Nov 2020 9:30] lalit Choudhary
Description:
Performance_schema.memory_summary_by_account_by_event_name table shoing incorrect "current_number_of_bytes_used" value for events.

for example:
started mysql on OS with 1G ram, but as per PS.memory_summary_by_account_by_event_name event/s current_number_of_bytes_used above 1GB.

mysql> select user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED from memory_summary_by_account_by_event_name where host="localhost" order by current_number_of_bytes_used desc limit 10;
+-----------------+---------------------------------------+-------------------+
| user            | event_name                            | MB_CURRENTLY_USED |
+-----------------+---------------------------------------+-------------------+
| root            | memory/sql/String::value              |     1424.86182404 |
| root            | memory/innodb/memory                  |        1.77591705 |
| root            | memory/innodb/ha_innodb               |        0.41287231 |
| root            | memory/sql/thd::main_mem_root         |        0.06154633 |
| root            | memory/innodb/row0sel                 |        0.02423859 |
| root            | memory/sql/NET::buff                  |        0.01563168 |
| event_scheduler | memory/sql/NET::buff                  |        0.01563168 |
| root            | memory/innodb/rem0rec                 |        0.00979042 |
| root            | memory/sql/Filesort_buffer::sort_keys |        0.00960922 |
| root            | memory/vio/vio                        |        0.00102234 |
+-----------------+---------------------------------------+-------------------+
10 rows in set (0.00 sec)

mysql> select user,event_name,current_number_of_bytes_used from memory_summary_by_account_by_event_name where host="localhost" order by current_number_of_bytes_used desc limit 10;
+-----------------+---------------------------------------+------------------------------+
| user            | event_name                            | current_number_of_bytes_used |
+-----------------+---------------------------------------+------------------------------+
| root            | memory/sql/String::value              |                   1494075912 |
| root            | memory/innodb/memory                  |                      1862184 |
| root            | memory/innodb/ha_innodb               |                       432928 |
| root            | memory/sql/thd::main_mem_root         |                        57376 |
| root            | memory/innodb/row0sel                 |                        25416 |
| root            | memory/sql/NET::buff                  |                        16391 |
| event_scheduler | memory/sql/NET::buff                  |                        16391 |
| root            | memory/innodb/rem0rec                 |                        10266 |
| root            | memory/sql/Filesort_buffer::sort_keys |                        10076 |
| root            | memory/vio/vio                        |                         1072 |
+-----------------+---------------------------------------+------------------------------+
10 rows in set (0.01 sec)

memory/sql/String::value  is just an example in this case but looks other event_types also affected and current_number_of_bytes_used is not calculated for them as well.

example: mysql instance monitor by Percona pmm and the pmm user memory stats on OS with 1GB memory,

+------+--------------------------------------+------------------------------+
| user | event_name                           | current_number_of_bytes_used |
+------+--------------------------------------+------------------------------+
| pmm  | memory/sql/String::value             |                    679921160 |
| pmm  | memory/sql/thd::main_mem_root        |                    324465648 |
| pmm  | memory/innodb/memory                 |                      1696184 |
| root | memory/sql/String::value             |                      1145144 |

So looks like PS.memory_summary_by_account_by_event_name has some issues while calculating current memory for the user. 

How to repeat:
MySQL 8.0.22

-start mysql with the default setting.
- enable memory instruments 

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
WHERE NAME LIKE 'memory/%';

Running some selects on I_S and P_S databases in loop.
$ for i in {1..1000000} ;do ./use < IS_PS_select.sql > /dev/null; done

[root@testos ~]# free -h
              total        used        free      shared  buff/cache   available
Mem:           1.0G        634M         82M        2.3M        297M        235M

mysql> select user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED from memory_summary_by_account_by_event_name where host="localhost" order by current_number_of_bytes_used desc limit 10;
+-----------------+---------------------------------------+-------------------+
| user            | event_name                            | MB_CURRENTLY_USED |
+-----------------+---------------------------------------+-------------------+
| root            | memory/sql/String::value              |     1424.86182404 |
| root            | memory/innodb/memory                  |        1.77591705 |
| root            | memory/innodb/ha_innodb               |        0.41287231 |
| root            | memory/sql/thd::main_mem_root         |        0.06154633 |
| root            | memory/innodb/row0sel                 |        0.02423859 |
| root            | memory/sql/NET::buff                  |        0.01563168 |
| event_scheduler | memory/sql/NET::buff                  |        0.01563168 |
| root            | memory/innodb/rem0rec                 |        0.00979042 |
| root            | memory/sql/Filesort_buffer::sort_keys |        0.00960922 |
| root            | memory/vio/vio                        |        0.00102234 |
+-----------------+---------------------------------------+-------------------+
10 rows in set (0.00 sec)

mysql> select user,event_name,current_number_of_bytes_used from memory_summary_by_account_by_event_name where host="localhost" order by current_number_of_bytes_used desc limit 10;
+-----------------+---------------------------------------+------------------------------+
| user            | event_name                            | current_number_of_bytes_used |
+-----------------+---------------------------------------+------------------------------+
| root            | memory/sql/String::value              |                   1494075912 |
| root            | memory/innodb/memory                  |                      1862184 |
| root            | memory/innodb/ha_innodb               |                       432928 |
| root            | memory/sql/thd::main_mem_root         |                        57376 |
| root            | memory/innodb/row0sel                 |                        25416 |
| root            | memory/sql/NET::buff                  |                        16391 |
| event_scheduler | memory/sql/NET::buff                  |                        16391 |
| root            | memory/innodb/rem0rec                 |                        10266 |
| root            | memory/sql/Filesort_buffer::sort_keys |                        10076 |
| root            | memory/vio/vio                        |                         1072 |
+-----------------+---------------------------------------+------------------------------+
10 rows in set (0.01 sec)

Suggested fix:
Current memory use in PS.memory_summary_by_account_by_event_name should not be greater than total OS memory and it should calculate correctly.
[23 Nov 2020 9:31] lalit Choudhary
test_sql_file

Attachment: IS_PS_select.sql (application/sql, text), 4.27 KiB.

[23 Nov 2020 11:12] lalit Choudhary
same for the 5.7.32 version.

mysql> select user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED from memory_summary_by_account_by_event_name where host="localhost" order by current_number_of_bytes_used desc limit 10;
+------+-----------------------------------+-------------------+
| user | event_name                        | MB_CURRENTLY_USED |
+------+-----------------------------------+-------------------+
| root | memory/sql/String::value          |     1052.91786957 |
| root | memory/sql/sp_head::main_mem_root |        1.35673523 |
| root | memory/innodb/mem0mem             |        0.99141407 |
| root | memory/myisam/MI_INFO             |        0.64611816 |
| root | memory/mysys/lf_node              |        0.08102417 |
| root | memory/myisam/record_buffer       |        0.06990051 |
| root | memory/mysys/IO_CACHE             |        0.06250763 |
| root | memory/csv/Transparent_file       |        0.04687500 |
| root | memory/sql/thd::main_mem_root     |        0.03909302 |
| root | memory/mysys/lf_dynarray          |        0.02156067 |
+------+-----------------------------------+-------------------+
10 rows in set (0.00 sec)

mysql> exit
Bye
[root@testos ~]# free -h
              total        used        free      shared  buff/cache   available
Mem:           1.0G        512M        152M        3.2M        350M        358M
[23 Nov 2020 12:51] MySQL Verification Team
Hello Lalit,

Thank you for the report and feedback!

Thanks,
Umesh
[29 Dec 2020 14:10] lalit Choudhary
Also, checked there is no utilization of virtual memory at this time by mysqld.

mysql> select user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED from memory_summary_by_account_by_event_name where host="localhost" order
by current_number_of_bytes_used desc limit 10;
+------+-----------------------------------+-------------------+
| user | event_name                        | MB_CURRENTLY_USED |
+------+-----------------------------------+-------------------+
| root | memory/sql/String::value          |      939.01168823 |
| root | memory/sql/sp_head::main_mem_root |        2.03739929 |
| root | memory/sql/get_all_tables         |        1.61967468 |
| root | memory/innodb/mem0mem             |        1.04492760 |
| root | memory/myisam/MI_INFO             |        0.77722168 |
| root | memory/sql/JOIN_CACHE             |        0.25000000 |
| root | memory/myisam/record_buffer       |        0.08270264 |
| root | memory/mysys/lf_node              |        0.08004761 |
| root | memory/sql/thd::main_mem_root     |        0.07012939 |
| root | memory/mysys/IO_CACHE             |        0.06250763 |
+------+-----------------------------------+-------------------+
10 rows in set (0.00 sec)

[root@testos ~]# free -h
              total        used        free      shared  buff/cache   available
Mem:           1.0G        617M         99M        6.9M        297M        250M
Swap:          3.0G          0B        3.0G

[root@testos ~]# ps eo user,pid,vsz,rss $(pgrep -f 'mysqld')
USER       PID    VSZ   RSS
mysql     1152 705320 263852

[root@testos ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  0      0 102312   2132 302856    0    0   194   228 1736 2693 46 11 36  7  0