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