… problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing these does help in many… too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea…
Comment: Joining many tables in MySQL - optimizer_search_depth
May 12, 2012 By 6 Comments
… in case of particular queries but surely cannot be an all-around answer to inefficiencies of the optimizer, that should be doing…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch… the query times. But I also had to turn on MRR because BKA uses the MRR interface. Note also the additional optimizer switches and variables in case of MariaDB 5.5. You can read more about these variables here. The query used…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… was taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need… making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used… best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. Low values, such…
Post: Diamond Keynote Panel, BOFs, Lightning Talks, and McAfee and AOL Sponsorships
April 2, 2012 By 1 Comment
… topic interests to enjoy some quality face time. Topics include “Query Optimization”, “MySQL and Ruby on Rails”, “MySQL in the Cloud”, and…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the optimizer is choosing the query execution plan, is not sufficiently tuned and it is recommended to turn this off. The query used… So during cold query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and the query time for… improvement in the optimizer, as it is clearly a part of the optimizer‘s job to select the best query execution plan. I…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… 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 Pushdown (ICP). Its available in both MySQL…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
…? At the time when I implemented the so-called “greedy optimizer“, I did some experiments to determine what is a reasonable… join optimizer is N!. The goal of this choice was to provide a conservative limit that guarantees that any query can be optimized… in MySQL 5.6. Ideally, the server should analyze the query and should determine the search depth based on some syntactic…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
….5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even though with general… | Rows_sent | Rows_examined | Rows_read | +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | 10219 | root | localhost | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0… MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show processlist…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
… very good indeed. Peter will present the results in an “optimizer standoff” talk at the upcoming MySQL conference, and Timour and… the server much faster with the binary log enabled Faster queries through speedier joins, faster subqueries, and elimination of useless tables… offers microsecond timestamp support, as well as a lot of optimizer improvements. Even the standard MySQL 5.5 offers pluggable authentication…
