Horton

Re: Improving connection scalability: GetSnapshotData()

David Rowley <dgrowleyml@gmail.com>

View thread View on postgr.es
From: David Rowley <dgrowleyml@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2020-04-05T12:05:12Z
Lists: pgsql-hackers

Attachments

On Sun, 1 Mar 2020 at 21:47, Andres Freund <andres@anarazel.de> wrote:
> On 2020-03-01 00:36:01 -0800, Andres Freund wrote:
> > conns   tps master            tps pgxact-split
> >
> > 1       26842.492845            26524.194821
> > 10      246923.158682           249224.782661
> > 50      695956.539704           709833.746374
> > 100     1054727.043139          1903616.306028
> > 200     964795.282957           1949200.338012
> > 300     906029.377539           1927881.231478
> > 400     845696.690912           1911065.369776
> > 500     812295.222497           1926237.255856
> > 600     888030.104213           1903047.236273
> > 700     866896.532490           1886537.202142
> > 800     863407.341506           1883768.592610
> > 900     871386.608563           1874638.012128
> > 1000    887668.277133           1876402.391502
> > 1500    860051.361395           1815103.564241
> > 2000    890900.098657           1775435.271018
> > 3000    874184.980039           1653953.817997
> > 4000    845023.080703           1582582.316043
> > 5000    817100.195728           1512260.802371
> >
> > I think these are pretty nice results.

FWIW, I took this for a spin on an AMD 3990x:

# setup
pgbench -i postgres

#benchmark
#!/bin/bash

for i in 1 10 50 100 200 300 400 500 600 700 800 900 1000 1500 2000
3000 4000 5000;
do
echo Testing with $i connections >> bench.log
pgbench2 -M prepared -c $i -j $i -S -n -T 60 postgres >> bench.log
done

pgbench2 is your patched version pgbench. I got some pretty strange
results with the unpatched version.  Up to about 50 million tps for
excluding connection establishing, which seems pretty farfetched

connections        Unpatched        Patched
1        49062.24413        49834.64983
10        428673.1027        453290.5985
50        1552413.084        1849233.821
100        2039675.027        2261437.1
200        3139648.845        3632008.991
300        3091248.316        3597748.942
400        3056453.5        3567888.293
500        3019571.47        3574009.053
600        2991052.393        3537518.903
700        2952484.763        3553252.603
800        2910976.875        3539404.865
900        2873929.989        3514353.776
1000        2846859.499        3490006.026
1500        2540003.038        3370093.934
2000        2361799.107        3197556.738
3000        2056973.778        2949740.692
4000        1751418.117        2627174.81
5000        1464786.461        2334586.042

> Attached as a graph as well.

Likewise.

David

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