| 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: | |
| 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: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
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.