0

We have a replica read database for MySQL (5.6.41) on AWS RDS service, which worked OK for the last 2 years, but it suddenly started to behave very differently during last 3 weeks: it uses space and is not normally returning it back. So I had to buy more storage for it to continue operating (you can see 2 peaks in the screenshot). enter image description here As I see the problem is that some daemon is automatically calling PURGE BINARY LOGS BEFORE 'mysql-bin-changelog.10xxxx'; but that log "10xxxx" is not deleted and just stays there. I've checked INNODB MONITOR OUTPUT, there is NO long time active transactions, show processlist shows NOTHING, yet ~100% CPU is used enter image description here space is not reclaiming! SHOW BINARY LOGS; shows >5200 records and this number continues to grow.

I've tried to close all incoming connections, even turned off "event scheduler" and replication process. Still the picture is not healthy: enter image description here CPU is just stuck at >50%! And there are NO sessions in DB (just rdsadmin@localhost)

Can you help me with the cause and how to recover? Because for now I have to buy ~50GB per 3-4 days for "nothing".

For now ANSWER: we've contacted AWS Support (paid developer support), but they did not find anything special and said that the problem is on their side in hardware probably. So we had to create new replica and migrate our custom schemas to it, killed the problematic one after that.

0x49D1
  • 8,505
  • 11
  • 76
  • 127
  • This is unexpected behavior. Assuming you don't have a support agreement with AWS, please consider posting this to the official AWS forum and add a link here. – Michael - sqlbot Nov 05 '18 at 15:43
  • I've asked there too: https://forums.aws.amazon.com/thread.jspa?threadID=292808 , still no answer tho.. The case seems a bit unusual because even after turning off accesses and internal services - CPU is still used hardly by something and I can't diagnose easily the source. – 0x49D1 Nov 06 '18 at 07:29
  • Still no answer. I had private mailing with the support and they just suggested to stop the replica and create a new one :(. The problem in our case was that there were additional schemas for internal usage by reporting team, so that they had read access on some data, but also added some more procedures for their own purposes. Anyway after complete refreshing it seems that replica is doing OK. – 0x49D1 Apr 19 '19 at 06:53

1 Answers1

2

I will answer own question, because have not found any better solution, even after contacting and investigating with AWS Support.

We've contacted AWS Support (paid developer support), but they did not find anything special and said that the problem is on their side in hardware probably. So we had to create new replica and migrate our custom schemas to it, killed the problematic one after that.

0x49D1
  • 8,505
  • 11
  • 76
  • 127