Re: tuplesort memory usage: grow_memtuples

On 17 August 2012 00:26, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:

> This patch works by not doubling the size of state->memtupsize when
> available memory is less than half of allowed memory (i.e. we are one
> call to grow_memtuples() away from reporting ). Rather, in that
> situation, it is set to a size that extrapolates the likely size of
> the total amount of memory needed in a way that is quite flawed, but
> likely to work well for the pass-by-value Datum case. Now, on the face
> of it, this may appear to be a re-hashing of the question of "how
> paranoid do we need to be about wasting memory in memory-constrained
> situations - should we consider anything other than a geometric growth
> rate, to be parsimonious with memory at the risk of thrashing?".
> However, this is not really the case, because this is only a single,
> last-ditch effort to avoid a particularly undesirable eventuality. We
> have little to lose and quite a bit to gain. A cheap guestimation
> seems reasonable.

This is a very useful optimisation, for both the low and the high end.

The current coding allows full use of memory iff the memory usage is
an exact power of 2, so this patch will allow an average of 50% and as
much as 100% gain in memory for sorts. We need to be careful to note
this as a change on the release notes, since users may well have set
work_mem to x2 what was actually needed to get it to work - that means
most users will see a sudden leap in actual memory usage, which could
lead to swapping in edge cases.

I notice that cost_sort doesn't take into account the space used for
sorting other than tuple space so apparently requires no changes with
this patch. ISTM that cost_sort should be less optimistic about memory
efficiency than it is.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services