Horton

Re: hash_search_with_hash_value is high in "perf top" on a replica

Matthias van de Meent <boekewurm+postgres@gmail.com>

View thread
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Ants Aasma <ants.aasma@cybertec.at>, Dmitry Koterov <dmitry.koterov@gmail.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2025-02-04T19:03:10Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. doc: Fix pg_buffercache_evict() title

  2. Fix several hash functions that were taking chintzy shortcuts instead of

On Sat, 1 Feb 2025 at 16:55, Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2025-02-01 15:43:41 +0100, Ants Aasma wrote:
> > On Fri, Jan 31, 2025, 15:43 Andres Freund <andres@anarazel.de> wrote:
> >
> > > > Maybe it's a red herring though, but it looks pretty suspicious.
> > >
> > > It's unfortunately not too surprising - our buffer mapping table is a
> > > pretty
> > > big bottleneck.  Both because a hash table is just not a good fit for the
> > > buffer mapping table due to the lack of locality and because dynahash is
> > > really poor hash table implementation.
> > >
> >
> > I measured similar things when looking at apply throughput recently. For
> > in-cache workloads buffer lookup and locking was about half of the load.
> >
> > One other direction is to extract more memory concurrency. Prefetcher could
> > batch multiple lookups together so CPU OoO execution has a chance to fire
> > off multiple memory accesses at the same time.
>
> I think at the moment we have a *hilariously* cache-inefficient buffer lookup,
> that's the first thing to address. A hash table for buffer mapping lookups imo
> is a bad idea, due to loosing all locality in a workload that exhibits a *lot*
> of locality. But furthermore, dynahash.c is very far from a cache efficient
> hashtable implementation.

In case you might be interested, I've sent a new patch with a new
approach to reducing the buffer lookup table's memory in [0], which
attempts to create a more cache-efficient hash table implementation.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

[0] https://www.postgresql.org/message-id/CAEze2WiRo4Zu71jwxYmqjq6XK814Avf2-kytaL6n%3DBreZR2ZbA%40mail.gmail.com