Thanks for the replies
The longest running query always is a sleeping one. ie:
| 1260 | user | localhost | user | Sleep | 1233 | | NULL
The one following seems to be the one causing the trouble:
| 3245 | user | localhost | user | Query | 503 | Sending data | SELECT COUNT(*) from cpgn_pictures WHERE approved = 'YES' AND aid IN (1,2,3,5,6,7,8,9,10,11,12,13 |
As you can see the query is from a Coppermine gallery script. It keeps hanging on 'Sending data". This is the case every time the thing 'hangs'.
Initially I used a tweaked my.cnf, but because of the issues I reverted back to the Debian default one. Both have the same effect on the 'crashing'.