Horton

Thread

Commits

  1. Increase hash_mem_multiplier default to 2.0.

  1. Time to increase hash_mem_multiplier default?

    Peter Geoghegan <pg@bowt.ie> — 2022-01-17T00:28:03Z

    The current hash_mem_multiplier default is 1.0, which is a fairly
conservative default: it preserves the historic behavior, which is
that hash-based executor nodes receive the same work_mem budget as
sort-based nodes. I propose that the default be increased to 2.0 for
Postgres 15.

Arguments in favor of artificially favoring hash-based nodes like this
were made when hash_mem_mutiplier went in. The short version goes like
this:

The relationship between memory availability and overall
performance/throughput has very significant differences when we
compare sort-based nodes with hash-based nodes. It's hard to make
reliable generalizations about how the performance/throughput of
hash-based nodes will be affected as memory is subtracted, even if we
optimistically assume that requirements are fairly fixed. Data
cardinality tends to make the picture complicated, just for starters.
But overall, as a general rule, more memory tends to make everything
go faster.

On the other hand, sort-based nodes (e.g., GroupAggregate) have very
predictable performance characteristics, and the possible upside of
allowing a sort node to use more memory is quite bounded. There is a
relatively large drop-off when we go from not being able to fit
everything in memory to needing to do an external sort. But even that
drop-off isn't very big -- not in absolute terms. More importantly,
there is hardly any impact as we continue to subtract memory (or add
more data). We'll still be able to do a single pass external sort with
only a small fraction of the memory needed to sort everything in
memory, which (perhaps surprisingly) is mostly all that matters.

The choice of 2.0 is still pretty conservative. I'm not concerned
about making hash nodes go faster (or used more frequently) -- at
least not primarily. I'm more worried about avoiding occasional OOMs
from sort nodes that use much more memory than could ever really make
sense. It's easy to demonstrate that making more memory available to
an external sort makes just about no difference, until you give it all
the memory it can make use of. This effect is reliable (data
cardinality won't matter, for example). And so the improvement that is
possible from giving a sort more memory is far smaller than (say) the
improvement in performance we typically see when the optimizer
switches from a hash aggregate to a group aggregate.

-- 
Peter Geoghegan

  2. Re: Time to increase hash_mem_multiplier default?

    John Naylor <john.naylor@enterprisedb.com> — 2022-01-19T19:31:51Z

    On Sun, Jan 16, 2022 at 7:28 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> The current hash_mem_multiplier default is 1.0, which is a fairly
> conservative default: it preserves the historic behavior, which is
> that hash-based executor nodes receive the same work_mem budget as
> sort-based nodes. I propose that the default be increased to 2.0 for
> Postgres 15.

I don't have anything really profound to say here, but in the last
year I did on a couple occasions recommend clients to raise
hash_mem_multiplier to 2.0 to fix performance problems.

During this cycle, we also got a small speedup in the external sorting
code. Also, if the "generation context" idea gets traction, that might
be another reason to consider differentiating the mem settings.
--
John Naylor
EDB: http://www.enterprisedb.com

  3. Re: Time to increase hash_mem_multiplier default?

    Peter Geoghegan <pg@bowt.ie> — 2022-02-15T06:32:43Z

    On Wed, Jan 19, 2022 at 11:32 AM John Naylor
<john.naylor@enterprisedb.com> wrote:
> I don't have anything really profound to say here, but in the last
> year I did on a couple occasions recommend clients to raise
> hash_mem_multiplier to 2.0 to fix performance problems.

I would like to push ahead with an increase in the default for
Postgres 15, to 2.0.

Any objections to that plan?

-- 
Peter Geoghegan

  4. Re: Time to increase hash_mem_multiplier default?

    Justin Pryzby <pryzby@telsasoft.com> — 2022-02-15T16:17:08Z

    On Mon, Feb 14, 2022 at 10:32:43PM -0800, Peter Geoghegan wrote:
> On Wed, Jan 19, 2022 at 11:32 AM John Naylor
> <john.naylor@enterprisedb.com> wrote:
> > I don't have anything really profound to say here, but in the last
> > year I did on a couple occasions recommend clients to raise
> > hash_mem_multiplier to 2.0 to fix performance problems.
> 
> I would like to push ahead with an increase in the default for
> Postgres 15, to 2.0.
> 
> Any objections to that plan?

The only reason not to is that a single-node hash-aggregate plan will now use
2x work_mem.  Which won't make sense to someone who doesn't deal with
complicated plans (and who doesn't know that work_mem is per-node and can be
used multiplicitively).  I don't see how one could address that other than to
change hash_mem_multiplier to nonhash_mem_divider.

It'll be in the release notes, so should be fine.

-- 
Justin

  5. Re: Time to increase hash_mem_multiplier default?

    Peter Geoghegan <pg@bowt.ie> — 2022-02-17T02:42:45Z

    On Tue, Feb 15, 2022 at 8:17 AM Justin Pryzby <pryzby@telsasoft.com> wrote:
> The only reason not to is that a single-node hash-aggregate plan will now use
> 2x work_mem.  Which won't make sense to someone who doesn't deal with
> complicated plans (and who doesn't know that work_mem is per-node and can be
> used multiplicitively).

Hearing no objections, I pushed a commit to increase the default to 2.0.

Thanks
--
Peter Geoghegan