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?