…MySQL configuration. MySQL…performance is using partitioning to reduce the size of the B+tree, having multiple buffer pool instances to reduce contention problems caused by buffer…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… compared to MySQL 5.5 While with join_buffer_size set to 6M and read_rnd_buffer_size set to 6M, the query time for MySQL 5.6…_buffer_size is set to 6M. While the lowest query time for MariaDB 5.5 is when both the Hash Join and the Key… MariaDB or MySQL, but you need to appropriately increase read_rnd_buffer_size/mrr_buffer_size because these have an impact on MRR performance, and BKA…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… is the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used by MRR can…_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based=off’ read_rnd_buffer_size=4M (only on MySQL 5.6) mrr_buffer_size… reduced further when buffer size is set large enough so that the index tuples fit in the buffer. But there is no performance improvement when…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…key_buffer_sizeblogpercona.commanual key_cache_age_thresholdblogpercona.commanual key_cache_block_sizeblogpercona.commanual key_cache_division_limitblogpercona.commanual Key_read_requestsblogpercona.commanual Key_readsblogpercona.commanual Key…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
…. Raise the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to a number you’re comfortable with. Set key_buffer_size really big, and… good instrumentation in MySQL to guide your decisions. It is also not the be-all and end-all of MySQL performance, and people…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
… work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server… get these settings right other changes will most commonly offer only incremental performance improvements. key_buffer_size – Very important if you use MyISAM tables. Set up…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… perform on each database. To do the size comparison, I used the du utility: InnoDB file size …-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-size=64M local-infile=on lock-wait…size=64M transaction-isolation=READ-COMMITTED user=mysql wait-timeout=86400 To be continued You can now set…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…_b.key_buffer_size=28390400; | +———————————————+ 2 rows in set (2 min 31.68 sec) Pass this via SELECT INTO OUTFILE or pipe it to mysql… in set (0.00 sec) So going through complex or not multiple key cache creation exercise you probably wonder how much performance gains…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
…size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size = 64M key_buffer_size = 8M bulk_insert_buffer_size = 8M myisam_sort_buffer_size…
