…in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL…key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size used by MRR can be controlled by the variable read_rnd_buffer_size…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size…in the manual either for MariaDB or MySQL, but you need to appropriately increase read_rnd_buffer_size/mrr_buffer_size…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been 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… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
…how exactly read_rnd_buffer_size works as well as which layer it corresponds to…to fixed size (basically everything but BLOB/TEXT) MySQL can use read_rnd_buffer to optimize data retrieval – As data is sorted by the key…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
…to you, how big the indexes are on disk, and so on. Raise the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to…to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key_buffer_size…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…buffers which are allocated at start and always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size…buffer_size bytes of memory if done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…buffer-size=16M key-buffer-size…optimizer-prune-level=0 partition=ON port=3306 read-buffer-size=512K read-rnd-buffer-size=1M skip-host-cache skip-name-resolve sort-buffer-size…
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
…to be set for optimal load speed. Depending on load type MyISAM may benefit from bulk_insert_tree_size increase myisam_sort_buffer_size or key_buffer_size…buffer_pool_size and large innodb_log_file_size to perform load effectively Load Options There two main ways to load data in MySQL…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…in the error log after a crash: “It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size…to parse and optimize…mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | +—————–+ | 1 | +—————–+ 1 row in…
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…to IO alignment yourself. In this case offset is correct, but file system is unaware how to…
