Re: Oh, this is embarrassing: init file logic is still broken - Mailing list pgsql-hackers

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@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 * :scale0.001427    \setrandom aid 1
:naccounts4.254619   SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
 

9.4.4:
t-ishii@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 * :scale0.001653    \setrandom aid 1
:naccounts9.320204   SELECT abalance FROM pgbench_accounts WHERE aid = :aid
 

9.4-stable:
t-ishii@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 * :scale0.001395    \setrandom aid 1
:naccounts4.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers