… starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections…: NULL 3 rows in set (0.00 sec) Innodb Memory Usage Finally it is often helpful to check how much memory Innodb has…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…optimizer_switch=’batched_key_access=on’ join_buffer_size=6M read_rnd_buffer_size=6M Also… workload Now let’s see how effective are the join optimizations…buffer_size increased to 6M, the query time was ~300s while when both the join_buffer_size and the read_rnd_buffer_size/mrr_buffer_size were set to…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…, however if the buffer size is small as compared to the combined size of the secondary key tuples fetched,… ~11min to under a minute. The query time is reduced further when the buffer size is set to 4M….counts how many times the buffer used by MRR had to be reinitialized, because the buffer was small…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
…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 then measure its size…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size for example) are allocated to their full size…key_buffer_size). There are many examples of this. What about join_buffer_size? I saw a my.cnf with a 128M join_buffer_size…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…set (34.47 sec) Wow this is over 15 times better so you surely need to make sure your key_buffer_size is set to…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…to use Multiple Key Caches the question is how many to use, what sizes to allocate and how to map tables to… | SET GLOBAL test_a.key_buffer_size=39514112; | | SET GLOBAL test_b.key_buffer_size=28390400; | +———————————————+ 2 rows in set…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
… was installed with default settings. I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are… much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…to key_buffer_size + (read_buffer_size + sort_buffer_size…set sort_buffer_size to 128K — I can quite well connect and say “set @@sort_buffer_size := 1 * 1024 * 1024 * 1024;” and my sort_buffer_size…
