Hope you're doing well.
We've recently moved to Mysql 8 (from Mysql 5.7).
We're using AWS RDS Service with MySql. The AWS Team is also looking for a solution btw.
Since a few days, we're facing several problem with replication. We never met these problems before with the 5.7 version.
First, we had a very import lag among our Master and our two replicas.
This was solved with specifics params on the Parameters group of the replica like :
- slave_parallel_type in LOGICAL_CLOCK mode
- binlog_group_commit_sync_delay with a 10ms delay
- sync_binlog at 0
It seems that the latency is now gone, and it's a good news (i'll wait several days to be sure).
Nevertheless, we're still facing a massive problem with the RAM used on the Replica.
We can't find the problem, perhaps the buffer, but it must be locked with a threshold.
It's a cycle movement like this :
In Green, the production.
In Orange the small replica.
In Blue the most powerfull replica (As you can see, we tried to upgrade the instance, but it's not a RAM limitation problem).
The problem is the same for both. The memory usage is increasing till the replica has to down.
By the way, the swap is never used.
If you have any clue with this, it will help me a lot!
Thanks for reading!
Have a nice day :)
--- UPDATE ---
It seems that the buffer is full, but I don't know with what. Is there any garbage collector running ? Because seems not, perhaps something to turn on with Mysql8?
Here some queries showing the size of the allocated memory and a zoom on the innodb memory repartition. We see that I Have 12go on this replica. And they must be full if the memory is decreasing :
SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, FORMAT_BYTES(SUM(current_alloc)) AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;
SELECT * FROM sys.x$memory_global_by_current_bytes WHERE SUBSTRING_INDEX(event_name,'/',2) = "memory/innodb" ORDER BY current_alloc DESC LIMIT 10;