1

I am running LAMP stack with CakePHP and MySQL compatible AWS Aurora instance. The Aurora instance is set up with a writer and read replica. Using CakePHP request routing, the read replica is only set up to serve incoming web requests.

I am experiencing a strange intermittent spiking in the number of MySQL connections to the read replica. When this happens, it also causes a similar spike in CPU usage, and thus an increase in query latency that causes the server to become very slow. This lasts for perhaps only a few minutes at a time, and happens maybe once or twice per day intermittently.

Here a graph showing the correlation between number of connections and CPU utilization:

enter image description here

However, despite an increase in number of connections, there is no noticeable spike in the number of queries. Here is that same graph, but including the number of queries:

enter image description here

As you can see, there is basically no correlation.

What could cause a spike in the number of connections? The read replica is only being used to serve incoming web requests. Why would additional connections be opened, but there is no noticeable increase in the number of additional queries?

Charles
  • 67
  • 1
  • 11
  • The existing connections being in use for a longer time, or just not freed, so a next query needs a new connection. – Gerard H. Pille Aug 04 '20 at 09:32
  • Do you have a cron job running ever 4 hours? Or does RDS have such? – Rick James Aug 04 '20 at 15:36
  • Is that "connections PER SECOND"? Or what? Or maybe number of connections currently connected? Or maybe threads_running. I hate ambiguous graphs. – Rick James Aug 04 '20 at 15:37
  • Turn on the slowlog with a low value of `long_query_time`. Wait until it goes past the next spike. Use `pt-query-digest` to analyse it. See what happened during the spike. – Rick James Aug 04 '20 at 15:40

0 Answers0