One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though fixing… too much in this case throttling by having relatively short queries and introducing “sleeps” between them can be a good idea…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 6 Comments
… best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. Low values, such… further I found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection that there were… compatibility. In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are improved…
Post: Diamond Keynote Panel, BOFs, Lightning Talks, and McAfee and AOL Sponsorships
April 2, 2012 By 1 Comment
… entitled “Future Perfect: The Road Ahead for MySQL” which will feature a panel of MySQL industry leaders, including: Sundar Raghavan, director product… to enjoy some quality face time. Topics include “Query Optimization”, “MySQL and Ruby on Rails”, “MySQL in the Cloud”, and much more. We also…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… is targeted at the join related optimizations introduced in the optimizer. These optimizations are available in both MySQL 5.6 and MariaDB 5.5… made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched…_size set to 6M, the query time for MySQL 5.6 becomes approximately equal to that of MySQL 5.5. MariaDB 5.5…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… MySQL 5.6, the optimizer chooses the wrong query execution plan. Let’s take a look at what are the good and bad query… during cold query runs the optimizer would switch to using plan ‘a’, which does not involve MRR, and the query time for MySQL 5… in query times between MySQL 5.5 and MySQL 5.6/MariaDB 5.5 This is another area of improvement in the optimizer, as…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one… post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally…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
… and MySQL 5.6 optimizer improvements, and both of them look very good indeed. Peter will present the results in an “optimizer standoff… the server much faster with the binary log enabled Faster queries through speedier joins, faster subqueries, and elimination of useless tables… fix, and MySQL 5.6 offers microsecond timestamp support, as well as a lot of optimizer improvements. Even the standard MySQL 5.5…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… of MySQL‘s join optimizer is N!. The goal of this choice was to provide a conservative limit that guarantees that any query can be optimized… the change to plan pruning in MySQL 5.6. Ideally, the server should analyze the query and should determine the search depth…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… indicator that it may be a good opportunity to run OPTIMIZE and thus reclaim some hard disk space? Taking real-world…/lib/mysql/databasename # ls -lh tablename* -rw-rw—- 1 mysql mysql 8.7K 2010-08-05 16:31 tablename.frm -rw-rw—- 1 mysql mysql 58G 2012-04-24 13:41 tablename.ibd On disk vs the information_schema query, there is around…
