MySQL Bugs: #34978: Threads in sending data state too long time

Bug #34978	Threads in sending data state too long time
Submitted:	1 Mar 2008 23:20	Modified:	3 Mar 2008 15:56
Reporter:	Yuriy Iovkov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1	OS:	FreeBSD (6.3)
Assigned to:		CPU Architecture:	Any
Tags:	sending data

Description:
When loading of server rise above 500 query per sec, there are appearance threads  with State "Sending data". And those threads i see very long time (more then 300 sec). Then all my applications witch using DB go down.
This threads usually contained very simple SELECT query.

Additional:
I try to run my database on new server and get this problem.
But the database work on other (no fester) server and have loading about 1500 query/sec. and have no problems.

Configuration files on both servers are simple, but old server work under fedora core 6. Also new server have raid1+0.

All it's very strange. 

I hope to your help.

How to repeat:
loading of server rise above 500 query per sec on freebsd server

Please, send your my.cnf. If your SELECTs work with InnoDB tables, please, send the results of SHOW INNODB STATUS. Ho many CPUs/cores do you have on this new server?

mysql proc status

Attachment: log_7 (application/octet-stream, text), 8.90 KiB.

show innodb status

Attachment: log_i_7 (application/octet-stream, text), 63.07 KiB.

configuration of new server is:
RAM 4Gb
CPU 2 x Xeon E5335 ,4 core

I try to use different values of thread_concurrency in my.cnf , but have no result.

config of mysql

Attachment: my.cnf (application/octet-stream, text), 2.47 KiB.

Thank you for the feedback.

I INNODB monitor output you have:

------------------------
LATEST DETECTED DEADLOCK
------------------------
080303 10:37:41
...
*** (2) TRANSACTION:
TRANSACTION 0 542871, ACTIVE 63 sec, OS thread id 2559773696 fetching rows, thread declared inside InnoDB 100
mysql tables in use 3, locked 3
1572 lock struct(s), heap size 194544, 463771 row lock(s), undo log entries 5
MySQL thread id 1521, query id 144803 localhost romewar_php
UPDATE _game_players_items SET status = 1 WHERE id IN (@head, @shoulder, @body, @hand1, @ring1, @ring2, @foot, @belt)
*** (2) HOLDS THE LOCK(S):

Thread still is in SHOW PROCESSLIST:

| 1521 | romewar_php | localhost                          | romewar_main | Query          | 24   |                           | UPDATE _game_players_items SET status = 1 WHERE id IN (@head, @shoulder, @body, @hand1, @ring1, @rin |

So I'll close the report as "Not a Bug", because you catched deadlock.

but what about thread 2376 (ELECT COUNT(pid) cnt FROM ...)
as i now SELECT query on InnoDB tables processing without any locks. But its status is "Sending data" (342 sec.)

The same queries to lead to such troubles.