1

Two days ago our MySQL RDS instance saw a sudden spike in BinLogDiskUsage, CPUUtilization, NetworkReceiveThroughput and NetworkTransmitThroughput.

graphs of BinLogDiskUsage and CPUUtilization for 7 days in context

The result was a database that was extremely slow to respond, but not unresponsive. Requests to the APIs connected to the database were taking 10x longer to respond than normal.

The WAF did not indicate an increase in traffic (although it did coincide with what is typically the busiest part of the day).

Within three hours, the situation resolved itself, and response times returned to normal.

My questions are:

  1. What would cause BinLogDiskUsage to suddenly increase?
  2. Why would this cause the DB to become so slow, as CPUUtilization only reached around 20%? (Am I confusing cause and effect?)

If it's helpful, the max_binlog_size is 134217728

0

3 Answers 3

0

Since writing to Binary Logs when there are a lot of INSERTs, UPDATEs, DELETEs and some DDL, you need to look for any processes on your end that would have been loading data in bulk.

EXAMPLES

  • Loading a mysqldump into a database would have all the changes pouring into the Binary Logs
  • Running pt-online-schema-change creates a temp table, creates three triggers, and copies every row from a source table to the temp table. All of that is recorded in the Binary Logs
  • RDS-specific DB Commands are also recorded in the Binary Logs.

The only way to answer this question is the run mysqlbinlog --read-from-remote-server to dump the contents of the Binary Logs to a disk file (if the Binary Logs from 2 Days ago are still avaialble).

You should run this

mysql> CALL mysql.rds_set_configuration('binlog retention hours',168);

to leave the Binary Logs around for 1 week (336 would be two weeks)

Then go examine the Binary Log contents and see what the events were.

0

Do you have a table that is getting larger as time goes on? And you do something like

UPDATE tbl SET foo = 1.2 * foo;

to modify all the rows?

That query, itself, will slow down other db activity, plus make for bigger binlogs as the table grows.

Look for such queries. The SlowLog can help if you have trouble finding the query.

Then, let's discuss workarounds for the naughty query.

2
  • It looks like it was an IOPS issue. The DB serves an app which makes regular read/writes while the user is active, and it has tens of thousands of concurrent users during busy periods. It seems we were regularly exceeding base IOPS, and it was running out of burst credits with enough load, thus slowing down the app, triggering retries in a feedback loop that just made things worse. It was only as users dropped off at the end of business hours that it allowed it to recover. Increasing the base IOPS appears to have prevented a repeat of the problem... so far...
    – RED_Tom
    Sep 24, 2023 at 16:55
  • Let's see the queries that are happening during busy periods.
    – Rick James
    Sep 24, 2023 at 18:07
0

I have created a Python tool, which extracts and counts the statements from the binary logs and creates a nice report. This report can help during the investigation of such cases. Especially UPDATE statements can blow up binary logs, or increase the amount of created binary logs (if there is a size limit defined).

I'm open for any improvements on this tool.

https://github.com/SHWorX/mysql-binary-logs-analyzer

2
  • Hi Cody, this doesnt read as spam at the moment. However, you should read the help on How Not to be a Spammer, to prevent your posts getting marked as spam. Nov 15, 2023 at 3:04
  • Hi @RohitGupta, thanks for your advise, I will keep that in mind for future posts. Of course I do not want to see this post as spam, I mentioned (and linked the repo) of this tool, cause I had in the past also some issues with large amount of binary logs created in a short period. In many answers (and comments) in stackoverflow (and here too), people provided a bash script to get the kind of report which my tool generates. Unfortunately, these scripts (or one-liners) are sometimes hard to understand, or requires some changes, this is where my tool comes in handy, it's easy to use for anyone
    – CodyKL
    Nov 15, 2023 at 5:57

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.