MySQL Bugs: #100624: Total memory allocation for P_S is different in different sources

Bug #100624	Total memory allocation for P_S is different in different sources
Submitted:	24 Aug 2020 15:05	Modified:	3 Feb 2021 17:46
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.7.30, 8.0.21, 5.7.31	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
There are two sources of information about memory usage for Performance Schema in MySQL 5.7+, the performance_schema.memory_summary_global_by_event_name table and the output of SHOW ENGINE PERFORMANCE_SCHEMA STATUS. The latter reports a bit smaller value, so probably some allocations are not taken into account there.

Such inconsistency is surely a bug.

How to repeat:
Start MySQL 5.7 as follows:

openxs@ao756:~/dbs/5.7$ bin/mysqld_safe --no-defaults --port=3310 --socket=/tmp/mysql57.sock --performance_schema=1 --performance-schema-instrument='memory/%=ON' &

for example. That is, with performance schema and memory instrumentation enabled. Now compare these outputs:

openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 137641608 |    0 | 137641608 |
+-----------+------+-----------+

and:

openxs@ao756:~/dbs/5.7$ bin/mysql -uroot --socket=/tmp/mysql57.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 137640120

The former (query to sum up all allocations for memory/performance_schema% events) reports 1488 bytes more than total performance_schema.memory reported by the latter.

Suggested fix:
Fix one of the sources (probably show engine performance_schema status) to have consistent outputs.

Hello Valeriy,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

- 5.7.31

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 137641608 |    0 | 137641608 |
+-----------+------+-----------+
bin/mysql -uroot -S /tmp/mysql_ushastry.sock -e"show engine performance_schema status\G" | tail -4
*************************** 229. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 137640120

- 8.0.21

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock -e"select sum(SUM_NUMBER_OF_BYTES_ALLOC) alloc, sum(SUM_NUMBER_OF_BYTES_FREE) free, sum(CURRENT_NUMBER_OF_BYTES_USED) used from performance_schema.memory_summary_global_by_event_name where event_name like 'memory/performance%'"
+-----------+------+-----------+
| alloc     | free | used      |
+-----------+------+-----------+
| 218186480 |    0 | 218186480 |
+-----------+------+-----------+
bin/mysql -uroot -S /tmp/mysql_ushastry.sock -e"show engine performance_schema status\G" | tail -4
*************************** 243. row ***************************
  Type: performance_schema
  Name: performance_schema.memory
Status: 188071536

Posted by developer:
 
Fixed in 8.0.24.

The SHOW ENGINE PERFORMANCE SCHEMA STATUS statement reported
incorrect memory usage for the Performance Schema.