I have a small innodb table ~ 2k rows in Mysql 5.5.31-log version. Dont
understand why sometimes in a day , one update on that table is taking
around 1 minute to complete.
DBName Table Name Data Data Free
| test | users | 0.48MB | 0.00MB |
Is dere any way to understand locking hierarchy on that table in a day so
that we came to know at that particular time who is taking lock on users
table due to which update is taking time to finish.
Usuallu it takes less than 10 ms to execute.
Slow query log :
# User@Host: user[userA] @ [Client IP]
# Query_time: 74.976727 Lock_time: 0.000072 Rows_sent: 0 Rows_examined: 1
UPDATE `users` SET `last_sign` = '2014-01-07 14:34:45', `current_sign` =
'2014-01-07 14:34:45', `last_sign` = '22.214.171.124', `current_sign_in_ip` =
'126.96.36.199', `sign_in_count` = 1, `updated_at` = '2014-01-07 14:34:45' WHERE
`users`.`id` = 2065;
Strange thing it happens once in day or two.
Backup is scheduled at different time.
Any idea to troubleshoot and fix the root cause.
|• Locking causing slow updates||Adarsh Sharma||8 Jan 2014|