maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hello,

index_merge uses @@sort_buffer_size as a guide of how much space it can use for
sorting. I think there is an issue with the way it is done.

Before index_merge was introduced, @@sort_buffer_size was used only by
filesort (also for couple of other cases like GROUP_CONCAT with sorting, but I
consider them to be too rare to be of practical importance).  This allowed the
following:

1. One can set @@sort_buffer_size to control how much space is to be allocated

2. SHOW STATUS will produce these numbers:

  Sort_merge_passes
  Sort_range
  Sort_scan

Sort_range+Sort_scan  gives the total number of filesort invocations.
Sort_merge_passes is the number of merges. One can get average amount of merges
per sort operation by calculating:

  Sort_merge_passes / (Sort_range + Sort_scan)      (1)

If the number is high, then it means that it could be useful to increase 
@@sort_buffer_size.

Now, if we take index_merge into the picture, the counters are much less
useful. index_merge causes Sort_merge_passes to be incremented, but we
dont have a counter for index_merge operations, so formula (1) becomes invalid.

Possible solutions are:

Option#1:  introduce a counter Sort_index_merge_count (name subject to
discussion), which will mean "number of index_merge sort union/intersection
scans".

Option#2: make it so that index_merge does not cause Sort_merge_passes to be
incremented. I think that in this case we'll need a separate variable to
control index_merge's buffer size. it could have @@sort_buffer_size value as
default.


Any comments?

BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog