Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » Locked query explosion
icon9.gif  Locked query explosion [message #1562] Thu, 26 July 2007 20:20 Go to next message
coda is currently offline coda
Messages: 2
Registered: July 2007
Junior Member
I was greeted by this wonderful output from one of my DB servers:
mysql> show full processlist;
+----------+-----------+------------------+------+---------+------+--------+--------------------------------------+
| Id       | User      | Host             | db   | Command | Time | State  | Info                                 |
+----------+-----------+------------------+------+---------+------+--------+--------------------------------------+
| 21180274 | boarduser | 10.0.0.102:59802 | abc2 | Query   |  853 | NULL   | LOCK TABLES x WRITE                  |
| 21180415 | boarduser | 10.0.0.102:51423 | abc2 | Query   |  847 | NULL   | LOCK TABLES x WRITE                  |
| 21180809 | boarduser | 10.0.0.100:58242 | abc2 | Query   |    0 | NULL   | show full processlist                |
| 21181068 | boarduser | 10.0.0.102:60884 | abc2 | Query   |  820 | NULL   | LOCK TABLES x WRITE                  |
| 21181610 | boarduser | 10.0.0.102:49327 | abc2 | Query   |  797 | NULL   | LOCK TABLES x WRITE                  |
| 21182156 | boarduser | 10.0.0.102:49332 | abc2 | Query   |  774 | NULL   | LOCK TABLES x WRITE                  |
| 21183134 | boarduser | 10.0.0.102:63748 | abc2 | Query   |  733 | Locked | LOCK TABLES x READ                   |
| 21183365 | boarduser | 10.0.0.102:63776 | abc2 | Query   |  723 | NULL   | LOCK TABLES x WRITE                  |
| 21184205 | boarduser | 10.0.0.102:63957 | abc2 | Query   |  691 | Locked | LOCK TABLES x READ                   |
| 21184223 | boarduser | 10.0.0.102:63959 | abc2 | Query   |  690 | Locked | LOCK TABLES x READ                   |
| 21185017 | boarduser | 10.0.0.102:64114 | abc2 | Query   |  663 | NULL   | LOCK TABLES x WRITE                  |
| 21185417 | boarduser | 10.0.0.102:64200 | abc2 | Query   |  647 | Locked | LOCK TABLES x READ                   |
| 21185784 | boarduser | 10.0.0.102:64270 | abc2 | Query   |  633 | NULL   | LOCK TABLES x WRITE                  |
| 21186488 | boarduser | 10.0.0.102:64423 | abc2 | Query   |  605 | Locked | LOCK TABLES x READ                   |
| 21186684 | boarduser | 10.0.0.102:64479 | abc2 | Query   |  598 | Locked | LOCK TABLES x READ                   |
| 21187368 | boarduser | 10.0.0.102:64612 | abc2 | Query   |  570 | Locked | LOCK TABLES x READ                   |
| 21188628 | boarduser | 10.0.0.102:64922 | abc2 | Query   |  519 | Locked | LOCK TABLES x READ                   |
| 21189640 | boarduser | 10.0.0.102:65084 | abc2 | Query   |  480 | Locked | LOCK TABLES x READ                   |
| 21189958 | boarduser | 10.0.0.102:49824 | abc2 | Query   |  466 | Locked | LOCK TABLES x READ                   |
| 21190054 | boarduser | 10.0.0.102:60472 | abc2 | Query   |  462 | Locked | LOCK TABLES x READ                   |
| 21190414 | boarduser | 10.0.0.102:64882 | abc2 | Query   |  449 | Locked | LOCK TABLES x READ                   |
| 21191849 | boarduser | 10.0.0.102:52529 | abc2 | Query   |  389 | Locked | LOCK TABLES x READ                   |
| 21192966 | boarduser | 10.0.0.102:53159 | abc2 | Query   |  343 | Locked | LOCK TABLES x READ                   |
| 21193122 | boarduser | 10.0.0.102:53184 | abc2 | Query   |  337 | Locked | LOCK TABLES x READ                   |
| 21193868 | boarduser | 10.0.0.102:53315 | abc2 | Query   |  306 | Locked | LOCK TABLES x READ                   |
| 21194730 | boarduser | 10.0.0.102:53399 | abc2 | Query   |  272 | Locked | LOCK TABLES x READ                   |
| 21195061 | boarduser | 10.0.0.102:55693 | abc2 | Query   |  259 | Locked | LOCK TABLES x READ                   |
| 21195930 | boarduser | 10.0.0.102:55821 | abc2 | Query   |  224 | Locked | LOCK TABLES x READ                   |
| 21196348 | boarduser | 10.0.0.102:60491 | abc2 | Query   |  208 | Locked | LOCK TABLES x READ                   |
| 21196694 | boarduser | 10.0.0.102:65380 | abc2 | Query   |  194 | Locked | LOCK TABLES x READ                   |
| 21197674 | boarduser | 10.0.0.102:51113 | abc2 | Query   |  156 | Locked | LOCK TABLES x READ                   |
| 21198024 | boarduser | 10.0.0.102:51182 | abc2 | Query   |  142 | Locked | LOCK TABLES x READ                   |
| 21198387 | boarduser | 10.0.0.102:60036 | abc2 | Query   |  129 | Locked | LOCK TABLES x READ                   |
| 21199468 | boarduser | 10.0.0.102:61690 | abc2 | Query   |   86 | Locked | LOCK TABLES x READ                   |
| 21199529 | boarduser | 10.0.0.102:61697 | abc2 | Query   |   84 | Locked | LOCK TABLES x READ                   |
| 21200066 | boarduser | 10.0.0.102:61809 | abc2 | Query   |   60 | Locked | LOCK TABLES x READ                   |
| 21200497 | boarduser | 10.0.0.102:61909 | abc2 | Query   |   41 | Locked | LOCK TABLES x READ                   |
| 21200777 | boarduser | 10.0.0.102:61966 | abc2 | Sleep   |    0 |        | NULL                                 |
| 21200782 | boarduser | 10.0.0.102:61968 | abc2 | Query   |   31 | Locked | LOCK TABLES x WRITE                  |
| 21200806 | boarduser | 10.0.0.102:61971 | abc2 | Query   |   29 | Locked | LOCK TABLES x WRITE                  |
| 21200819 | boarduser | 10.0.0.102:61973 | abc2 | Query   |   29 | Locked | LOCK TABLES x WRITE                  |
| 21200830 | boarduser | 10.0.0.102:61975 | abc2 | Query   |   29 | Locked | LOCK TABLES x WRITE                  |
| 21200833 | boarduser | 10.0.0.102:61977 | abc2 | Query   |   28 | Locked | LOCK TABLES x WRITE                  |
| 21200857 | boarduser | 10.0.0.102:61982 | abc2 | Query   |   28 | Locked | LOCK TABLES x WRITE                  |
| 21200864 | boarduser | 10.0.0.102:61984 | abc2 | Query   |   27 | Locked | LOCK TABLES x WRITE                  |
| 21200886 | boarduser | 10.0.0.102:61988 | abc2 | Query   |   26 | Locked | LOCK TABLES x WRITE                  |
| 21200898 | boarduser | 10.0.0.102:61990 | abc2 | Query   |   26 | Locked | LOCK TABLES x WRITE                  |
| 21200900 | boarduser | 10.0.0.102:61992 | abc2 | Query   |   26 | Locked | LOCK TABLES x WRITE                  |
| 21200919 | boarduser | 10.0.0.102:61997 | abc2 | Query   |   25 | Locked | LOCK TABLES x WRITE                  |
| 21200942 | boarduser | 10.0.0.102:61999 | abc2 | Query   |   25 | Locked | LOCK TABLES x WRITE                  |
| 21200987 | boarduser | 10.0.0.102:62006 | abc2 | Query   |   23 | Locked | LOCK TABLES x WRITE                  |
| 21200993 | boarduser | 10.0.0.102:62008 | abc2 | Query   |   22 | Locked | LOCK TABLES x WRITE                  |
| 21201034 | boarduser | 10.0.0.102:62012 | abc2 | Query   |   21 | Locked | LOCK TABLES x WRITE                  |
| 21201043 | boarduser | 10.0.0.102:62014 | abc2 | Query   |   20 | Locked | LOCK TABLES x WRITE                  |
| 21201046 | boarduser | 10.0.0.102:62016 | abc2 | Query   |   20 | Locked | LOCK TABLES x WRITE                  |
| 21201089 | boarduser | 10.0.0.102:62024 | abc2 | Query   |   18 | Locked | LOCK TABLES x WRITE                  |
| 21201113 | boarduser | 10.0.0.102:62028 | abc2 | Query   |   17 | Locked | LOCK TABLES x WRITE                  |
| 21201156 | boarduser | 10.0.0.102:62033 | abc2 | Query   |   15 | Locked | LOCK TABLES x WRITE                  |
| 21201188 | boarduser | 10.0.0.102:58944 | abc2 | Query   |   14 | Locked | LOCK TABLES x WRITE                  |
| 21201289 | boarduser | 10.0.0.102:59252 | abc2 | Query   |    9 | Locked | LOCK TABLES x WRITE                  |
| 21201313 | boarduser | 10.0.0.102:56240 | abc2 | Query   |    8 | Locked | LOCK TABLES x WRITE                  |
| 21201400 | boarduser | 10.0.0.102:51523 | abc2 | Query   |    3 | Locked | LOCK TABLES x WRITE                  |
| 21201413 | boarduser | 10.0.0.102:50122 | abc2 | Query   |    2 | Locked | LOCK TABLES x WRITE                  |
| 21201429 | boarduser | 10.0.0.102:59458 | abc2 | Query   |    2 | Locked | LOCK TABLES x WRITE                  |
| 21201453 | boarduser | 10.0.0.102:64592 | abc2 | Query   |    2 | Locked | LOCK TABLES x WRITE                  |
| 21201463 | boarduser | 10.0.0.102:52391 | abc2 | Query   |    1 | Locked | LOCK TABLES x WRITE                  |
| 21201491 | boarduser | 10.0.0.102:54131 | abc2 | Query   |    0 | Locked | LOCK TABLES x WRITE                  |
| 21201506 | boarduser | 10.0.0.102:62358 | abc2 | Sleep   |    0 |        | NULL                                 |
+----------+-----------+------------------+------+---------+------+--------+--------------------------------------+
69 rows in set (0.01 sec)


As you can imagine, it gets worse until all the connections are filled.
Table x is MyISAM. There are about 100 requests per minute to a PHP script which does LOCK TABLES x WRITE as shown above, does some very simple select queries (no joins, mostly uses covering indexes) and a single row update and insert on a table with <5000 rows, and unlocks and disconnects to do some more work. I am baffled as to why these locked queries are piling up and how to fix it. Shouldn't there be a query that these locked threads are waiting upon? I don't know if InnoDB would fix this, since I will end up selecting every row in the table for update anyway.

Any ideas? Thanks
Report message to a moderator
Re: Locked query explosion [message #1598 is a reply to message #1562 ] Thu, 16 August 2007 08:42 Go to previous messageGo to next message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
There is but it might be hard to catch

It may well be some query in the Sleep stage which just took the table and now sleeping with the lock or it could be some long running query which uses this table (these are easy to catch)

One of the frequent reasons for this problem is using persistent connection when script is aborted after locking the table and it could take a while before some other script runs unlock tables in the same connection to set table free.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Re: Locked query explosion [message #1636 is a reply to message #1562 ] Thu, 16 August 2007 21:34 Go to previous messageGo to next message
coda is currently offline coda
Messages: 2
Registered: July 2007
Junior Member
Hi Peter,
Thanks for your reply. We definitely have no scripts using persistent connections, and queries taking more than 1 second are extremely rare (verified with the slow query log). Any advice on how to track down what's causing this?
Report message to a moderator
Re: Locked query explosion [message #1639 is a reply to message #1636 ] Fri, 17 August 2007 05:28 Go to previous message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
Which MySQL version is that ? What OS ?

Also try running "mysqladmin debug" which will post you some data about who owns table locks.

When this happens try killing all connections which are not waiting for lock tables as this would release the lock

It is possible some connection lock the table and when does long batch job but no long queries....

It also could be spme form of threading or MySQL bug with lock beomming stale

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Previous Topic:Unable to store images into BLOB
Next Topic:45 million row table - Two BETWEENs not using a composite index optimally
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Mon Jul 6 17:15:25 EDT 2009

Total time taken to generate the page: 0.02811 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.