I just did some similar testing. If you have a known small query, it’s worth doing a SET SESSION sort_buffer_size=$smallval; SELECT foo;
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… more sequential, by having a buffer in between where secondary key tuples are buffered and then sorted by the primary key values, and… primary key columns) Buffer each pk_column value fetched from step 1, and when the buffer is full sort them by pk_column… importance of the size of the buffer used for sorting the secondary key tuples. If the buffer size is large enough only a…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… w/ join_buffer_size=6M & mrr_buffer_size=6M MariaDB 5.5 Hash Join Disabled w/ join_buffer_size=4M & mrr_buffer_size=4M… 1 Sort_scan 1 1 1 1 1 1 The first obvious improvement is shown by the high numbers of Innodb_buffer… on both the join_buffer_size and the read_rnd_buffer_size/mrr_buffer_size and both of these buffers should be increased appropriately…
Comment: Predicting how long data load would take
March 26, 2012 By 12 Comments
… trick for really huge, hash-partitioned InnoDB tables is to sort by the partition rule, then by the primary key. The…, load in PK order. This will result in very nice buffer pool behavior. We loaded a 1.5 billion row table…
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… is a per session buffer that is allocated every time it is needed. The problem with the sort buffer comes from the way… of the sort_buffer. It is my understanding that the sort_buffer is used when no index are available to help the sorting so I… the number of sort merge passes. For the small values of the sort buffer size, below 440KB, there are many sort merge passes and…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… “sort%”; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | Sort_merge_passes | 10 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +——————-+——-+ 4 rows in set (0.00 sec) set sort_buffer… get rid of sort merge passes – may be it is sort merge which is inefficient with large sort_buffer_size ? mysql> set sort_buffer_size=100000000…
Post: What exactly is read_rnd_buffer_size
July 24, 2007 By 4 Comments
… of pointers from sort_buffer (just enough so all rows fit in read_rnd_buffer as they are read) and sorts them by row…. The read_rnd_buffer_size is important (optimization works in following conditions): Row pointers are stored in the sort_buffer, not the whole…_length_for_sort_data) data should be stored in sort buffer and sort file so there would be no need for read_rnd_buffer, if…
Post: Are larger buffers always better ?
June 6, 2006 By 9 Comments
… provide quite unexpected improvements. sort_buffer_size – recently I worked with case which was running much faster with 32K sort_buffer_size, compared to 32M. The problem was memory allocation – sorting was used in corelated subquery…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are… MySQL to use Large Pages for allocating Innodb Buffer Pool and few other buffers, which may have other performance benefits as well…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… quite often want to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size for example) are allocated to… corresponding buffers are allocated only as big as needed (key_buffer_size). There are many examples of this. What about join_buffer_size… to the question about how the buffer is allocated. Its meaning is actually “minimum join buffer size.” Here’s the code, in…
