Re: Improving connection scalability (src/backend/storage/ipc/procarray.c)

Em sáb., 28 de mai. de 2022 às 09:00, Tomas Vondra <
tomas(dot)vondra(at)enterprisedb(dot)com> escreveu:

> On 5/28/22 02:15, Ranier Vilela wrote:
> >
> >
> > Em sex., 27 de mai. de 2022 às 18:08, Andres Freund <andres(at)anarazel(dot)de
> > <mailto:andres(at)anarazel(dot)de>> escreveu:
> >
> > Hi,
> >
> > On 2022-05-27 03:30:46 +0200, Tomas Vondra wrote:
> > > On 5/27/22 02:11, Ranier Vilela wrote:
> > > > ./pgbench -M prepared -c $conns -j $conns -T 60 -S -n -U postgres
> > > >
> > > > pgbench (15beta1)
> > > > transaction type: <builtin: select only>
> > > > scaling factor: 1
> > > > query mode: prepared
> > > > number of clients: 100
> > > > number of threads: 100
> > > > maximum number of tries: 1
> > > > duration: 60 s
> > > >
> > > > conns tps head tps patched
> > > >
> > > > 1 17126.326108 17792.414234
> > > > 10 82068.123383 82468.334836
> > > > 50 73808.731404 74678.839428
> > > > 80 73290.191713 73116.553986
> > > > 90 67558.483043 68384.906949
> > > > 100 65960.982801 66997.793777
> > > > 200 62216.011998 62870.243385
> > > > 300 62924.225658 62796.157548
> > > > 400 62278.099704 63129.555135
> > > > 500 63257.930870 62188.825044
> > > > 600 61479.890611 61517.913967
> > > > 700 61139.354053 61327.898847
> > > > 800 60833.663791 61517.913967
> > > > 900 61305.129642 61248.336593
> > > > 1000 60990.918719 61041.670996
> > > >
> > >
> > > These results look much saner, but IMHO it also does not show any
> > clear
> > > benefit of the patch. Or are you still claiming there is a benefit?
> >
> > They don't look all that sane to me - isn't that way lower than one
> > would
> > expect?
> >
> > Yes, quite disappointing.
> >
> > Restricting both client and server to the same four cores, a
> > thermically challenged older laptop I have around I get 150k tps at
> > both 10
> > and 100 clients.
> >
> > And you can share the benchmark details? Hardware, postgres and pgbench,
> > please?
> >
> >
> > Either way, I'd not expect to see any GetSnapshotData() scalability
> > effects to
> > show up on an "Intel® Core™ i5-8250U CPU Quad Core" - there's just
> > not enough
> > concurrency.
> >
> > This means that our customers will not see any connections scalability
> > with PG15, using the simplest hardware?
> >
>
> No. It means that on 4-core machine GetSnapshotData() is unlikely to be
> a bottleneck, because you'll hit various other bottlenecks way earlier.
>
> I personally doubt it even makes sense to worry about scaling to this
> many connections on such tiny system too much.
>

> >
> > The correct pieces of these changes seem very unlikely to affect
> > GetSnapshotData() performance meaningfully.
> >
> > To improve something like GetSnapshotData() you first have to come
> > up with a
> > workload that shows it being a meaningful part of a profile. Unless
> > it is,
> > performance differences are going to just be due to various forms of
> > noise.
> >
> > Actually in the profiles I got with perf, GetSnapShotData() didn't show
> up.
> >
>
> But that's exactly the point Andres is trying to make - if you don't see
> GetSnapshotData() in the perf profile, why do you think optimizing it
> will have any meaningful impact on throughput?
>
You see, I've seen in several places that GetSnapShotData() is the
bottleneck in scaling connections.
One of them, if I remember correctly, was at an IBM in Russia.
Another statement occurs in [1][2][3]
Just because I don't have enough hardware to force GetSnapShotData()
doesn't mean optimizing it won't make a difference.
And even on my modest hardware, we've seen gains, small but consistent.
So IMHO everyone will benefit, including the small servers.

regards,
Ranier Vilela

[1]
https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462
[2] https://www.postgresql.org/message-id/5198715A.6070808%40vmware.com
[3]
https://it-events.com/system/attachments/files/000/001/098/original/PostgreSQL_%D0%BC%D0%B0%D1%81%D1%88%D1%82%D0%B0%D0%B1%D0%B8%D1%80%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5.pdf?1448975472

>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>