I have a web/ database server with 5 websites on it, all of which execute fairly complex queries regularly.
Some of these queries appear in the slow log and take around 0.6-0.8 seconds. However I have a regular occurrence of such queries coming from 2 specific websites with 2 separate database that seem to lock each other up. What I am finding in this case in top is a MySQL process occupied with about 20-30% of a CPU and about 30 Apache processes taking up about 30% CPU each. This situation goes away after about 15-20 minutes or if I restart the MySQL server. The slow query log shows more slow queries at this time.
The problem never seems to involve only queries to a single database, but always to the same 2 databases (and websites)
The server is on a VPS with 4GB of RAM, 5 CPU cores on Ubuntu 12.04
Any idea how I could diagnose what is happening?