… of node2) through Xtrabackup SST (state snapshot transfer). Node3 subsequently joins the cluster with node1 and node2 in its gcomm:// (since… restarted (and there are others when it comes up) it joins the cluster seamlessly. I would like to note here that… (we are adding one in 5.5.31), looking for optimizations with HAProxy to account for high connection rates, and using…
Post: Percona XtraDB Cluster (PXC) in the real world: Share your use cases!
June 17, 2013 By Leave a Comment
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… SQL can produce an infinite variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can… amount of engineer-years spent developing and testing the query optimizer. The developers have to make a decision about which query… it helped? When I did not use the STRAIGHT_JOIN, the query optimizer reordered the tables. It seemed to prefer an index…
Post: Webinar: SQL Query Patterns, Optimized
May 20, 2013 By Leave a Comment
… built-in methods to analyze them for optimal query efficiency. The discussion will cover optimizer reports, query profiling, and session status to measure performance. The query patterns will include: Exclusion Join Random Selection…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…. The SSB The SSB tests a database’s ability to optimize queries for a star schema. A star schema presents some unique challenge to the database optimizer. The SSB benchmark consists of four sets of queries. Each…=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer_size=8M default_tmp_storage_engine=myisam tmpdir=/dev…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… this configuration B. The next one contained four SSD-specific optimizations based partially on some earlier work that I’d done…’d determined that configuration C appeared to produce the most optimal results, I moved on to reviewing FlashCache performance versus that… = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size…
Post: Keynotes, BOFs, and the Community Networking Reception at Percona Live MySQL Conference and Expo
April 15, 2013 By 1 Comment
… 10.0 & What’s New With the Project” Sergei Petrunia, Optimizer Developer, and Colin Charles, Chief Evangelist, MariaDB, for Monty Program… order of the cloud and big data. If you can join us in Santa Clara for the Percona Live MySQL Conference…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… is one additional join optimization in MariaDB that I would like to quickly explain here, and that is Hash Joins. Hash Join As I…-memory workload Now let’s see how effective are the join optimizations when the workload fits entirely in memory. For the purpose… Hash Join and the Key-ordered Scan are disabled. IO bound workload Now let’s see how effective are the join optimizations when…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…=on’ optimizer_switch=’mrr_cost_based=off’ mrr_buffer_size=32M optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=4 join_buffer_size=32M join_buffer…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… one join order takes an hour with another, and the optimizer chose the poorer of the two join orders. Why is one join order…. Let’s start with the MySQL query optimizer. The optimizer tries to choose the best join order based on its cost metric; it… know why one join order is faster than the other. But why didn’t the optimizer know this, too? The optimizer does know…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used… hypothesis that most users have joins with few tables, – it is not clear how far from optimal plans do we get by… things are likely to get even better handling joins of many tables as optimizer heuristics are improved so much higher search depths…
