Bug #100962 memory_summary_%_by_event_name Have different values
Submitted: 26 Sep 2020 5:17 Modified: 26 Oct 2020 11:42
Reporter: hongyu dong (OCA) Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[26 Sep 2020 5:17] hongyu dong
Description:
When using performance schema to troubleshoot memory issues, I found memory_summary_%_by_event_name Have different values:

memory_summary_by_user_by_event_name:
MySQL>select user,event_name,sum(SUM_NUMBER_OF_BYTES_ALLOC)/1024/1024, sum(SUM_NUMBER_OF_BYTES_FREE)/1024/1024, sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 from memory_summary_by_user_by_event_name where event_name = 'memory/sql/String::value' group by user,event_name order by sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 desc limit 10;
+------+--------------------------+------------------------------------------+-----------------------------------------+---------------------------------------------+
| user | event_name               | sum(SUM_NUMBER_OF_BYTES_ALLOC)/1024/1024 | sum(SUM_NUMBER_OF_BYTES_FREE)/1024/1024 | sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 |
+------+--------------------------+------------------------------------------+-----------------------------------------+---------------------------------------------+
| root | memory/sql/String::value |                              23.48831177 |                             11.32242584 |                                 12.16588593 |
| NULL | memory/sql/String::value |                               0.00000000 |                              0.00000000 |                                  0.00000000 |
+------+--------------------------+------------------------------------------+-----------------------------------------+---------------------------------------------+

memory_summary_global_by_event_name:
MySQL>select SUBSTRING_INDEX(event_name,'/',3) event_name,sum(SUM_NUMBER_OF_BYTES_ALLOC)/1024/1024, sum(SUM_NUMBER_OF_BYTES_FREE)/1024/1024,  sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 from memory_summary_global_by_event_name where event_name = 'memory/sql/String::value' group by SUBSTRING_INDEX(event_name,'/',3) order by sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 desc limit 10;
+--------------------------+------------------------------------------+-----------------------------------------+---------------------------------------------+
| event_name               | sum(SUM_NUMBER_OF_BYTES_ALLOC)/1024/1024 | sum(SUM_NUMBER_OF_BYTES_FREE)/1024/1024 | sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 |
+--------------------------+------------------------------------------+-----------------------------------------+---------------------------------------------+
| memory/sql/String::value |                              23.48936462 |                             23.48928833 |                                  0.00007629 |
+--------------------------+------------------------------------------+-----------------------------------------+---------------------------------------------+
1 row in set (0.01 sec)

"SUM_NUMBER_OF_BYTES_FREE" is quite different

How to repeat:
for i in $(seq 1 100)
do
mysql -u root -P 3320 -h 127.0.0.1 -pxxxx performance_schema -e "show global status\G"
done

MySQL>select user,event_name,sum(SUM_NUMBER_OF_BYTES_ALLOC)/1024/1024, sum(SUM_NUMBER_OF_BYTES_FREE)/1024/1024, sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 from memory_summary_by_user_by_event_name where event_name = 'memory/sql/String::value' group by user,event_name order by sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 desc limit 10;

MySQL>select SUBSTRING_INDEX(event_name,'/',3) event_name,sum(SUM_NUMBER_OF_BYTES_ALLOC)/1024/1024, sum(SUM_NUMBER_OF_BYTES_FREE)/1024/1024,  sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 from memory_summary_global_by_event_name where event_name = 'memory/sql/String::value' group by SUBSTRING_INDEX(event_name,'/',3) order by sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 desc limit 10;
[28 Sep 2020 14:33] MySQL Verification Team
Hi Mr. dong,

Thank you for your bug report.

However, this is not a bug.

It is sufficient to run a single query, like your first query, to see a change in that value, usually quite small.

Not a bug.
[29 Sep 2020 11:50] hongyu dong
Thanks for your respond , My description is not clear。Let me describe :

