On 01/19/2011 02:32 PM, Sergey Petrunya wrote:

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?

IMHO it does not make sense to think about it BEFORE 5.3 beta is out. Regards, Igor.

BR Sergey