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

Em qua., 25 de mai. de 2022 às 08:26, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
escreveu:

> Em qua., 25 de mai. de 2022 às 07:13, Tomas Vondra <
> tomas(dot)vondra(at)enterprisedb(dot)com> escreveu:
>
>>
>>
>> On 5/25/22 11:07, Ranier Vilela wrote:
>> > Em qua., 25 de mai. de 2022 às 00:46, Andres Freund <andres(at)anarazel(dot)de
>> > <mailto:andres(at)anarazel(dot)de>> escreveu:
>> >
>> > Hi Andres, thank you for taking a look.
>> >
>> >
>> > On 2022-05-24 12:28:20 -0300, Ranier Vilela wrote:
>> > > Linux Ubuntu 64 bits (gcc 9.4)
>> > > ./pgbench -M prepared -c $conns -j $conns -S -n -U postgres
>> > >
>> > > conns tps head tps patched
>> > > 1 2918.004085 3190.810466
>> > > 10 12262.415696 17199.862401
>> > > 50 13656.724571 18278.194114
>> > > 80 14338.202348 17955.336101
>> > > 90 16597.510373 18269.660184
>> > > 100 17706.775793 18349.650150
>> > > 200 16877.067441 17881.250615
>> > > 300 16942.260775 17181.441752
>> > > 400 16794.514911 17124.533892
>> > > 500 16598.502151 17181.244953
>> > > 600 16717.935001 16961.130742
>> > > 700 16651.204834 16959.172005
>> > > 800 16467.546583 16834.591719
>> > > 900 16588.241149 16693.902459
>> > > 1000 16564.985265 16936.952195
>> >
>> > 17-18k tps is pretty low for pgbench -S. For a shared_buffers
>> > resident run, I
>> > can get 40k in a single connection in an optimized build. If you're
>> > testing a
>> > workload >> shared_buffers, GetSnapshotData() isn't the bottleneck.
>> And
>> > testing an assert build isn't a meaningful exercise either, unless
>> > you have
>> > way way higher gains (i.e. stuff like turning O(n^2) into O(n)).
>> >
>> > Thanks for sharing these hits.
>> > Yes, their 17-18k tps are disappointing.
>> >
>> >
>> > What pgbench scale is this and are you using an optimized build?
>> >
>> > Yes this optimized build.
>> > CFLAGS='-Wall -Wmissing-prototypes -Wpointer-arith
>> > -Wdeclaration-after-statement -Werror=vla -Wendif-labels
>> > -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type
>> > -Wformat-security -fno-strict-aliasing -fwrapv
>> > -fexcess-precision=standard -Wno-format-truncation
>> > -Wno-stringop-truncation -O2'
>> > from config.log
>> >
>>
>> That can still be assert-enabled build. We need to see configure flags.
>>
> ./configure
> Attached the config.log (compressed)
>
>
>>
>> > pgbench was initialized with:
>> > pgbench -i -p 5432 -d postgres
>> >
>> > pgbench -M prepared -c 100 -j 100 -S -n -U postgres
>>
>> You're not specifying duration/number of transactions to execute. So
>> it's using just 10 transactions per client, which is bound to give you
>> bogus results due to not having anything in relcache etc. Use -T 60 or
>> something like that.
>>
> Ok, I will try with -T 60.
>

Here the results with -T 60:
Linux Ubuntu 64 bits
shared_buffers = 128MB

./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

Linux Ubuntu 64 bits
shared_buffers = 2048MB

./pgbench -M prepared -c $conns -j $conns -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
number of transactions per client: 10

conns tps head tps patched

1 2918.004085 3211.303789
10 12262.415696 15540.015540
50 13656.724571 16701.182444
80 14338.202348 16628.559551
90 16597.510373 16835.016835
100 17706.775793 16607.433487
200 16877.067441 16426.969799
300 16942.260775 16319.780662
400 16794.514911 16155.023607
500 16598.502151 16051.106724
600 16717.935001 16007.171213
700 16651.204834 16004.353184
800 16467.546583 16834.591719
900 16588.241149 16693.902459
1000 16564.985265 16936.952195

Linux Ubuntu 64 bits
shared_buffers = 2048MB

./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 17174.265804 17792.414234
10 82365.634750 82468.334836
50 74593.714180 74678.839428
80 69219.756038 73116.553986
90 67419.574189 68384.906949
100 66613.771701 66997.793777
200 61739.784830 62870.243385
300 62109.691298 62796.157548
400 61630.822446 63129.555135
500 61711.019964 62755.190389
600 60620.010181 61517.913967
700 60303.317736 61688.044232
800 60451.113573 61076.666572
900 60017.327157 61256.290037
1000 60088.823434 60986.799312

regards,
Ranier Vilela