1

We are observing high latency for some queries, digging further we found that every 5 minutes the queries take long time by examining the slow query logs.

We observed that

PURGE BINARY LOGS TO 'mysql-bin-changelog.041045';

Running every 5 mins and taking more than 1000ms. At the same time queries like

SET timestamp=1492673425; commit;

Also taking time and

SELECT @@session.tx_isolation;

We are using MySQL 5.7.11 on Amazon RDS. Any reason why PURGE BINARY LOGS will take longer and causing the other queries to also stuck at the same time?

0
1

This is a rather nasty bug in MySQL 5.7 that I have experienced outside of RDS 2 months back. There is a strange mutexing behavior involving the act of purging binary logs.

My workaround involved manually deleting some of the binary logs in the OS as listed in bin-log.index and executing PURGE BINARY LOGS BEFORE CURDATE() + INTERVAL 2 HOUR; (Nightly purge every 2AM). It sped up it up some because it had less files in the OS to examine. When it came to the actual binlogs still in existence, running PURGE BINARY LOGS ... simply dragged. That's why I did manual smoke-and-mirrors stuff.

YOUR ACTUAL SITUATION

In your particular instance, you are using RDS. Since you cannot go to the OS, there is nothing else you can do to the binlogs because max_binlog_size cannot be changed in an RDS Parameter Group. You could change the max_binlog_cache_size if you have large or many transactions that need to cached ahead of its writing to the binlogs, but this may or may not help depending on how frequent you write in your app.

RECOMMENDATION

This issue does not exist in MySQL 5.6.

You may have to migrate your DB back to MySQL 5.6.27 RDS.

EPILOGUE

From my experience, I was victimized by this bug in MySQL 5.7.12. You informed me that the bug was addressed in MySQL 5.7.14. I wish I had upgraded like I wanted. Since your situation came up MySQL 5.7.11, I knew someone besides me noticed this problem. Go for your upgrade to 5.7.16 ASAP. Have a good day !!!

1
0

If you can change the configuration, I suggest

  • Lower max_binlog_size
  • Set expire_logs_days to a suitable value -- instead of manually using PURGE BINARY LOGS.

If the PURGE is coming from some maintenance script from RDS, then file a bug report with Amazon.

3
  • Actually we are observing high CPU spikes from 25% -> 50% - 60% and in slow query logs we don't have any application query, rather just the push binary logs and variable selection. Do you think high latency of purging cna be a cause?
    – Ahmed
    Apr 20 '17 at 17:35
  • Hmmm... I would expect the purge to be mostly I/O-bound -- as the OS works on deleting lots of blocks of the log file(s).
    – Rick James
    Apr 20 '17 at 22:17
  • I like 14 (days) for expire_log_days, but then I am referring to self-hosted MySQL servers, not Cloud-hosted. Probably Amazon knows best, but it is worth bugging them (if you have a service contract).
    – Rick James
    Apr 20 '17 at 22:19

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.