Tweet
We have a constantly recurring overloading issue with our server. The cause of the problem seems to be some kind of bottleneck with the mysql server. The simultaneous mysql connections oscillates normally around 3-10, but hits the roof (300) in a couple of seconds between 8-9 P.M. This causes the server to overload completely.
I have followed the mysql process list during just before the crashes, and noticed some queries that seem to piling up the following ones. Especially queries marked with state "sorting result" and "copying to tmp" seem to be the problematic ones.
Take a look at the following one, it showed up in my process list this evening, just before the server went down.
What could be wrong? Could it be this query? Or is it something else?
I have followed the mysql process list during just before the crashes, and noticed some queries that seem to piling up the following ones. Especially queries marked with state "sorting result" and "copying to tmp" seem to be the problematic ones.
Take a look at the following one, it showed up in my process list this evening, just before the server went down.
Code:
Id: 1712173
User: forum
Host: localhost
db: forum_h
Command: Query
Time: 29
State: Sorting result
Info: SELECT distinct *
FROM thread
WHERE forumid IN (6,56,55,54,74,30,27,29,20,24,28,113,92,49,1,8,9,22,21,23,10,11,12,13,25,14,26,15,17,94,19,95,16,110,18,111,7,57,62,66,61,67,63,65,58,59,60,64,5,52,53,50,51,107,4,73,93,48,47,70,71,72,3,38,109,41,39,112,42,108,43,40,46,45,44,36,37,115,116,134,117,135,118,138,120,127,128,131,2,32,34) AND open=1
ORDER BY replycount DESC
LIMIT 10
Comment