Re: kqueue

On Wed, Sep 14, 2016 at 9:09 AM, Matteo Beccati <php(at)beccati(dot)com> wrote:

> Hi,
>
> On 14/09/2016 00:06, Tom Lane wrote:
>
>> I'm inclined to think the kqueue patch is worth applying just on the
>> grounds that it makes things better on OS X and doesn't seem to hurt
>> on FreeBSD. Whether anyone would ever get to the point of seeing
>> intra-kernel contention on these platforms is hard to predict, but
>> we'd be ahead of the curve if so.
>>
>> It would be good for someone else to reproduce my results though.
>> For one thing, 5%-ish is not that far above the noise level; maybe
>> what I'm measuring here is just good luck from relocation of critical
>> loops into more cache-line-friendly locations.
>>
>
> FWIW, I've tested HEAD vs patch on a 2-cpu low end NetBSD 7.0 i386 machine.
>
> HEAD: 1890/1935/1889 tps
> kqueue: 1905/1957/1932 tps
>
> no weird surprises, and basically no differences either.
>
>
> Cheers
> --
> Matteo Beccati
>
> Development & Consulting - http://www.beccati.com/

Thomas Munro brought up in #postgresql on freenode needing someone to test
a patch on a larger FreeBSD server. I've got a pretty decent machine
(3.1Ghz Quad Core Xeon E3-1220V3, 16GB ECC RAM, ZFS mirror on WD Red HDD)
so offered to give it a try.

Bench setup was:
pgbench -i -s 100 -d postgres

I ran this against 96rc1 instead of HEAD like most of the others in this
thread seem to have done. Not sure if that makes a difference and can
re-run if needed.
With higher concurrency, this seems to cause decreased performance. You can
tell which of the runs is the kqueue patch by looking at the path to
pgbench.

SINGLE PROCESS
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1_kqueue/bin/pgbench -T
60 -j 1 -c 1 -M prepared -S postgres -p
5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 1547387
latency average: 0.039 ms
tps = 25789.750236 (including connections establishing)
tps = 25791.018293 (excluding connections establishing)
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1_kqueue/bin/pgbench -T
60 -j 1 -c 1 -M prepared -S postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 1549442
latency average: 0.039 ms
tps = 25823.981255 (including connections establishing)
tps = 25825.189871 (excluding connections establishing)
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1_kqueue/bin/pgbench -T
60 -j 1 -c 1 -M prepared -S postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 1547936
latency average: 0.039 ms
tps = 25798.572583 (including connections establishing)
tps = 25799.917170 (excluding connections establishing)

[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1/bin/pgbench -T 60 -j 1
-c 1 -M prepared -S postgres -p
5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 1520722
latency average: 0.039 ms
tps = 25343.122533 (including connections establishing)
tps = 25344.357116 (excluding connections establishing)
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1/bin/pgbench -T 60 -j 1
-c 1 -M prepared -S postgres -p 5496~
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 1549282
latency average: 0.039 ms
tps = 25821.107595 (including connections establishing)
tps = 25822.407310 (excluding connections establishing)
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1/bin/pgbench -T 60 -j 1
-c 1 -M prepared -S postgres -p 5496~
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 1
number of threads: 1
duration: 60 s
number of transactions actually processed: 1541907
latency average: 0.039 ms
tps = 25698.025983 (including connections establishing)
tps = 25699.270663 (excluding connections establishing)

FOUR
/home/keith/pgsql96rc1_kqueue/bin/pgbench -T 60 -j 4 -c 4 -M prepared -S
postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 60 s
number of transactions actually processed: 4282185
latency average: 0.056 ms
tps = 71369.146931 (including connections establishing)
tps = 71372.646243 (excluding connections establishing)
[keith(at)corpus ~/postgresql-9.6rc1_kqueue]$
/home/keith/pgsql96rc1_kqueue/bin/pgbench -T 60 -j 4 -c 4 -M prepared -S
postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 60 s
number of transactions actually processed: 4777596
latency average: 0.050 ms
tps = 79625.214521 (including connections establishing)
tps = 79629.800123 (excluding connections establishing)
[keith(at)corpus ~/postgresql-9.6rc1_kqueue]$
/home/keith/pgsql96rc1_kqueue/bin/pgbench -T 60 -j 4 -c 4 -M prepared -S
postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 60 s
number of transactions actually processed: 4809132
latency average: 0.050 ms
tps = 80151.803249 (including connections establishing)
tps = 80155.903203 (excluding connections establishing)

/home/keith/pgsql96rc1/bin/pgbench -T 60 -j 4 -c 4 -M prepared -S postgres
-p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 60 s
number of transactions actually processed: 5114286
latency average: 0.047 ms
tps = 85236.858383 (including connections establishing)
tps = 85241.847800 (excluding connections establishing)
/home/keith/pgsql96rc1/bin/pgbench -T 60 -j 4 -c 4 -M prepared -S postgres
-p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 60 s
number of transactions actually processed: 5600194
latency average: 0.043 ms
tps = 93335.508864 (including connections establishing)
tps = 93340.970416 (excluding connections establishing)
/home/keith/pgsql96rc1/bin/pgbench -T 60 -j 4 -c 4 -M prepared -S postgres
-p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 4
number of threads: 4
duration: 60 s
number of transactions actually processed: 5606962
latency average: 0.043 ms
tps = 93447.905764 (including connections establishing)
tps = 93454.077142 (excluding connections establishing)

SIXTY-FOUR
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1_kqueue/bin/pgbench -T
60 -j 64 -c 64 -M prepared -S postgres -p
5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 4084213
latency average: 0.940 ms
tps = 67633.476871 (including connections establishing)
tps = 67751.865998 (excluding connections establishing)
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1_kqueue/bin/pgbench -T
60 -j 64 -c 64 -M prepared -S postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 4119994
latency average: 0.932 ms
tps = 68474.847365 (including connections establishing)
tps = 68540.221835 (excluding connections establishing)
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1_kqueue/bin/pgbench -T
60 -j 64 -c 64 -M prepared -S postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 4068071
latency average: 0.944 ms
tps = 67192.603129 (including connections establishing)
tps = 67254.760177 (excluding connections establishing)

[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1/bin/pgbench -T 60 -j 64
-c 64 -M prepared -S postgres -p
5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 4281302
latency average: 0.897 ms
tps = 70147.847337 (including connections establishing)
tps = 70389.283564 (excluding connections establishing)
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1/bin/pgbench -T 60 -j 64
-c 64 -M prepared -S postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 4573114
latency average: 0.840 ms
tps = 74848.884475 (including connections establishing)
tps = 75102.862539 (excluding connections establishing)
[keith(at)corpus /tank/pgdata]$ /home/keith/pgsql96rc1/bin/pgbench -T 60 -j 64
-c 64 -M prepared -S postgres -p 5496
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 100
query mode: prepared
number of clients: 64
number of threads: 64
duration: 60 s
number of transactions actually processed: 4341447
latency average: 0.884 ms
tps = 72350.152281 (including connections establishing)
tps = 72421.831179 (excluding connections establishing)

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com