List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:January 8 2014 11:46am
Subject:Locking causing slow updates
View as plain text  
Hi,

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
SET timestamp=1389105360;
UPDATE `users` SET `last_sign` = '2014-01-07 14:34:45', `current_sign` =
'2014-01-07 14:34:45', `last_sign` = '1.2.3.4', `current_sign_in_ip` =
'1.2.3.4', `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.


Thanks

Thread
Locking causing slow updatesAdarsh Sharma8 Jan 2014