Bug #61317 Many locked connections while one connection is "Sending data"
Submitted: 27 May 2011 6:58 Modified: 19 Sep 2011 23:44
Reporter: Ricardo Amillategui Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:4.1 OS:Any
Assigned to: CPU Architecture:Any

[27 May 2011 6:58] Ricardo Amillategui 
Description:
In the last days we have detected that many queries stay "locked" while one remains "sending data". The strange thing is that the connection that makes the other stay locked is processing a select and not an update or insert query. Database default engine is supossed to be Innodb but tables are created as MyIsam so we decided to bump key_buffer_size to 128MB. No more because machine total RAM amount is 2GB and 1,6GB is exclusive for Apache. But the problem stays there. Additionally, we analized column indexing and we think columns are correctly indexed. ¿Why select queries lock the others? ¿What we should do? Any help will be very useful. Thanks

How to repeat:
The problem could be repeated loading the database with not very complex queries.
[27 May 2011 7:02] Ricardo Amillategui 
Server Variables

Attachment: variables.txt (text/plain), 4.58 KiB.
[27 May 2011 7:04] Valeriy Kravchuk 
Please, send the output of SHOW PROCESSLIST when you see this problem.
[31 May 2011 8:13] Ricardo Amillategui 
ProcessList Output

Attachment: ProcessList_Output.txt (text/plain), 3.20 KiB.
[31 May 2011 8:16] Ricardo Amillategui 
I uploaded the file where the process list is shown. During this morning there was heavy activity over the application (Scarab) and connections were locked.

Thanks by interesting in our problem.
[31 May 2011 23:07] MySQL Verification Team 
Are you tried for test the current version servers? 4.1 version is End of Product Lifecycle.
[1 Jun 2011 5:54] Ricardo Amillategui 
So, which version do you think we should migrate to? Maybe any of 5.1?
[19 Jul 2011 18:29] Sveta Smirnova 
Thank you for the feedback.

Yes, to latest GA: either 5.1 or 5.5
[19 Aug 2011 23:00] Bugs System 
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".
[20 Sep 2011 23:00] Bugs System 
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".