Horton

Re: Improving connection scalability: GetSnapshotData()

Konstantin Knizhnik <k.knizhnik@postgrespro.ru>

View thread View on postgr.es
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: AJG <ayden@gera.co.nz>, pgsql-hackers@postgresql.org
Date: 2021-03-01T15:46:51Z
Lists: pgsql-hackers 

On 27.02.2021 20:40, AJG wrote:
> Hi,
>
> Greatly appreciate if you could please reply to the following questions as
> time allows.
>
> I have seen previous discussion/patches on a built-in connection pooler. How
> does this scalability improvement, particularly idle connection improvements
> etc, affect that built-in pooler need, if any?
>
>
> Same general question about an external connection pooler in general in
> Production? Still required to route to different servers but no longer
> needed for the pooling part. as an example.
>
>
> Many Thanks!
>

Connection pooler is still needed.
The patch for GetSnapshotData() mostly improves scalability of read-only 
queries and reduce contention for procarray lock.
But read-write upload cause contention for many other resources: 
relation extension lock, buffer locks, tuple locks and so on.

If you run pgbench at NUMA machine with hundreds of cores, then you will 
still observe significant degradation of performance with increasing 
number of connection.
And this degradation will be dramatic if you replace some non-uniform 
distribution of keys, for example Zipfian distribution.




>
>
>
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
>
>

Commits

  1. Try to unbreak 021_row_visibility.pl on mingw.

  2. Fix and test snapshot behavior on standby.

  3. Fix race condition in snapshot caching when 2PC is used.

  4. snapshot scalability: cache snapshots using a xact completion counter.

  5. Fix use of wrong index in ComputeXidHorizons().

  6. Make vacuum a bit more verbose to debug BF failure.

  7. snapshot scalability: Introduce dense array of in-progress xids.

  8. snapshot scalability: Move PGXACT->vacuumFlags to ProcGlobal->vacuumFlags.

  9. snapshot scalability: Move subxact info to ProcGlobal, remove PGXACT.

  10. snapshot scalability: Move PGXACT->xmin back to PGPROC.

  11. snapshot scalability: Don't compute global horizons while building snapshots.

  12. BRIN: Handle concurrent desummarization properly

  13. Track latest completed xid as a FullTransactionId.

  14. Rename VariableCacheData.nextFullXid to nextXid.

  15. snapshot scalability: Move delayChkpt from PGXACT to PGPROC.

  16. Report progress of CREATE INDEX operations