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)

| improve this question | | | | |

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.

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 "rdsadmin" is doing this.

I selected a binlog at random to check 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 flushing the binlog, then immediately writing a new value to this table. Reading the value from this table on the slaves would give the RDS supervisory systems a mechanism for determining/monitoring the slave'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 slave would not be caught without the necessary logs being available.

Instead of letting MySQL age out it binlogs on its own based on the global varible expire_logs_days, RDS seems to be managing this process externally, and quite possibly this 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.

| improve this answer | | | | |

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).

| improve this answer | | | | |
  • That it true, but seems unrelated to the question, which is about the master holding onto its old binlogs. – Michael - sqlbot Feb 22 '14 at 0:25

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.

| improve this answer | | | | |

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.