Re: WIP: dynahash replacement for buffer table

Hi,

On 2014-10-14 09:30:58 -0400, Robert Haas wrote:
> A few years ago I started working on a concurrent hash table for
> PostgreSQL.  The hash table part of it worked, but I never did
> anything with it, really.  Amit mentioned to me earlier this week that
> he was seeing contention inside the dynahash machinery, which inspired
> me to go back and update the patch.  I took the basic infrastructure
> from before and used it to replace the buffer table.  Patch is
> attached.

So. I ran a quick tests on a larger x86 machine. 4xE5-4620, 256GB RAM.

The hotel wifi is too flaky to get detailed results, but some tasty
bits.

The test I used is readonly pgbench on a scale 5000 DB - about 73GB. I'm
benchmarking chash ontop my LW_SHARED and StrategyGetBuffer()
optimizations because otherwise the bottleneck is completely elsewhere.

When using shared_buffers = 96GB there's a performance benefit, but not
huge:
master (f630b0dd5ea2de52972d456f5978a012436115e):               153621.8
master + LW_SHARED + lockless StrategyGetBuffer():              477118.4
master + LW_SHARED + lockless StrategyGetBuffer() + chash:      496788.6
master + LW_SHARED + lockless StrategyGetBuffer() + chash-nomb: 499562.7

But with shared_buffers = 16GB:
master (f630b0dd5ea2de52972d456f5978a012436115e):               177302.9
master + LW_SHARED + lockless StrategyGetBuffer():              206172.4
master + LW_SHARED + lockless StrategyGetBuffer() + chash:      413344.1
master + LW_SHARED + lockless StrategyGetBuffer() + chash-nomb: 426405.8

All the numbers here -P5 output when it looks like it has stabilized -
it takes a couple minutes to get to that point so pgbench runs would
have to be really long to not be skewed by the startup. I don't think my
manual selection of measurements matters much at this scale of
differences ;)

I had to play with setting max_connections+1 sometimes to get halfway
comparable results for master - unaligned data otherwise causes wierd
results otherwise. Without doing that the performance gap between master
96/16G was even bigger. We really need to fix that...

This is pretty awesome.

Greetings,

Andres Freund

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