mysql> select user,event_name,sum(SUM_NUMBER_OF_BYTES_ALLOC), sum(SUM_NUMBER_OF_BYTES_FREE), sum(CURRENT_NUMBER_OF_BYTES_USED) from memory_summary_by_user_by_event_name where event_name = 'memory/sql/String::value' group by user,event_name order by sum(CURRENT_NUMBER_OF_BYTES_USED) desc limit 10;
+------+--------------------------+--------------------------------+-------------------------------+-----------------------------------+
| user | event_name               | sum(SUM_NUMBER_OF_BYTES_ALLOC) | sum(SUM_NUMBER_OF_BYTES_FREE) | sum(CURRENT_NUMBER_OF_BYTES_USED) |
+------+--------------------------+--------------------------------+-------------------------------+-----------------------------------+
| root | memory/sql/String::value |                       22960000 |                      11474400 |                          11485600 |
| NULL | memory/sql/String::value |                              0 |                             0 |                                 0 |
+------+--------------------------+--------------------------------+-------------------------------+-----------------------------------+
2 rows in set (0.00 sec)

mysql> select SUBSTRING_INDEX(event_name,'/',3) event_name,sum(SUM_NUMBER_OF_BYTES_ALLOC), sum(SUM_NUMBER_OF_BYTES_FREE),  sum(CURRENT_NUMBER_OF_BYTES_USED) from memory_summary_global_by_event_name where event_name = 'memory/sql/String::value' group by SUBSTRING_INDEX(event_name,'/',3) order by sum(CURRENT_NUMBER_OF_BYTES_USED) desc limit 10;
+--------------------------+--------------------------------+-------------------------------+-----------------------------------+
| event_name               | sum(SUM_NUMBER_OF_BYTES_ALLOC) | sum(SUM_NUMBER_OF_BYTES_FREE) | sum(CURRENT_NUMBER_OF_BYTES_USED) |
+--------------------------+--------------------------------+-------------------------------+-----------------------------------+
| memory/sql/String::value |                       22960000 |                      22960000 |                                 0 |
+--------------------------+--------------------------------+-------------------------------+-----------------------------------+
1 row in set (0.00 sec)

 
The "SUM_NUMBER_OF_BYTES_FREE" in the two tables is very different. In other words, you can get "memory/sql/String::value" from the user table. This event uses very high memory (CURRENT_NUMBER_OF_BYTES_USED), but you can see from the global table This event uses relatively low memory (CURRENT_NUMBER_OF_BYTES_USED). In the above example, they are: 11485600 AND 0 .
I think the reason is that the "SUM_NUMBER_OF_BYTES_FREE" in the two tables is very different 
[29 Sep 2020 13:43] MySQL Verification Team
Again, not a bug.

This allocation will be reused. There are many items that are cached within a connection.
[6 Oct 2020 7:11] Jason Han
OK.If it's not a bug, could you please tell us why the same event_name that named memory/sql/String::value of the metric about the sum of CURRENT_NUMBER_OF_BYTES_USED is different between table memory_summary_by_user_by_event_name and table memory_summary_global_by_event_name?
Looking forward for your answer!
Thank you.
[6 Oct 2020 12:19] MySQL Verification Team
The explanation is extremely simple.

There is memory allocated that is not tied to any particular user.
[7 Oct 2020 11:44] Jason Han
If table memory_summary_global_by_event_name contains allocated memory that is not tied to any particular user, so the column SUM_NUMBER_OF_BYTES_ALLOC of table memory_summary_global_by_event_name should be greater than the column SUM_NUMBER_OF_BYTES_ALLOC of table memory_summary_by_user_by_event_name, as well as the column CURRENT_NUMBER_OF_BYTES_USED.
[7 Oct 2020 12:12] MySQL Verification Team
Actually, no, not necessarily.

Memory allocation, owners and properties are much more complex than what is presumed.

Some of it is described in our Internals Manual, found on dev.mysql.com.
[16 Oct 2020 11:57] Sveta Smirnova
Hi MySQL Verification Team,

could you please check one more time?

