MySQL Bugs: #60866: Queries in lock state

Bug #60866	Queries in lock state
Submitted:	14 Apr 2011 9:42	Modified:	14 May 2011 11:21
Reporter:	khurram javed	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S5 (Performance)
Version:	5.0.67	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
mysql  Ver 14.12 Distrib 5.0.67, for suse-linux-gnu (x86_64)
 

After every 2 three hours we get queries in locked state and cpu over 300%, and again and again, we have to flush/restart db.
here is the slow query log.
samples are 
mysqld is using a lot of cpu.

processlist from mysql.

[code]mysql> show processlist;
+-------+-------+-------------------------------+-------------------+---------+------+-------------------------+------------------------------------------------ ------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------+-------------------------------+-------------------+---------+------+-------------------------+------------------------------------------------ ------------------------------------------------------+
| 592 | start | startlamp01.gkl.tdch.dk:55885 | db_papkassen_prod | Query | 8611 | FULLTEXT initialization | SELECT COUNT(distinct a.id) AS a__0 FROM ad a W HERE a.active = 1 AND a.adstatusid = 1 AND a.expiryda |
| 9634 | start | startlamp01.gkl.tdch.dk:53850 | db_papkassen_prod | Query | 2101 | Locked | UPDATE ad SET adstatusid = 5 WHERE id = 800415 |
| 9640 | start | startlamp01.gkl.tdch.dk:53871 | db_papkassen_prod | Sleep | 2097 | | NULL |
| 9642 | start | startlamp01.gkl.tdch.dk:53873 | db_papkassen_prod | Query | 2094 | Locked | SELECT COUNT(distinct a.id) AS a__0 FROM ad a W HERE a.active = 1 AND a.adstatusid = 1 AND a.expiryda |
| 9662 | start | startlamp01.gkl.tdch.dk:53950 | db_papkassen_prod | Query | 2076 | Locked | SELECT a.id AS a__id, a.categoryid AS a__catego ryid, a.userid AS a__userid, a.adtypeid AS a__adtypei |
| 9677 | start | startlamp01.gkl.tdch.dk:54090 | db_papkassen_prod | Query | 2063 | Locked | SELECT a.id AS a__id, a.categoryid AS a__catego ryid, a.userid AS a__userid, a.adtypeid AS a__adtypei |
| 9717 | start | startlamp01.gkl.tdch.dk:54300 | db_papkassen_prod | Query | 2038 | Locked | SELECT a.id AS a__id, a.categoryid AS a__catego ryid, a.userid AS a__userid, a.adtypeid AS a__adtypei |

and 100s of other queries

How to repeat:
our guess is, its due to indexing which is used for search, but not sure.

Suggested fix:
we increase innodb_buffer_pool_size to 5GB.
optimized search queries as below
SELECT COUNT(distinct a.id) AS a__0 FROM ad a WHERE a.active = 1 AND a.adstatusid = 1 AND a.expirydate >= '2011-04-06 08:52:02' AND a.categoryid in (1, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 195, 196, 197, 198, 199, 200, 203, 204, 205, 206, 207, 280, 281, 282, 283, 284, 285, 286, 287, 288) AND a.regionid in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 17, 18, 19, 29) AND match(a.searchtext) against ('t?FFFFC3?FFFFA5rnbuffe t?FFFFC3?FFFFA5rnbuff t?FFFFC3?FFFFA5rnbuffer' IN BOOLEAN MODE)
closed all open mysql connection
connection limit : 750

Please, check if the problem is repeatable with a newer version, 5.0.92 at least. If it is, please, send the results of EXPLAIN for the full query (use SHOW FULL PROCESSLIST to get it) like:

SELECT COUNT(distinct a.id) AS a__0 FROM ad a W HERE a.active =
1 AND a.adstatusid = 1 AND a.expiryda ...

that blocks every other access to this MyISAM table (ad). Send also SHOW CREATE TABLE and SHOW TABLE STATUS results for it.

thanks for quick reply . We will update to newer version, and will be back if there is still the same issue.

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".