MySQL Bugs: #20277: Queries block for no discernable reason

Bug #20277	Queries block for no discernable reason
Submitted:	6 Jun 2006 2:09	Modified:	23 Aug 2006 10:06
Reporter:	Henning Schulzrinne	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.20-standard-log	OS:	Linux (Centos 4)
Assigned to:		CPU Architecture:	Any

Description:
The server will work just fine for a while (several days, sometimes just minutes) and then suddenly queries start piling up, with processlists of 40 or more and CPU queue sizes of 10 to 50. The queries are the same small set, almost all SELECT, that's being processed without a hitch otherwise, as far as I can tell. (See attached processlist for a few snapshots of a recent incident.) Sometimes the system freezes, sometimes the process queue clears out and things go back to normal.

How to repeat:
Difficult - there does not appear to be a single type of query that causes this. I have entered all queries that were in the process log and ran them individually. Each one took less than one second to execute. I also tried 'EXPLAIN', with no obvious issues.

Suggested fix:
None.

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

I took a look at the error log and apparently, the server crashed during some of these incidents:

060606 15:28:51  mysqld restarted
060606 15:30:01  InnoDB: Started; log sequence number 0 47339
060606 15:30:03 [Note] Recovering after a crash using mysql-bin
060606 15:30:07 [Note] Starting crash recovery...
060606 15:30:07 [Note] Crash recovery finished.

I don't know if that qualifies as a bug or not.

In case of crash you should have a more detailed information in the error log: backtrace and, in many cases, even SQL statement that was executing while this crash happened. Please, send this information, with resolved stack trace (see comments in the error log on how to resolve it).

Please, send SHOW CREATE TABLE and SHOW TABLE STATUS results for that person table. Can you upload its dump (as private file, if you want)?

There may be a problem related to memory allocation; the crashes have become less frequent since I changed a very memory-intensive PHP function. (The system did crash again just yesterday.)

Thank you for the additional information. Please, send you my.cnf content and describe your hardware (amount of RAM). Send the results of "free" Linux command under usual load.

Please, try to decrease the following values in my.cnf:

read_rnd_buffer_size	= 2M # was 4M
myisam_sort_buffer_size	= 4M # was 64M

In case you'll see similar behaviour after that, please, try to repeat with a newer version, 5.0.22. 

Any ideas on how to repeat the behaviour described each and every time are also welcomed.

Hello, 

i don't know if it accurate to interfere in your bug report. Sorry if it disturbs.

From the description of the original bug, i have the same problem on a dual cpu 
dual core server ( 2 GB RAM ) running linux 2.4.33 with 4.0.24 as multidomain system. 

since 3-4 weeks we have queries which normaly run in <1 second together with multiply other queries from diffrent accounts, sometime, without warning, these small selects run 90 secs and more with 100% cpu usage ( at least they try ). 

520169 USERX localhost DBX Query 27 Sending data SELECT postid AS posts FROM post,thread^M
WHERE thread.threadid=post.threadid AND thread.forumid=
520175 USERX localhost DBX Query 26 Locked INSERT INTO thread (threadid,title,lastpost,forumid,open,replycount,postusername,lastposter
,dat
520198 USERX localhost DBX Query 24 Locked SELECT threadid FROM thread WHERE threadid=74904
520204 USERX localhost DBX Query 23 Locked SELECT threadid from thread 
.. a lot of locked selects follow for this db.....

The customer states that neither he lockes the table nor that the select would retrieve much data. fact is, it runs more than 90secs and it runs normaly for under 1 second the rest of the day. We where not able to retrieve the WHERE or ORDER statements of the selects in that particulare moment. If we open the querlog, the server becomes slow, because its a highly frequented system.
Sorry, its not an option as we don't know when it will run mad .

As these incidents gather a load ABOVE 500 it's critial for all of us.

Pls investigate it. Fell free to contact me , we maybe are able to offer more detailed informations on a none public scope.

BTW: Can you tell me how to set a max execution time for queries ? If it is possible at all.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".