… to lock 1638400 rows, making it less than 3 bits per locked row; Now lets see how much exclusinve locks take: mysql> begin; Query OK, 0 rows… of memory: History list length 5 Total number of lock structs in row lock hash table 6092 LIST OF TRANSACTIONS FOR EACH SESSION… | +———-+ 1 row in set (4.44 sec) Looking at SHOW INNODB STATUS we can see: Total number of lock structs in row lock hash…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…: 0 node1 mysql> SHOW ENGINE INNODB STATUS\G … ———— TRANSACTIONS ———— … —TRANSACTION 83B, ACTIVE 50 sec 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 3972, OS… to get lock; try restarting transaction node1 mysql> select * from autoinc; +—+——-+ | i | j | +—+——-+ | 1 | node3 | | 4 | node2 | | 7 | node2 | +—+——-+ 3 rows in set…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
…: mysql> commit; Query OK, 0 rows affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in share mode; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) mysql> select * from tst…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… the query accessed, the length of time query waited for row locks, etc). So you can see there is a plethora of… enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might… Killed: 0 # Query_time: 0.117904 Lock_time: 0.002886 Rows_sent: 1 Rows_examined: 655360 Rows_affected: 0 Rows_read: 655361 # Bytes_sent: 68…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… LOCK to temporary make MySQL…mysql> show processlist; +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… as well as inevitable application specific scalability restrictions, such as row level locks. There is also a load aspect – “random arrivals” tell… ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S… separately through number of IOPS) but it also can be row level locks, etc. The ratio between Wait Time and CPU time…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213… only actual rows which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains… Killed: 0 # Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 2 # Bytes_sent: 75…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…_time: 0.503016 Lock_time: 0.000048 Rows_sent: 56 Rows_examined: 1113 LOGGING OF THE REPLICATED STATEMENTS Normally MySQL will not write into… data from storage. InnoDB_rec_lock_wait is the time (in seconds) query had to wait on row locks. InnoDB_queue_wait is… [] # Thread_id: 4 # Query_time: 0.503016 Lock_time: 0.000048 Rows_sent: 56 Rows_examined: 1113 # QC_Hit: No Full_scan: No…
Post: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
… available here and from the Percona Software Repositories). Based on MySQL 5.1.66, including all the bug fixes in it… transactions downgrade the requested exclusive (X) row locks to shared (S) locks, these S locks prevent X locks from being taken and block the real… not to take any row locks. Bug fixed #1064326 (Mark Callaghan, Laurynas Biveinis). Fake changes were increasing the changed row and userstat counters…
Post: Announcing Percona Server 5.5.28-29.2
December 7, 2012 By 2 Comments
… available here and from the Percona Software Repositories). Based on MySQL 5.5.28, including all the bug fixes in it… transactions downgrade the requested exclusive (X) row locks to shared (S) locks, these S locks prevent X locks from being taken and block the real… not to take any row locks. Bug fixed #1064326 (Mark Callaghan, Laurynas Biveinis). Fake changes were increasing the changed row and userstat counters…
