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 previous 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
[Message index]

Read Message
Read Message
Read Message
Read Message
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:11:16 EDT 2009

Total time taken to generate the page: 0.00556 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.