…_free: 0 Auto_increment: 1638401 Create_time: 2006-07-12 07:31:51 Update_time: NULL Check_time: NULL Collation: utf8_general_ci… seconds longer (I repeated runs many time to ensure it is repeatable) so exclusive locks seems to be more expensive to set… memory: History list length 5 Total number of lock structs in row lock hash table 6092 LIST OF TRANSACTIONS FOR EACH SESSION…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… you can be locked sub-second. Even with innodb only this can greatly cut down on the lock time by syncing the frm…. Global read lock only held until myisam tables are dumped. We are researching into how we could further improve lock times here when non-transactional tables are used Almost no locking, if not using myisam tables…
Post: Analyzing Slow Query Table in MySQL 5.6
February 18, 2013 By 1 Comment
…_time, ‘%y%m%d %H:%i:%s’), CHAR(10), ‘# User@Host: ‘, user_host, CHAR(10), ‘# Query_time: ‘, TIME_TO_SEC(query_time), ‘ Lock_time: ‘, TIME_TO_SEC(lock_time), ‘ Rows_sent: ‘, rows_sent, ‘ Rows_examined: ‘, rows_examined, CHAR(10), ‘SET timestamp=’, UNIX_TIMESTAMP(start_time…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…_query_time is in microseconds and you can set it for example to 300000 which is 0.3s! # Query_time: 0.503016 Lock_time…: # User@Host: macko[macko] @ localhost [] # Thread_id: 4 # Query_time: 0.503016 Lock_time: 0.000048 Rows_sent: 56 Rows_examined: 1113 # QC… not used: # Time: 071031 20:03:16 # User@Host: macko[macko] @ localhost [] # Thread_id: 12 # Query_time: 0.026385 Lock_time: 0.000158 Rows…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 8338s 1us 284s 5ms 13ms 298ms 185us # Lock time 71s 0 3ms 39us 54us 28us 35us…% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 98 1753697 # Exec time 52 4337s 71us 466ms 2ms 12ms 7ms 176us # Lock time 96 69s 17us 3ms 39us 49us…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… the impact of FLUSH TABLES WITH READ LOCK can be quite large because of the time it may take to complete this… it works. Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock, it does for this statement… reason (other than code design) for read lock to wait for other read lock in order to be set. As a workaround…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…` int(10) unsigned default NULL, PRIMARY KEY (`tracker_id`,`username`,`time_id`), KEY `block_id` (`block_id`) ) ENGINE=InnoDB Table has… in set (0.00 sec) and extended stats: # Query_time: 17.765369 Lock_time: 0.000137 Rows_sent: 1 Rows_examined: 11569733 Rows… # InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 35584 You see that time returned to appropriate…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…: 64914 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows…: 65005 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 0.000323 Lock_time: 0.000095 Rows_sent: 0 Rows_examined: 0 Rows…: 65144 Schema: sbtest Last_errno: 0 Killed: 0 # Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… when you have long enough ACTIVE transactions which have some locks, like in this case: MySQL thread id 2059, query id… same rows it will have to wait… possibly until innodb_lock_wait_timeout elapses and when it completes with error. Second… to see transactions ACTIVE for very long time at all but the ones having locks is really the killer. If you can…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
… can’t contain query execution information such as execution and lock times and number of rows examined. After this patch is applied… look like this: # User@Host: root[root] @ localhost [] # Query_time: 0.000652 Lock_time: 0.000109 Rows_sent: 1 Rows_examined: 1 SELECT… WHERE name=’baouong’; # User@Host: root[root] @ localhost [] # Query_time: 0.001943 Lock_time: 0.000145 Rows_sent: 0 Rows_examined: 0 INSERT…
