…) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is disabled during these benchmark runs and that the…’ optimizer_switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=8 join_buffer_size=6M mrr_buffer_size… desc, o_orderdate LIMIT 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits…
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… same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the…5) optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=4M (only on MySQL 5.6) mrr_buffer_size=4M …_name, c_address, c_comment order by revenue desc LIMIT 20; In-memory workload Now let’s see …
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size…queries. There can be mutiple key caches. Multiple key caches can be created to accomodate query executions Query Parsing and optimization…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…cache size, but size range This is minor one. Unlike you would typically see Falcon uses minimum and maximum sizes for row cache… index covered queries is great optimization for wide range of queries. [+] Ordered data … transaction. [-] Isolation Modes So far limited amount of isolation modes is …
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…query_alloc_block_sizeblogpercona.commanual query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query_cache_sizeblogpercona.commanual query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… stepping : 11 cpu MHz : 1995.022 cache size : 4096 KB physical id : 1 siblings :…query, and index optimization Analyzing and optimizing a server’s table and index structures, and the queries…query log output. The stock MySQL server’s limitation of one-second granularity makes it hide problem queries…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… very hard to prove, there actually is a very limited use for the statistics from which the ratio is ….9%” or “if your key cache hit ratio is low, your key_buffer_size is too small.” So here’s … the query‘s execution time. Alas, that correlation fools us into thinking it’s a cause, and we optimize-by-…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…. bulk_insert_buffer_size – Used by MyISAM tables only to optimize bulk inserts (multiple value… tables. key_cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache/Key Buffer replacement algorithm… and Innodb. Some SQL level items as Query Cache also could use this feature. low_…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
…) – 1 As with any performance optimization effort, you will want to optimize for the common case. If you…the number of threads that can enter InnoDB is limited by innodb_thread_concurrency (which is why these…_max_dirty_pages_pct=90 max_connections=3000 query_cache_size=0 skip-name-resolve table_cache…
