… the Meta Data Locking behavior prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5… table metadata lock | select * from test_2 order by id | | 8 | msandbox | localhost | test | Query | 180 | Waiting for table metadata lock | select * from test_2 order by id | | 9 | msandbox | localhost | test | Query | 169 | Waiting for table metadata lock | select * from test…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… compared to what pure SELECT would give. This by the way applies to SELECT .. LOCK IN SHARE MODE and SELECT … FOR UPDATE as well… or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 1 MySQL thread id…, locked 1 2539 lock struct(s), heap size 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… with an exclusive lock and the gaps between them with a shared gap lock. This lock doesn’t only affect to SELECT … FOR UPDATE. This is an example with a DELETE statement: transaction1 > SELECT * FROM… source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… Changed: 1 Warnings: 0 node3 mysql> commit; Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from autoinc; +—+——-+ | i | j | +—+——-+ | 1… what happened: node1 mysql> commit; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction node1 mysql> select * from autoinc; +—+——-+ | i…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… it works. Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock, it does for this statement… any SELECT statement to complete in order to complete locking tables. This means if you have workload which includes some very long SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… long does it take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count(i) from sample lock in share mode… bits per locked row; Now lets see how much exclusinve locks take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count… any locks is going to be faster than any of locking modes, however we can see locking overhead is not that large: mysql> select count…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… Last_errno: 0 Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000….k; mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select_type |…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat… seconds per 181243 select queries which is about 270us per select query. We can … ======= ======= ======= ======= ======= ======= # Exec time 8338s 1us 284s 5ms 13ms 298ms 185us # Lock time 71s 0 3ms 39us 54us 28us…
Post: Resyncing table on MySQL Slave
June 29, 2008 By 11 Comments
… you can do something like this on the master: LOCK TABLE tbl WRITE; SELECT * FROM table INTO OUTFILE ‘/tmp/tbl.txt’; DELETE… do following instead: mysql> rename table rep to rep_maint; Query OK, 0 rows affected (0.01 sec) mysql> select * from rep_maint into outfile ‘/tmp/rep.txt’; Query OK, 0 rows affected (0.01 sec) mysql> create table…
