Bug #100624 Total memory allocation for P_S is different in different sources

• Submitted: 24 Aug 2020 15:05
• Modified: 25 Aug 2020 5:48
• Reporter: Valeriy Kravchuk
• Status: Verified
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• Version: 5.7.30, 8.0.21, 5.7.31
• OS: Any
• CPU Architecture: Any

[24 Aug 2020 15:05] Valeriy Kravchuk

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.

[25 Aug 2020 5:48] MySQL Verification Team

Hello Valeriy,

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

regards,
Umesh

[25 Aug 2020 5:49] MySQL Verification Team

- 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