I've been using RDS for years, and this only became an issue a few months ago. I was pulling my hair out trying to find the source of the issue... only to figure out it's suddenly RDS.

I've made no configuration changes or anything, but now I can see that when RDS does a backup of my MySQL 5.6.34 database, it is kicking off all the clients and doesn't accept connections and queries until it finishes.

https://forum.ghost.org/t/back-with-the-ghost-sites-failing-to-load-following-an-aws-rds-maintenance-backup-window/1983/3 I wound up finding a similar problem that someone is experiencing here, but with no solution to it figured out.

What changed and how do I resolve this?

  • RDS doesn't lock out connections, deny queries, or do anything unusual during backups, other than an I/O freeze and locks on tables in the mysql schema itself that are typically so brief that they are difficult to even observe happen... or, at least, it shouldn't. Set log_warnings in the parameter group to "2" and see what appears in the error log before/during/after these events. Consider also connecting manually before a backup and capturing the output of the query SHOW PROCESSLIST; before, during, and after. – Michael - sqlbot Sep 28 '19 at 2:49
  • Ah I did have a log, though this was before setting log_warnings=2. pastebin.com/H34FBmPi Nothing in it stands out to be other than the long semaphore waits. It says that it crashes when shutting down, but I'm not seeing a reason why unless it'll come up with the changed warning level. But regardless of it crashing, I'm not seeing anything to indicate why some clients aren't reconnecting when it finishes crashing and restarting, other than connection timing out on the clients' end. – sadtaco Oct 2 '19 at 14:02
  • Okay, that log file tells an interesting story. InnoDB: Warning: a long semaphore wait is a warning that InnoDB waited an entirely unreasonable amount of time for a semaphore, which in physical servers often points to a hard drive on the brink of failure that is stalling on reads and/or writes but not throwing errors (or not throwing them yet), or a degraded RAID array... but RDS runs on EBS, making that a less likely possibility (or so it would seem). – Michael - sqlbot Oct 2 '19 at 17:50
  • Is this a t2 RDS instance? If so, you should start with your CPUCreditBalance metric -- depleting that balance slows the CPU to a crawl, but RDS backups are volume snapshots, occurring outside the instance, so they don't consume CPU credits. Still, that's my next question. Is it a t2, and, if so, how does that metric look? – Michael - sqlbot Oct 2 '19 at 17:52
  • The CPU has always been under 5%, usually around 1%. Anyway, it seems to have magically fixed itself about a week and a half ago without me changing anything. I'm guessing it was some disk issue at AWS. – sadtaco Nov 17 '19 at 20:25

Your Answer

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

Browse other questions tagged or ask your own question.