We have issue with MySql db and I'll try to describe what happens
Our site reaches about 11.000 visits per day and handles 2 MySql data bases,
hosted on VPS :
Total processors: 10
Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Total memory: 2GB
Issue that happens is that in MySQL Process List we can see that it gets full with queries that needs more than
160 secs of time to execute (connections are not "Sleep").
That would be no problem if it is only one or couple of connections, but when that happen connections only accumulate and MySql stops responding.
Sometimes it needs 2 - 5 minutes to clear and resolve queries and sometimes it needs 30 - 60 minutes.
Usually we resolve this issue by restarting MySql service, but occasionally happens that restarting is not solving this issue because the same minute that MySql starts it gets full of processes again.
Interesting thing is that this issue is not happening when we have visitors peek, we use google analytics and wecan see that, when we have 200 visitors at the same time, everything runs smoothly, also we do not use permanent connections, and everything on the site is cashed for 60 minutes.
Here is our my.cnf
table_definition_cache = 512
max_heap_table_size = 32M
tmp_table_size = 32M
We appreciate any help and idea what might be wrong