In memory_summary_by_user_by_event_name: sum(SUM_NUMBER_OF_BYTES_FREE) + sum(CURRENT_NUMBER_OF_BYTES_USED) = 11.32242584 + 12.16588593 = 23.48831177

This is slightly less than sum(SUM_NUMBER_OF_BYTES_FREE)/1024/1024 in memory_summary_global_by_event_name

And value of sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 in memory_summary_global_by_event_name is about 0.

If it would be opposite: sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024 in memory_summary_global_by_event_name is about same as number of used and free bytes in memory_summary_by_user_by_event_name I would agree with your reasoning, but for this example it looks like memory_summary_global_by_event_name puts USED bytes into the FREE list.
[16 Oct 2020 11:57] Justin Swanhart
It seems to me summary and detail do not match, and that it is not using the _user_ tables and thus the events should indeed match and that it is in fact a bug or should be instrumented different to make this distinction clear.
[16 Oct 2020 11:58] Justin Swanhart
Oops.  My mistake.  Misread the queries.  Ignore that.
[16 Oct 2020 12:00] MySQL Verification Team
Thank you, Justin.
[19 Oct 2020 9:00] Marc ALFF
I have to disagree with the previous assessments here,
as this indeed looks like a bug, although not verified yet.

There are reasons why summaries by thread / account / users / hosts
might differ from global summaries, such as:

1)

Using TRUNCATE TABLE memory_summary_by_xxx_by_event_name.

This aggregates xxx statistics to the parent level,
but does not change the global statistics,
so that global statistics can report more activity compared to xxx statistics.
This will affect COUNT_ALLOC and COUNT_FREE, but not the balance COUNT_USED.

2)

Executing a performance schema SELECT from memory statistics table,
on a busy server.

Statistics are computed on the fly, and can either miss or count twice a session just connecting or disconnecting, reporting an incorrect -- but spurious -- result.

It seems none of these reasons explain the behavior seen here:
- reason 1 will show different ALLOC stats for global and user, not the case.
- reason 2 is spurious, and even when the bug report does not mention it, I would assume the query was tried several times before reporting a bug, so that the behavior seen is not spurious.

Now, to investigate properly this issue, several things are needed,
please see below.

A)

Please report the exact version of MySQL used.

B)

Please report the full row returned by performance schema tables.

In particular, seeing the data with both counts (COUNT columns) and sizes (SIZE columns) would help to understand if the issue is about missing an event entirely, or only about incorrect sizes.

C)

Please clarify if this issue is seen only on the "memory/sql/String::value" instrument, or if the same happens on other memory instruments.

Likewise, this can help to understand if the issue is about some specific instrumentation, or more general.

Changing bug status to Need Feedback, for points A) B) and C).
[19 Oct 2020 12:23] MySQL Verification Team
Hi,

Please, do note that without ALL of the info on A, B and C , we can not conclude whether it is a bug or not.

Needless to say, you should check your findings on the latest release of 5.7 !!!!! You will find them on the download pages of dev.mysql.com.
[20 Oct 2020 9:18] hongyu dong
ALL of the info on A, B and C

Attachment: Bug #100962 memory_summary_%_by_event_name Have different values (application/octet-stream, text), 9.47 KiB.

[20 Oct 2020 9:19] hongyu dong
hi, Thank you, everyone: 
File uploaded
[20 Oct 2020 12:39] MySQL Verification Team
Hi Mr. dong,

I do not think that your attachment contains all the data that our Development asked for.

Also, we missed the info whether you managed to reproduce the behaviour with the latest 5.7 release, which is 5.7.32.

We are waiting on your feedback.
[21 Oct 2020 12:58] Jason Han
Hi, mysql team. 
What is other data we must provide?
Can you spend some time to test this case yourself? 
Looking forward to make progress!
[25 Oct 2020 15:04] hongyu dong
Upload related information  

Attachment: Bug #100962 memory_summary_%_by_event_name Have different values (application/octet-stream, text), 27.67 KiB.