…buffer_size=32M innodb_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k read_rnd_buffer_size=8M join_buffer_size…buffer_size=10G # Disabling symbolic-links is recommended to…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
…sort_buffer_size is one the MySQL parameters that is far from obvious to adjust. It is a per session buffer that is allocated every time it is…sort merge passes. For the small values of the sort buffer size, below 440KB, there are many sort…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…buffer_size which is not large enough to fit all key blocks from temporary table index…to read through here is the summary: key_buffer_size is important for MyISAM temporary tables performance to avoid OS writes OS Write cache is…
Comment: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…buffer_size is bigger to help queries that don’t use indexes and that do use some of the new optimizer features. query_cache_size RAM is allocated even if query_cache_type is 0. So useful to have some small…queries and/or low query rates. sort_buffer_size is much improved over the past 2M…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
…buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index…is no reason to increase sort_buffer_size even if you have 64GB of memory to…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
…size is very small. What is the biggest problem with UUID ? It is the fact inserts are going in random locations in index…is constantly hitting, this is true but at large date size it is way overturned by BTREE buffer. The most efficient approach here is not to…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… for sorting the secondary key tuples. If the buffer size is large enough only a single range lookup will be needed, however if the buffer size is small… ~11min to under a minute. The query time is reduced further when buffer size is set large enough so that the index tuples fit in the buffer…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…is good change to reduce the potential of blocked host errors though I think one could go with even higher default value. sort_buffer_size was decreased to 256K from 2M. This change should help many small sorts for which allocation of 2M for sort buffer…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…buffer pool which is large enough to fit complete table with index (but is a bit too small to…index does not fit to memory any more. Creating Index by sort is great Creating index by sort can provide over 20x performance improvement to…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_switch=’index_condition_pushdown=on’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ mrr_buffer_size… hash joins is data warehouse applications that need to run reporting queries that need to join on lookup tables which tend to be small mostly…
