I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… a SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but… you execute SELECT * FROM id > 1000 FOR UPDATE twice, you expect to get the same value twice. To accomplish that, InnoDB locks… gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways: 1- Change…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… 100551 103011 77213 Innodb_pages_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select_scan 1 1… these lookups are performed in a single call to the storage engine and the counters Handler_read_key and Innodb_rows_read… and then to perform the MRR range scan on the PK. This causes the counters Handler_read_key and Innodb_rows_read…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… values by PK column and then performing the lookup, and then there are other possibilities like InnoDB doing read_ahead by noticing… 16164 15506 Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read 4127318 6718351 6611675 6707882 5479445 5527245 Select_scan 1…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… to get consistent backup for their Innodb tables without making database read only…. ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create…| +———-+ | 2359296 | +———-+ 1 row in set (1.73 sec) mysql> select * from C; +——+ | t | +——+ | test | +——+ 1 row …
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…innodb innodb_data_home_dir = innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb… and all my transactions are locked. select count(1),field1 from Table where REQUEST_…54.29 sec) Please help to improve performance and suggest if any config changes …
Comment: Should you move from MyISAM to Innodb ?
March 10, 2012 By 53 Comments
… that is) and that if not using InnoDB you aren’t too smart. However, InnoDB brings a lot of cons to the… you have a huge number of concurrent writes and selects and query performance must be consistently fast in this use case – you… your problems. In fact there are other engines better than InnoDB for certain usage patterns – if you go down this path…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
March 7, 2012 By 21 Comments
…, InnoDB engine. Our requirement is to insert data in same table and read from same table. insert into … SAME_TABLE select my… be reduce further? Is there anyway I can improve the performance? We can use OUTFILE method as suggested by Peter because…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
Everyone using Innodb tables probably got use to the fact Innodb tables perform non locking reads, meaning unless you use some modifiers such as LOCK IN SHARE MODE or FOR UPDATE, SELECT… tests to see how well it performs 
One more thing to keep into account – INSERT … SELECT actually performs read in locking mode and…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
…: 4 ref: const rows: 4092 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t1 type: eq_ref possible_keys: PRIMARY… only simple select query which should be executed without exclusive table locking. Even on 16 threads we’re getting performance worse than… t1 into InnoDB, and results: Threads Queries/sec 1 296 2 341 4 544 8 493 16 498 InnoDB both performs much better…
