Re: postgres 9.3 vs. 9.4

----- Original Message -----
> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
> To: "Tigran Mkrtchyan" <tigran.mkrtchyan@desy.de>, pgsql-performance@postgresql.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
>