Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have an InnoDB table for visitors' counting, which has perfectly worked for several years, but it failed twice yesterday, may be cause of the increase of visitors Without apparent reason, this table locked, with hundreds of DELETE an REPLACE INTO queries (+500) with "updating" or "cleaning up" status. (I've no more the copy of the processlist...)

This table contains few entries, between 500 and 1500, so the updating queries are usualy very fast and don't lock.

When the problem occurred, I checked the processlist a lot of times, and I haven't seen any query which was locking the table explicitly. Actually, I set my websites in maintenance mode, so no more queries were added to the queue, and there still were more than 500 queries, all the same :

+---------+---------+--------------+---------+---------+------+----------+-----------------------------------------------+
| Id      | User    | Host         | db      | Command | Time | State    | Info                                          |
+---------+---------+--------------+---------+---------+------+----------+-----------------------------------------------+
| 2780657 | XXXXXXX | XXXXXX:58199 | XXXXXXX | Query   |  660 | updating | DELETE FROM connected WHERE time < 1272100968 |
| 2764941 | XXXXXXX | XXXXXX:57314 | XXXXXXX | Query   |  660 | updating | DELETE FROM connected WHERE time < 1272100968 |
| 2784467 | XXXXXXX | XXXXXX:44221 | XXXXXXX | Query   |  661 | updating | DELETE FROM connected WHERE time < 1272100969 |
...

I don't know where I must search to find the cause of this problem and resolve it definitively.

But I guess this could give you a better vision of the problem :

mysql> show global status like "%innodb_row_lock%";
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| Innodb_row_lock_current_waits | 0         |
| Innodb_row_lock_time          | 132004175 |
| Innodb_row_lock_time_avg      | 10521     |
| Innodb_row_lock_time_max      | 59373     |
| Innodb_row_lock_waits         | 12546     |
+-------------------------------+-----------+
5 rows in set (0.00 sec)

Sorry for my poor english, and thanks for your help ;-)

share|improve this question

1 Answer 1

The trick is usually to locate the longest running query, which is identified in show full processlist; You can also use a utility like mytop to sort the queries by runtime.

Once you isolate the query causing the lock, you can try to determine why it locked. Was there an alteration on the table causing a write lock? There are a variety of different scenarios, which in most cases will be related to an application issue.

It's very difficult to provide any definitive recommendation without actively troubleshooting. At the very least, further details such as the processlist and the application in question would be helpful.

share|improve this answer
    
Thank you for your answer. When the problem occurred, I checked the processlist a lot of time, and I haven't seen any query which was locking the table explicitly. Actually, I set my websites in maintenance mode, so no more queries were added to the queue, and there still were more than 500 queries, all the same (I just added those precisions and the processlist in my question above). –  Skreo Apr 25 '10 at 13:15
    
SHOW INNODB STATUS is a helpful tool in cases like yours too. –  Ztyx Dec 2 '13 at 15:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.