0

I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during peak traffic time. This often happens 3 or 4 times throughout the peak traffic hours.

During these unresponsive times, it is almost impossible to open a connection with the database (timeouts are the most common response). If you get a connection, queries normally perform as expected for a short while. The processlist shows dozens to hundreds of items with state "init" and info "commit". Row operations drop nearly to zero and stay there for minutes on end until the database suddenly begins to recover, the processlist clears, and it becomes responsive to traffic once again.

What we have attempted so far:

  • We have tried to remove all slow queries during these peak hours, shutting down large swaths of functionality and workers during these times.
  • We have increased the redo log size in innodb.
  • We have looked for lock contention and deadlocks.
  • We have doubled the compute power, memory, and throughput available to the RDS instance. (DB CPU usage during peak hours hovers around 50% and there are no alarming spikes in memory or disk or network usage.)
  • We have tried using proxy servers to hold open long-lived connections to the database.
  • We've looked for any recent changes or new queries introduced in the app.

All of that only helped a little bit. We often still see at least one "storm" where we get stuck in the bad state, and re-enabling any batch jobs tends to push us over the edge.

Has anyone seen the pattern of processes getting stuck in STATE=init, INFO=commit for minutes on end? Does anyone have suggestions on how to proceed with debugging or analysis? What other resource contention could we be running into?

  • Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Sep 25 at 16:24
0
  • You are seeing the victims. Probably only a few of them are the villains.
  • Look at "Time". A few 'system' processes may have very large times; look at the next couple -- they may be the ones that started the problem
  • How big is max_connections? If it is more than a few dozen, it is inviting a log jam.
  • When a lot of threds are running (check Threads_running), they tend to stumble over each other, waiting for resources (CPU, I/O, buffer_pool space, table cache space, etc). Meanwhile, the allocation mechanism is "playing fair". That is, it is given each player a little of what it needs, so as to give a fair amount to the others. Net effect: Latency goes through the roof and throughput flattens or declines.

Cures:

  • Check for swapping -- Swapping is terrible for performance, and it may be a factor in what you are seeing.
  • Lower (yes, lower) max_connections.
  • Lower (yes, lower) the number of web server clients that can run simultaneously.
  • Use the slowlog (with a low value of long_query_time to identify both the long-running queries and the fast, but frequently running, queries. Improve both. It is usually easier to give the end-user a civilized message "System is busy, DO NOT HIT RELOAD", than to let things clog up and "never" finish.
  • Use Replicas to spread the readonly queries across multiple servers.
| improve this answer | |
  • Thank you! We've started down some of this list, but not all of it (and not always as aggressively as you suggest). We'll go through the rest of the bullets and post updates here. The next time we have an incident we'll check the stats and post responses to your questions above. – paxundae Sep 22 at 21:21
  • @paxundae - If you had any monitoring turned on, check for swapping, Max_used_connections, Threads_running (ephemeral), etc. – Rick James Sep 22 at 21:34
  • The proxy messes with our max used connections recently, but when this first started that figure was hovering between 900 and 950. No indication of swapping, thankfully. Threads running was normally in the 30s but would spike up to 1k during the events. Not clear if that's a symptom, a cause, or both. – paxundae Sep 22 at 21:52
  • That is rather high. – Rick James Sep 22 at 21:54
  • So I take it from your initial bullets! We'll try lowering those ceilings and see how that goes. – paxundae Sep 22 at 22:07

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.