Re: [HACKERS] Proposal for CSN based snapshots

On Mon, Dec 4, 2017 at 6:07 PM, Alexander Kuzmenkov <
a(dot)kuzmenkov(at)postgrespro(dot)ru> wrote:

> Performance on pgbench tpcb with subtransactions is now slightly better
> than master. See the picture 'savepoints2'. This was achieved by removing
> unnecessary exclusive locking on CSNLogControlLock in SubTransSetParent.
> After that change, both versions are mostly waiting on XidGenLock in
> GetNewTransactionId.
>
> Performance on default pgbench tpcb is also improved. At scale 500, csn is
> at best 30% faster than master, see the picture 'tpcb500'. These
> improvements are due to slight optimizations of GetSnapshotData and
> refreshing RecentGlobalXmin less often. At scale 1500, csn is slightly
> faster at up to 200 clients, but then degrades steadily: see the picture
> 'tpcb1500'. Nevertheless, CSN-related code paths do not show up in perf
> profiles or LWLock wait statistics [1]. I think what we are seeing here is
> again that when some bottlenecks are removed, the fast degradation of
> LWLocks under contention leads to net drop in performance. With this in
> mind, I tried running the same benchmarks with patch from Yura Sokolov [2],
> which should improve LWLock performance on NUMA machines. Indeed, with this
> patch csn starts outperforming master on all numbers of clients measured,
> as you can see in the picture 'tpcb1500'. This LWLock change influences the
> csn a lot more than master, which also suggests that we are observing a
> superlinear degradation of LWLocks under increasing contention.
>

These results look promising for me. Could you try benchmarking using more
workloads including read-only and mixed mostly-read workloads?
You can try same benchmarks I used in my talk about CSN in pgconf.eu [1]
slides 19-25 (and you're welcome to invent more benchmakrs yourself)

Also, I wonder how current version of CSN patch behaves in worst case when
we have to scan the table with a lot of unique xid (and correspondingly
have to do a lot of csnlog lookups)? See [1] slide 18. This worst case
was significant part of my motivation to try "rewrite xid with csn"
approach. Please, find simple extension I used to fill table with random
xmins in the attachment.

1. https://www.slideshare.net/AlexanderKorotkov/the-future-is-csn

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company