Description:
All my tables are MyISAM and the update is causing table lock. My stored procedures are waiting, then timing out. Even though they were locked and timed out without finishing their job, the Lock_time seems to be very low.
Linux xxxxxxhost 3.0.26-0.7-default #1 SMP Tue Apr 17 10:27:57 UTC 2012 (3829766) x86_64 x86_64 x86_64 GNU/Linux
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.23-log |
+------------+
1 row in set (0.00 sec)
# Time: 131224 15:18:35
# User@Host: Toni[Toni] @ myhost [myipaddress]
# Query_time: 134.412257 Lock_time: 0.000091 Rows_sent: 0 Rows_examined: 0
SET timestamp=1387898315;
call mydb.mystps('2013-12-23 00:00:00' , '2013-12-28 00:00:00', '', '', 0);
# User@Host: Toni[Toni] @ myhost [myipaddress]
# Query_time: 132.370270 Lock_time: 0.000091 Rows_sent: 0 Rows_examined: 0
SET timestamp=1387898315;
call mydb.mystps('2013-12-23 00:00:00' , '2013-12-28 00:00:00', '', '', 0);
# User@Host: Toni[Toni] @ myhost [myipaddress]
# Query_time: 135.576004 Lock_time: 0.000089 Rows_sent: 0 Rows_examined: 0
SET timestamp=1387898315;
call mydb.mystps('2013-12-23 00:00:00' , '2013-12-28 00:00:00', '', '', 0);
# User@Host: Toni[Toni] @ myhost [myipaddress]
# Query_time: 133.167910 Lock_time: 0.000087 Rows_sent: 0 Rows_examined: 0
SET timestamp=1387898315;
call mydb.mystps('2013-12-01 00:00:00' , '2014-01-01 00:00:00', '', '', 0);
# User@Host: Toni[Toni] @ myhost [myipaddress]
# Query_time: 137.359866 Lock_time: 0.000088 Rows_sent: 0 Rows_examined: 0
How to repeat:
1. Set the slow query variables
+---------------------+----------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------+
| log_slow_queries | ON |
| long_query_time | 5 |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | slow.queries.log |
+---------------------+----------------------------------------+
2. Create MyISAM table
3. Insert 3M rows
3. Select * from the table
4. Quickly open another session and do update all on the same table
5. Quickly open another session and try to select a few rows from the same table
6. Look at the slow query for Lock_time
Suggested fix:
Lock_time should reflect true waiting time to obtain a lock.
"The lock_time is the time spent waiting for a lock" as described in MySQL forum: http://forums.mysql.com/read.php?35,377522,378908
Description: All my tables are MyISAM and the update is causing table lock. My stored procedures are waiting, then timing out. Even though they were locked and timed out without finishing their job, the Lock_time seems to be very low. Linux xxxxxxhost 3.0.26-0.7-default #1 SMP Tue Apr 17 10:27:57 UTC 2012 (3829766) x86_64 x86_64 x86_64 GNU/Linux mysql> select version(); +------------+ | version() | +------------+ | 5.5.23-log | +------------+ 1 row in set (0.00 sec) # Time: 131224 15:18:35 # User@Host: Toni[Toni] @ myhost [myipaddress] # Query_time: 134.412257 Lock_time: 0.000091 Rows_sent: 0 Rows_examined: 0 SET timestamp=1387898315; call mydb.mystps('2013-12-23 00:00:00' , '2013-12-28 00:00:00', '', '', 0); # User@Host: Toni[Toni] @ myhost [myipaddress] # Query_time: 132.370270 Lock_time: 0.000091 Rows_sent: 0 Rows_examined: 0 SET timestamp=1387898315; call mydb.mystps('2013-12-23 00:00:00' , '2013-12-28 00:00:00', '', '', 0); # User@Host: Toni[Toni] @ myhost [myipaddress] # Query_time: 135.576004 Lock_time: 0.000089 Rows_sent: 0 Rows_examined: 0 SET timestamp=1387898315; call mydb.mystps('2013-12-23 00:00:00' , '2013-12-28 00:00:00', '', '', 0); # User@Host: Toni[Toni] @ myhost [myipaddress] # Query_time: 133.167910 Lock_time: 0.000087 Rows_sent: 0 Rows_examined: 0 SET timestamp=1387898315; call mydb.mystps('2013-12-01 00:00:00' , '2014-01-01 00:00:00', '', '', 0); # User@Host: Toni[Toni] @ myhost [myipaddress] # Query_time: 137.359866 Lock_time: 0.000088 Rows_sent: 0 Rows_examined: 0 How to repeat: 1. Set the slow query variables +---------------------+----------------------------------------+ | Variable_name | Value | +---------------------+----------------------------------------+ | log_slow_queries | ON | | long_query_time | 5 | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | slow.queries.log | +---------------------+----------------------------------------+ 2. Create MyISAM table 3. Insert 3M rows 3. Select * from the table 4. Quickly open another session and do update all on the same table 5. Quickly open another session and try to select a few rows from the same table 6. Look at the slow query for Lock_time Suggested fix: Lock_time should reflect true waiting time to obtain a lock. "The lock_time is the time spent waiting for a lock" as described in MySQL forum: http://forums.mysql.com/read.php?35,377522,378908