We have a MySQL server on RDS, version 5.6.34.R1 with innodb.
Our traffic is pretty moderate, it fluctuates at around 100-300 KB/S.
Our selects per second hovers at around 30, and can sometimes spike up to 120.
All of our tables are properly indexed, and according to the server status in MySQL Workbench, the Key Efficiency is 100%.
The server itself is on an M4.4xLarge.
However, if it's anything lower than M4.4xLarge, during a small traffic spike (which should be tolerable for even an M4 Medium), one query starts, which joins maybe 3 or 4 tables, and lags behind and take a couple minutes, and then everything starts to fail.
More and more queries start to come in, but are unable to complete.
The number of connections rises significantly (300+), reaching the "maximum number of connections."
The CPU and RAM usage of the database server increases significantly.
Killing all of the connections only delays the inevitable for a couple of seconds; they come right back.
This is with 0 writes, and only SELECTs.
I'm not sure what to do. Is there some configuration I should be looking at? With out kind of traffic, an M4 Medium really should be able to handle it and more.
Update:
I've set the slow_query_log threshhold to 2 seconds.
I've downgraded the instance to an m4.xlarge; we'll see how long until it collapses.
Once I get a list of slow queries, I'll post them here.