Re: Oh, this is embarrassing: init file logic is still broken

F.Y.I. here is the results I did on my laptop (Ubuntu 14, i7-4600U,
16GB mem, 512GB SSD). Unlike Josh, I used Unix domain sockets. In
summary:

9.4.3: 943.439840
9.4.4: 429.953953
9.4 stable as of June 30: 929.804491

So comparing with 9.4.3, 9.4.4 is 54% slow, and 9.4-stable is 1.4% slow.

I think the difference between 9.4.3 seems a measurement error but the
difference between 9.4.3 and 9.4.4 is real. Good thing is 9.4 stable
fixes the issue as expected. Thanks Tom!

Below is the details.

9.4.3:
t-ishii(at)localhost: pgbench -p 5434 -s 100 -j 2 -c 6 -r -C -S -T 1200 test
Scale option ignored, using pgbench_branches table count = 100
starting vacuum...end.
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 6
number of threads: 2
duration: 1200 s
number of transactions actually processed: 1132130
latency average: 6.360 ms
tps = 943.439840 (including connections establishing)
tps = 64573.165915 (excluding connections establishing)
statement latencies in milliseconds:
0.002809 \set naccounts 100000 * :scale
0.001427 \setrandom aid 1 :naccounts
4.254619 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

9.4.4:
t-ishii(at)localhost: pgbench -p 5433 -s 100 -j 2 -c 6 -r -C -S -T 1200 test
Scale option ignored, using pgbench_branches table count = 100
starting vacuum...end.
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 6
number of threads: 2
duration: 1200 s
number of transactions actually processed: 515946
latency average: 13.955 ms
tps = 429.953953 (including connections establishing)
tps = 56235.664740 (excluding connections establishing)
statement latencies in milliseconds:
0.003311 \set naccounts 100000 * :scale
0.001653 \setrandom aid 1 :naccounts
9.320204 SELECT abalance FROM pgbench_accounts WHERE aid = :aid

9.4-stable:
t-ishii(at)localhost: pgbench -p 5435 -s 100 -j 2 -c 6 -r -C -S -T 1200 test
Scale option ignored, using pgbench_branches table count = 100
starting vacuum...end.
transaction type: SELECT only
scaling factor: 100
query mode: simple
number of clients: 6
number of threads: 2
duration: 1200 s
number of transactions actually processed: 1115767
latency average: 6.453 ms
tps = 929.804491 (including connections establishing)
tps = 64676.863921 (excluding connections establishing)
statement latencies in milliseconds:
0.002803 \set naccounts 100000 * :scale
0.001395 \setrandom aid 1 :naccounts
4.316686 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

> OK, this is pretty bad in its real performance effects. On a workload
> which is dominated by new connection creation, we've lost about 17%
> throughput.
>
> To test it, I ran pgbench -s 100 -j 2 -c 6 -r -C -S -T 1200 against a
> database which fits in shared_buffers on two different m3.large
> instances on AWS (across the network, not on unix sockets). A typical
> run on 9.3.6 looks like this:
>
> scaling factor: 100
> query mode: simple
> number of clients: 6
> number of threads: 2
> duration: 1200 s
> number of transactions actually processed: 252322
> tps = 210.267219 (including connections establishing)
> tps = 31958.233736 (excluding connections establishing)
> statement latencies in milliseconds:
> 0.002515 \set naccounts 100000 * :scale
> 0.000963 \setrandom aid 1 :naccounts
> 19.042859 SELECT abalance FROM pgbench_accounts WHERE aid
> = :aid;
>
> Whereas a typical run on 9.3.9 looks like this:
>
> scaling factor: 100
> query mode: simple
> number of clients: 6
> number of threads: 2
> duration: 1200 s
> number of transactions actually processed: 208180
> tps = 173.482259 (including connections establishing)
> tps = 31092.866153 (excluding connections establishing)
> statement latencies in milliseconds:
> 0.002518 \set naccounts 100000 * :scale
> 0.000988 \setrandom aid 1 :naccounts
> 23.076961 SELECT abalance FROM pgbench_accounts WHERE aid
> = :aid;
>
> Numbers are pretty consistent on four runs each on two different
> instances (+/- 4%), so I don't think this is Amazon variability we're
> seeing. I think the syscache invalidation is really costing us 17%. :-(
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers