MySQL Bugs: #105816: The memory data of the system table only increases during slave playback

Bug #105816	The memory data of the system table only increases during slave playback
Submitted:	7 Dec 2021 7:56	Modified:	14 Jan 2022 9:50
Reporter:	sheng wei (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	8.0.25,8.0.26	OS:	Red Hat
Assigned to:	MySQL Verification Team	CPU Architecture:	x86

Description:
In the process of master and slave playback, Use the command to view EVENT_NAME='memory/sql/slave_improve_memroot' in the 'memory_summary_by_thread_by_event_name' table. During the whole process, only memory was continuously applied for, and memory was not released.

Check the corresponding THREAD_ID in the sys table and find that the current_memory memory whose user is sql/slave_sql has exceeded 1T

Use 'top' to observe the memory usage of the mysqld process, and find that the memory is not leaked, which is within the normal range

There is a problem with the memory data statistics in this table, which can cause misunderstandings.

How to repeat:
Establish a normal master/slave relationship, use tpcc pressure test, use the command 'select * from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 5 \G;' to view memory usage

Hi,

I could not reproduce this with 8.0.26 but maybe the issue is with TPCC, I don't often use it. I assume you talk about http://www.tpc.org/tpcc/ - can you share your exact script to test with tpcc

thanks

I tested the mysql8026 version just now, and the same phenomenon occurs when the slave plays back normal sentences instead of using the tpcc tool. In the process of continuous playback on the slave, you can use the command: "select * from memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 4 \G;" to see the result of "CURRENT_NUMBER_OF_BYTES_USED" occupying memory. The result should be "EVENT_NAME:the memory of memory/sql/Log_event" only increases without decreasing, this is easy to reproduce.
Thanks

Hi,

How high do you see these values go, I see them rise for a while and then the number is capped and stopped increasing. This looks normal behavior to me.

all best

Hi，
I use the "select CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME='memory/sql/Log_event' order by CURRENT_NUMBER_OF_BYTES_USED  desc limit 1;" command to check. I see that the playback value of the slave exceeds 100g, and there has been no downward trend. The following is my parameter configuration:
(performance_schema = ON
max_binlog_size = 1G
binlog_format = ROW
log-slave-updates = 1
slave_parallel_workers = 128
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1
relay_log_recovery = 0
gtid_mode = on
enforce_gtid_consistency=on)

Kernel used:
	uname -a
	Linux localhost.localdomain 3.10.0-693.el7.x86_64 #1 SMP Thu Jul 6 19:56:57 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux
	
system:
	Red Hat Enterprise Linux Server release 7.4 (Maipo)
	
test tools:
	This is the case with  benchmarksql_5.0、sysbench0.5 and other tool tests 

all best

Hi,

I'm not reproducing this ?!

Just to be clear, you are running a test on the master and on the slave you are executing

select CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME='memory/sql/Log_event' order by CURRENT_NUMBER_OF_BYTES_USED  desc limit 1;

I cannot reproduce this... for e.g. during my test:

slave2 [localhost:21629] {msandbox} ((none)) > select CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME='memory/sql/Log_event' order by CURRENT_NUMBER_OF_BYTES_USED  desc limit 1;
+------------------------------+
| CURRENT_NUMBER_OF_BYTES_USED |
+------------------------------+
|                        12792 |
+------------------------------+
1 row in set (0.01 sec)

slave2 [localhost:21629] {msandbox} ((none)) > select CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME='memory/sql/Log_event' order by CURRENT_NUMBER_OF_BYTES_USED  desc limit 1;
+------------------------------+
| CURRENT_NUMBER_OF_BYTES_USED |
+------------------------------+
|                        12792 |
+------------------------------+
1 row in set (0.01 sec)

slave2 [localhost:21629] {msandbox} ((none)) > select CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME='memory/sql/Log_event' order by CURRENT_NUMBER_OF_BYTES_USED  desc limit 1;
+------------------------------+
| CURRENT_NUMBER_OF_BYTES_USED |
+------------------------------+
|                      2714516 |
+------------------------------+
1 row in set (0.01 sec)

slave2 [localhost:21629] {msandbox} ((none)) > select CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_by_thread_by_event_name where EVENT_NAME='memory/sql/Log_event' order by CURRENT_NUMBER_OF_BYTES_USED  desc limit 1;
+------------------------------+
| CURRENT_NUMBER_OF_BYTES_USED |
+------------------------------+
|                          570 |
+------------------------------+
1 row in set (0.00 sec)

slave2 [localhost:21629] {msandbox} ((none)) >

as you can see the value dropped after test was finished (actually value dropped during the test too and then went up and down and finally went back to starting value)

this was test with 8.0.26

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".