I'm running a site on AWS with the following setup:
Single m1.small instance (web server)
Single RDS m1.small db
Joomla 1.5
Generally, the site is performant, but is fairly low-traffic - say around 50-100 visits / hour. However, at peak time, we see about double that traffic. During peak time, pretty much every day:
CPU usage on the web server slowly climbs to 100%
CPU usage on the RDS server climbs quite quickly to about 30%, from an average of about 15
Database connections shoot up to about 140, from a normal average of about 2 or 3
The site is then occasionally unreachable, certainly according to pingdom monitoring.
Does anyone recognise this behaviour? Can you point me in the right direction to begin investigating? Of course, RDS makes it difficult to do things like slow query logging, so I've started by regularly dumping the mysql process list into a file to see if there's anything I can spot there, but it would be good to have something more concrete to investigate.
UPDATE
At least, can someone confirm that I'm definitely right in saying that the level of traffic implies the problem must be a specific type of query taking way longer than it should to execute? This would happen if a table gets locked, and many queries need to write to it, right? For this very reason, I've already changed the __session table type to InnoDB.
