… ， I’ve tried your example “the affect gap locks to SELECT … FOR UPDATE ” on my server ,and my results are as… affected (0.00 sec) root@localhost:test 14:35:29>select * from t; +——+ | i | +——+ | 21 | | 30 | | 26 | +——+ 3 rows in set… 0 27638 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… a SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT… a DELETE statement: transaction1 > SELECT * FROM t; +——+ | age | +——+ | 21 | | 25 | | 30 | +——+ Start a transaction and delete the record 25: transaction1 > START TRANSACTION…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… query will roughly be evaluated in following steps, without MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x… is recommended to turn this off. The query used is: select c_custkey, c_name, sum(l_extendedprice * (1 – l_discount… columns) involves, reading an index record, and then using the PK column value in the index record to make a lookup in…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as: KEY…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…; +———-+ | count(*) | +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ | test | +——+ 1 row in set (0.00… not null; Query OK, 1 row affected (0.65 sec) Records: 1 Duplicates: 0 Warnings: 0 SESSION2: root@ubuntu:~/dump# tail…
Comment: Finding out largest tables on MySQL Server
March 30, 2012 By 24 Comments
… table has 5,000,000 records. The size of it is 9GB ( has serialized arrays ). An select like SELECT `content` FROM `table` WHERE…
Post: Percona Live MySQL Conference & Expo Was A Great Event
April 17, 2012 By 2 Comments
Thanks to all of our sponsors, speakers, speaker selection committee, event staff, and especially the attendees for making last … percona.tv for your viewing pleasure anytime. Sheeri Cabral also recorded some of the breakout sessions, so watch Planet MySQL for…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… command with approx 3 million records is taking too long and all my transactions are locked. select count(1),field1 from Table…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… compared to what pure SELECT would give. This by the way applies to SELECT .. LOCK IN SHARE MODE and SELECT … FOR UPDATE as…_mode X locks rec but not gap waiting Record lock, heap no 180 PHYSICAL RECORD: n_fields 2; compact format; info bits… localhost root Sending data insert into test select * from sample ——– As you can see INSERT… SELECT has a lot of lock structs…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… the active master’s buffer pool with the following query: select table_schema, table_name, page_type, count(*) from information_schema… passive server, with both a mixed workload from my mirrored SELECT statements and with the “pure” replication workload. I totaled the… SELECTs do is keep the insert buffer in a production steady-state. The buffered records are forced to be merged by the SELECTs…
