6

New to RDS (used to having my own server in my own data center).

We are on MySQL 5.6 on RDS.

when a slave gets behind in replication lag, the master ceases to rotate out its binary logs. It is, apparently, waiting to every slave to commit every transaction before rotating binary logs out. I don't have a problem with this conceptually (as long as you have monitors on disk space on the master, its a good thing).

My question is academic -- what causes this to happen?
** I know with ASYNCHRONOUS replication, the master is unaware of the slaves' status

is this a function of synchronous replication? semi-synchronous replication?

I'd love some insight and perhaps a pointer to a white paper somewhere (I can not find one that addresses this specific issue of binary logs not being rotated out)

3 Answers 3

7

This doesn't appear to be anything intrinsic to MySQL Server, so I can't give you a proper citation on what "causes" this to happen -- but I strongly suspect it is an element of the design of RDS.

Instead of letting MySQL age out it binlogs on its own based on the global variable expire_logs_days, RDS seems to be managing this process from outside the server, and quite possibly this same process is responsible for archiving the logs out of sight so that they can be used for the native RDS point-in-time restoration feature.

If you take a look at SHOW FULL PROCESSLIST; you'll notice there's always a user connected called "rdsadmin." If you look in the mysql schema, you'll spot a table called rds_heartbeat2, with a recent epoch time x 1000 stored in its single row. This is changed every few minutes, and my reasoned speculation is that that the "rdsadmin" user (created by RDS) is doing this.

I selected a binlog at random to test this theory, and here's the first event:

use `mysql`; INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1393029075007) 
ON DUPLICATE KEY UPDATE value = 1393029075007

I'll speculate, then, that the "rdsadmin" user is periodically flushing the binlog, then immediately writing a new value to this table. Reading the value from this table on the replicas would give the RDS supervisory systems a mechanism for determining/monitoring the replica's behavior, allowing it to purge binlogs that it knows have been fully processed or let them linger on the master if they haven't, so that a managed replica would not be caught without the necessary logs being available.


Although it wasn't really part of the original question... as noted in comments, readers may have arrived at this answer while trying to figure out how to prevent RDS from purging the binlogs quite so hastily. There's a special stored procedure in the mysql schema that can be used to set the retention time for the binlogs to a value between 1 and 168. The default behavior, when you don't set this, is to purge the logs almost immediately, as soon as none of the managed replicas need them.

mysql> CALL mysql.rds_set_configuration('binlog retention hours', 24);
3
  • Just noticed this when trying to setup replication FROM an RDS mysql to an on-premises Mysql. Something at RDS is deleting the binlogs from my server, even though expire_logs_days is 0... making it difficult to setup the replica, as the binlogs are deleted before the mysqldump and transfer is completed :/
    – BjornW
    May 7, 2023 at 22:04
  • If someone else comes here looking for the same issue, here's the magic how to fix it: CALL mysql.rds_set_configuration('binlog retention hours', 48);
    – BjornW
    May 7, 2023 at 23:35
  • 1
    @BjornW Thanks, updated. May 12, 2023 at 15:37
0

There are two threads on a slave: IO thread and SQL thread. The IO thread pulls binary log (events in the binary log to be precise) from the master and SQL thread executes them.

When people say a slave falls behind they mean implicitly the SQL thread falls behind, not IO thread. The IO thread is fast (faster than the SQL thread) because all it has to do is to read an event from the master and write it sequentially to a file (a relay log).

1
  • That it true, but seems unrelated to the question, which is about the master holding onto its old binlogs. Feb 22, 2014 at 0:25
0

By default the master holds all it's binary logs indefinitely. There is a setting to expire logs after N days (expire_logs_days) but sometimes this can be a little impractical, since in my opinion you really want to allocate based on storage capacity (GB; not time).

It sounds like as a result of this, Amazon has written their own retention mechanism as an alternative to expire_logs_days, but it doesn't always work as smart as it could. I am not sure you will be able to get an official documented answer. This is more in the domain of RDS internal management.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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