High Memory Usage on Replica - Mysql 8

Question

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;

Answer 1

innodb_buffer_pool_load_at_startup = ON

Tells the server to reload the buffer_pool at startup. This causes a lot of I/O in a short period of time.

Its goal is to make the subsequent queries a little faster, but that may or may not be worth it.

If this is a cloud service, they probably do some regular maintenance, perhaps a backup, at the about the same time every day.

As a compromise, drop this to 10 (percent):

innodb_buffer_pool_dump_pct

That will cut back on the I/O while still giving some of the pre-loading benefits.

What is the size of all the data? Perhaps the buffer_pool is unnecessarily large?