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

Em qua., 25 de mai. de 2022 às 07:13, Tomas Vondra <tomas.vondra@enterprisedb.com> escreveu:

On 5/25/22 11:07, Ranier Vilela wrote:
> Em qua., 25 de mai. de 2022 às 00:46, Andres Freund <andres@anarazel.de
> <mailto:andres@anarazel.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