0

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 :

High Memory usage on replica versus Production

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;

Result of First Query

   SELECT *
   FROM sys.x$memory_global_by_current_bytes
 WHERE SUBSTRING_INDEX(event_name,'/',2) = "memory/innodb"
   ORDER BY current_alloc DESC
 LIMIT 10;

Result of Second Query

2
  • What values do you have for these (on each server): SHOW VARIABLES LIKE 'innodb_buffer_pool_load_at_startup'; and SHOW VARIABLES LIKE 'innodb_buffer_pool_dump%'' ?
    – Rick James
    Jun 9 at 15:53
  • Hello Rick, Thanks for answering. On the main server for your first query the Value is "ON". For the second query the values are : innodb_buffer_pool_dump_at_shutdown ON innodb_buffer_pool_dump_now OFF innodb_buffer_pool_dump_pct 25 Exact same values for the Replica Server.
    – NicolasCab
    Jun 10 at 16:13
0
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?

2
  • Hello Rick, Thanks for the Tips! For the data, i've there about 200GO of data in this database, what do you think?
    – NicolasCab
    Jun 14 at 8:15
  • 200G is relatively large. I assume (without any proof) that the "pct" gives preference to non-leaf nodes of the various BTrees. This would give a good tradeoff.
    – Rick James
    Jun 15 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.