troubleshooting Question

MySQL Lock up

Avatar of hassler
hassler asked on
MySQL Server
3 Comments1 Solution467 ViewsLast Modified:
Recently upgraded our server to Debian 4.0, Apache 2.0 and Mysql 5.0.32-Debian_7etch8-log. Among other things, the previous server setup was locking up (tight), but we could never determine why. Now at least, we're able to get a lot more information, and can see (clearly, I think) that it is a database issue that is locking up our web site (and previously, the entire server). However, I'm at a loss to explain why.

The attached "mysqladmin processlist" output shows the situation in the early stages of a lockup. At this point, the web site is completely unresponsive, as the web queries are all in a "Locked" state.

My biggest question is trying to figure out WHICH of these processes is holding a lock that the other threads are waiting on. You can see the top Queries have been in a "Copying to tmp table on disk" state for over 2 minutes (they'll continue for several more, seemingly never completing. under normal circumstances, these queries complete within a few seconds -- even though they may be analyzing about 3M records.

Are their obvious clues to the more-knowledgable out there that I can pursue, or any recommendations? The web site is fairly straightforward, with mostly SELECT-based queries. A fraction of traffic may store query results in a Results table (you can see some of those in the output) where previous results are DELETEd, and the INSERT INTO Results... SELECT to get the next set of results. There is ongoing administrative activity as well (INSERTion of new records and UPDATEs to existing information). We'll often see one of those SQL statements involved in this morass as well, although not in this particular case shown.

So, I believe something has a critical table locked, or deadlocked for some reason.
+--------+------+-------------------+-----+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id     | User | Host              | db  | Command | Time | State                        | Info                                                                                                 |
+--------+------+-------------------+-----+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 836537 | usr  | www.XXX.com:34403 | usr | Sleep   | 422  |                              |                                                                                                      |
| 837979 | usr  | www.XXX.com:35847 | usr | Sleep   | 273  |                              |                                                                                                      |
| 837981 | usr  | www.XXX.com:35849 | usr | Sleep   | 273  |                              |                                                                                                      |
| 838711 | usr  | www.XXX.com:36581 | usr | Sleep   | 231  |                              |                                                                                                      |
| 838734 | usr  | www.XXX.com:36604 | usr | Sleep   | 227  |                              |                                                                                                      |
| 838882 | usr  | www.XXX.com:36752 | usr | Sleep   | 208  |                              |                                                                                                      |
| 839044 | usr  | www.XXX.com:36913 | usr | Sleep   | 187  |                              |                                                                                                      |
| 839155 | usr  | www.XXX.com:45011 | usr | Sleep   | 180  |                              |                                                                                                      |
| 839269 | usr  | www.XXX.com:45125 | usr | Sleep   | 167  |                              |                                                                                                      |
| 839361 | usr  | www.XXX.com:45217 | usr | Sleep   | 154  |                              |                                                                                                      |
| 839364 | usr  | www.XXX.com:45220 | usr | Sleep   | 154  |                              |                                                                                                      |
| 839373 | usr  | www.XXX.com:45229 | usr | Sleep   | 152  |                              |                                                                                                      |
| 839375 | usr  | www.XXX.com:45231 | usr | Sleep   | 152  |                              |                                                                                                      |
| 839420 | usr  | www.XXX.com:45276 | usr | Query   | 95   | Locked                       | INSERT INTO Results (itemID, lead, auctionid, saledate, price, score, namescore, sessionID) SELECT   |
| 839423 | usr  | www.XXX.com:45279 | usr | Query   | 146  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839425 | usr  | www.XXX.com:45281 | usr | Query   | 146  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839430 | usr  | www.XXX.com:45286 | usr | Query   | 145  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839432 | usr  | www.XXX.com:45288 | usr | Query   | 145  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839438 | usr  | www.XXX.com:45294 | usr | Query   | 142  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839440 | usr  | www.XXX.com:45296 | usr | Query   | 141  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839443 | usr  | www.XXX.com:45299 | usr | Query   | 140  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839445 | usr  | www.XXX.com:45301 | usr | Query   | 140  | Copying to tmp table on disk | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839448 | usr  | www.XXX.com:45304 | usr | Sleep   | 133  |                              |                                                                                                      |
| 839473 | usr  | www.XXX.com:45329 | usr | Sleep   | 127  |                              |                                                                                                      |
| 839572 | usr  | www.XXX.com:45430 | usr | Sleep   | 116  |                              |                                                                                                      |
| 839588 | usr  | www.XXX.com:45446 | usr | Query   | 113  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839589 | usr  | www.XXX.com:45447 | usr | Query   | 113  | Locked                       | INSERT INTO Items (lead,origin,yearlow,yearhigh,description,provenance,`condition`,dimensions,litera |
| 839596 | usr  | www.XXX.com:45454 | usr | Query   | 110  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839597 | usr  | www.XXX.com:45455 | usr | Query   | 108  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839601 | usr  | www.XXX.com:45459 | usr | Query   | 105  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839603 | usr  | www.XXX.com:45461 | usr | Query   | 104  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839604 | usr  | www.XXX.com:45462 | usr | Query   | 103  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839606 | usr  | www.XXX.com:45464 | usr | Query   | 100  | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839609 | usr  | www.XXX.com:45467 | usr | Query   | 96   | Locked                       | SELECT /* Features.pm:26 */ Items.itemID FROM Auctions,ItemHistory,Items WHERE Items.feature='Y' AND |
| 839610 | usr  | www.XXX.com:45468 | usr | Query   | 96   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839613 | usr  | www.XXX.com:45471 | usr | Query   | 95   | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839614 | usr  | www.XXX.com:45472 | usr | Query   | 95   | Locked                       | SELECT itemID, lead FROM Items WHERE itemID=224241                                                   |
| 839616 | usr  | www.XXX.com:45474 | usr | Query   | 92   | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839617 | usr  | www.XXX.com:45475 | usr | Query   | 91   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839618 | usr  | www.XXX.com:45476 | usr | Query   | 88   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839620 | usr  | www.XXX.com:45478 | usr | Query   | 88   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839623 | usr  | www.XXX.com:45481 | usr | Query   | 83   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839625 | usr  | www.XXX.com:45483 | usr | Query   | 83   | Locked                       | SELECT /* Search.pm:200 */ DISTINCT Items.itemID,Items.lead,ItemHistory.saleDate,ItemHistory.price F |
| 839626 | usr  | www.XXX.com:45484 | usr | Query   | 82   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839627 | usr  | www.XXX.com:45485 | usr | Query   | 81   | Locked                       | UPDATE Items SET  lead='Table-Drop-Leaf; George III, Mahogany, Demilune Leaves, 1 Apron Drawer, Turn |
| 839630 | usr  | www.XXX.com:45488 | usr | Query   | 76   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839632 | usr  | www.XXX.com:45490 | usr | Query   | 74   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839633 | usr  | www.XXX.com:45491 | usr | Query   | 72   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839646 | usr  | www.XXX.com:45504 | usr | Query   | 72   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839648 | usr  | www.XXX.com:45506 | usr | Query   | 70   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839651 | usr  | www.XXX.com:45509 | usr | Query   | 66   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839652 | usr  | www.XXX.com:45511 | usr | Query   | 66   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839654 | usr  | www.XXX.com:45513 | usr | Query   | 65   | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839656 | usr  | www.XXX.com:45515 | usr | Query   | 65   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839658 | usr  | www.XXX.com:45517 | usr | Query   | 64   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839660 | usr  | www.XXX.com:45519 | usr | Query   | 60   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839661 | usr  | www.XXX.com:45520 | usr | Query   | 60   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839663 | usr  | www.XXX.com:45522 | usr | Query   | 60   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839665 | usr  | www.XXX.com:45524 | usr | Query   | 58   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839666 | usr  | www.XXX.com:45525 | usr | Query   | 57   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839667 | usr  | www.XXX.com:45526 | usr | Query   | 56   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839669 | usr  | www.XXX.com:45528 | usr | Query   | 55   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839672 | usr  | www.XXX.com:45531 | usr | Query   | 50   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839675 | usr  | www.XXX.com:45534 | usr | Query   | 49   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839676 | usr  | www.XXX.com:45535 | usr | Query   | 49   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839677 | usr  | www.XXX.com:45536 | usr | Sleep   | 16   |                              |                                                                                                      |
| 839678 | usr  | www.XXX.com:45537 | usr | Sleep   | 10   |                              |                                                                                                      |
| 839679 | usr  | www.XXX.com:45538 | usr | Query   | 46   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839680 | usr  | www.XXX.com:45539 | usr | Sleep   | 5    |                              |                                                                                                      |
| 839682 | usr  | www.XXX.com:45541 | usr | Query   | 45   | Locked                       | SELECT /* Features.pm:26 */ Items.itemID FROM Auctions,ItemHistory,Items WHERE Items.feature='Y' AND |
| 839683 | usr  | www.XXX.com:45542 | usr | Query   | 44   | Locked                       | SELECT /* Features.pm:26 */ Items.itemID FROM Auctions,ItemHistory,Items WHERE Items.feature='Y' AND |
| 839686 | usr  | www.XXX.com:45545 | usr | Query   | 40   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839687 | usr  | www.XXX.com:45546 | usr | Query   | 40   | Locked                       | DELETE FROM Results WHERE sessionID='0f9ab21f44053104308e8f40bff59ec4'                               |
| 839689 | usr  | www.XXX.com:45548 | usr | Query   | 40   | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839690 | usr  | www.XXX.com:45549 | usr | Query   | 39   | Locked                       | SELECT /* Features.pm:26 */ Items.itemID FROM Auctions,ItemHistory,Items WHERE Items.feature='Y' AND |
| 839692 | usr  | www.XXX.com:45551 | usr | Query   | 39   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839694 | usr  | www.XXX.com:45553 | usr | Query   | 38   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839695 | usr  | www.XXX.com:45554 | usr | Query   | 34   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839696 | usr  | www.XXX.com:45555 | usr | Query   | 33   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839697 | usr  | www.XXX.com:45556 | usr | Query   | 31   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839699 | usr  | www.XXX.com:45558 | usr | Query   | 31   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839701 | usr  | www.XXX.com:45560 | usr | Query   | 30   | Locked                       | SELECT itemID FROM Items WHERE itemID>=192012 AND itemID<192112                                      |
| 839703 | usr  | www.XXX.com:45562 | usr | Query   | 29   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839705 | usr  | www.XXX.com:45564 | usr | Query   | 25   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839708 | usr  | www.XXX.com:45567 | usr | Query   | 23   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839710 | usr  | www.XXX.com:45569 | usr | Query   | 22   | Locked                       | SELECT /* Search.pm:200 */ DISTINCT Items.itemID,Items.lead,ItemHistory.saleDate,ItemHistory.price F |
| 839712 | usr  | www.XXX.com:45571 | usr | Query   | 20   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839713 | usr  | www.XXX.com:45572 | usr | Query   | 20   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839714 | usr  | www.XXX.com:45573 | usr | Query   | 20   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839715 | usr  | www.XXX.com:45574 | usr | Query   | 20   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839716 | usr  | www.XXX.com:45575 | usr | Query   | 19   | Locked                       | SELECT r.* FROM Results r WHERE r.sessionID = '0a11051ad1f2479f1b9dc488aa5564c6' ORDER BY saleDate D |
| 839718 | usr  | www.XXX.com:45577 | usr | Query   | 17   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839720 | usr  | www.XXX.com:45579 | usr | Query   | 14   | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839723 | usr  | www.XXX.com:45582 | usr | Query   | 8    | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839725 | usr  | www.XXX.com:45584 | usr | Query   | 5    | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839726 | usr  | www.XXX.com:45585 | usr | Query   | 5    | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839728 | usr  | www.XXX.com:45588 | usr | Query   | 2    | Locked                       | SELECT count(DISTINCT Items.itemID,Items.lead,ItemHistory.auctionID,ItemHistory.saleDate,ItemHistory |
| 839735 | usr  | www.XXX.com:45596 | usr | Sleep   | 0    |                              |                                                                                                      |
| 839737 | usr  | www.XXX.com:45598 | usr | Query   | 0    | Locked                       | SELECT Items.*,ItemHistory.auctionID,ItemHistory.saleDate,Auctions.notes as auctionnote FROM Items
         |
| 839738 | usr  | localhost         |     | Query   | 0    |                              | show processlist                                                                                     |
+--------+------+-------------------+-----+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros