Re: postgres 9.3 vs. 9.4

----- Original Message -----
> From: "Mark Kirkwood" <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
> To: "Tigran Mkrtchyan" <tigran(dot)mkrtchyan(at)desy(dot)de>, pgsql-performance(at)postgresql(dot)org
> Sent: Thursday, September 18, 2014 12:17:45 PM
> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
>
> On 18/09/14 21:58, Mkrtchyan, Tigran wrote:
> >
> >
> > Hi Folk,
> >
> > I am trying to investigate some performance issues which we have with
> > postgres
> > (a different topic by itself) and tried postgres.9.4beta2, with a hope that
> > it
> > perform better.
> >
> > Turned out that 9.4 is 2x slower than 9.3.5 on the same hardware.
> >
> > Some technical details:
> >
> > Host: rhel 6.5 2.6.32-431.23.3.el6.x86_64
> > 256 GB RAM, 40 cores, Intel(R) Xeon(R) CPU E5-2660 v2 @ 2.20GHz
> > 2x160GB PCIe SSD DELL_P320h-MTFDGAL175SAH ( on one 9.3, on an other one
> > 9.4 )
> >
> > postgres tweaks:
> >
> >
> > default_statistics_target = 100
> > wal_writer_delay = 10s
> > vacuum_cost_delay = 50
> > synchronous_commit = off
> > maintenance_work_mem = 2GB
> > checkpoint_completion_target = 0.9
> > effective_cache_size = 94GB
> > work_mem = 402MB
> > wal_buffers = 16MB
> > checkpoint_segments = 64
> > shared_buffers = 8GB
> > max_connections = 100
> > random_page_cost = 1.5
> > # other goodies
> > log_line_prefix = '%m <%d %u %r> %%'
> > log_temp_files = 0
> > log_min_duration_statement = 5
> >
> > in both cases databases are fresh - no data.
> >
> > Here is a results with pgbench.
> >
> >
> > 9.3.5:
> >
> > # /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 60
> > starting vacuum...end.
> > transaction type: TPC-B (sort of)
> > scaling factor: 1
> > query mode: simple
> > number of clients: 1
> > number of threads: 1
> > duration: 60 s
> > number of transactions actually processed: 96361
> > tps = 1605.972262 (including connections establishing)
> > tps = 1606.064501 (excluding connections establishing)
> > statement latencies in milliseconds:
> > 0.001391 \set nbranches 1 * :scale
> > 0.000473 \set ntellers 10 * :scale
> > 0.000430 \set naccounts 100000 * :scale
> > 0.000533 \setrandom aid 1 :naccounts
> > 0.000393 \setrandom bid 1 :nbranches
> > 0.000468 \setrandom tid 1 :ntellers
> > 0.000447 \setrandom delta -5000 5000
> > 0.025161 BEGIN;
> > 0.131317 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE
> > aid = :aid;
> > 0.100211 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
> > 0.117406 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid
> > = :tid;
> > 0.114332 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE
> > bid = :bid;
> > 0.086660 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES
> > (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
> > 0.035940 END;
> >
> >
> > 9.4beta2:
> >
> > # /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 60
> > starting vacuum...end.
> > transaction type: TPC-B (sort of)
> > scaling factor: 1
> > query mode: simple
> > number of clients: 1
> > number of threads: 1
> > duration: 60 s
> > number of transactions actually processed: 34017
> > tps = 566.948384 (including connections establishing)
> > tps = 567.008666 (excluding connections establishing)
> > statement latencies in milliseconds:
> > 0.001879 \set nbranches 1 * :scale
> > 0.000526 \set ntellers 10 * :scale
> > 0.000490 \set naccounts 100000 * :scale
> > 0.000595 \setrandom aid 1 :naccounts
> > 0.000421 \setrandom bid 1 :nbranches
> > 0.000480 \setrandom tid 1 :ntellers
> > 0.000484 \setrandom delta -5000 5000
> > 0.055047 BEGIN;
> > 0.172179 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE
> > aid = :aid;
> > 0.135392 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
> > 0.157224 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid
> > = :tid;
> > 0.147969 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE
> > bid = :bid;
> > 0.123001 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES
> > (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
> > 0.957854 END;
> >
> > any ideas?
> >
>
> Hi Tigran,
>
> Some ideas:
>
> 60s is too short for reliable results (default settings for checkpoints
> is 300s so 600s is the typical elapsed time to get reasonably repeatable
> numbers (to ensure you get about 1 checkpoint in your run). In addition
> I usually do
>
> psql <<!
> CHECKPOINT;
> !
>
> Plus
>
> $ sleep 10
>
> before each run so that I've got some confidence that we are starting
> from approximately the same state each time (and getting hopefully only
> *one* checkpoint per run)!

Sure, I can run a longer tests with longer breaks in between.

9.3.5

# /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 600
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 600 s
number of transactions actually processed: 1037297
tps = 1728.826406 (including connections establishing)
tps = 1728.836277 (excluding connections establishing)
statement latencies in milliseconds:
0.001471 \set nbranches 1 * :scale
0.000456 \set ntellers 10 * :scale
0.000411 \set naccounts 100000 * :scale
0.000524 \setrandom aid 1 :naccounts
0.000364 \setrandom bid 1 :nbranches
0.000437 \setrandom tid 1 :ntellers
0.000424 \setrandom delta -5000 5000
0.024217 BEGIN;
0.118966 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.092483 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.108232 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.107978 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.080137 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.034015 END;

9.4beta2

# /usr/pgsql-9.3/bin/pgbench -r -j 1 -c 1 -T 600
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 600 s
number of transactions actually processed: 373454
tps = 622.422377 (including connections establishing)
tps = 622.429494 (excluding connections establishing)
statement latencies in milliseconds:
0.001252 \set nbranches 1 * :scale
0.000417 \set ntellers 10 * :scale
0.000384 \set naccounts 100000 * :scale
0.000466 \setrandom aid 1 :naccounts
0.000344 \setrandom bid 1 :nbranches
0.000411 \setrandom tid 1 :ntellers
0.000397 \setrandom delta -5000 5000
0.047489 BEGIN;
0.157164 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.119992 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
0.141147 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
0.132492 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.108917 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.889112 END;

Tigran.

>
> Cheers
>
> Mark
>