Re: postgres 9.3 vs. 9.4

On 19/09/14 10:16, Mark Kirkwood wrote:
> On 19/09/14 09:10, Mkrtchyan, Tigran wrote:
>>
>>
>> ----- Original Message -----
>>> From: "Mark Kirkwood" <mark.kirkwood@catalyst.net.nz>
>>> To: "Merlin Moncure" <mmoncure@gmail.com>, "Tigran Mkrtchyan"
>>> <tigran.mkrtchyan@desy.de>
>>> Cc: "postgres performance list" <pgsql-performance@postgresql.org>
>>> Sent: Thursday, September 18, 2014 10:56:36 PM
>>> Subject: Re: [PERFORM] postgres 9.3 vs. 9.4
>>>
>>> On 19/09/14 08:32, Merlin Moncure wrote:
>>>> On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran
>>>> <tigran.mkrtchyan@desy.de> wrote:
>>>>>
>>>>> 9.3.5:
>>>>>           0.035940        END;
>>>>>
>>>>>
>>>>> 9.4beta2:
>>>>>           0.957854        END;
>>>>
>>>>
>>>> time being spent on 'END' is definitely suggesting i/o related issues.
>>>> This is making me very skeptical that postgres is the source of the
>>>> problem.   I also thing synchronous_commit is not set properly on the
>>>> new instance (or possibly there is a bug or some such).  Can you
>>>> verify via:
>>>>
>>>> select * from pg_settings where name = 'synchronous_commit';
>>>>
>>>> on both servers?
>>>>
>>>
>>> Yes, does look suspicious. It *could* be that the 9.4 case is getting
>>> unlucky and checkpointing just before the end of the 60s run, and 9.3
>>> isn't.
>>
>> 10 minutes run had the same results.
>>
>> Is there some kind of statistics which can tell there time is spend?
>> Or the only way is to run on solaris with dtrace? For me it's more
>> important
>> to find why I get only 1500tps with 9.3. The test with 9.4 was just a
>> hope for
>> a magic code change that will give me a better performance.
>>
>>
>
> Interesting. With respect to dtrace, you can use systemtap on Linux to
> achieve similar things.
>
> However before getting too carried away with that - we already *know*
> that 9.4 is spending longer in END (i.e commit) than 9.3 is. I'd
> recommend you see what wal_sync_method is set to on both systems. If it
> is the same, then my suspicion is that one of the SSD's needs to be
> trimmed [1]. You can do this by running:
>
> $ fstrim /mountpoint
>
> Also - are you using the same filesystem and mount options on each SSD?
>
> Cheers
>
> Mark
>
> [1] if fact, for the paranoid - I usually secure erase any SSD before
> performance testing, and then check the SMART counters too...
>

Further to the confusion, here's my 9.3 vs 9.4 on two M550 (one for 9.3
one for 9.4), see below for results.

I'm running xfs on them with trim/discard enabled:

$ mount|grep pg
/dev/sdd4 on /mnt/pg94 type xfs (rw,discard)
/dev/sdc4 on /mnt/pg93 type xfs (rw,discard)


I'm *not* seeing any significant difference between 9.3 and 9.4, and the
numbers are both about 2x your best number, which is food for thought
(those P320's should toast my M550 for write performance...).


9.3:

$ pgbench -r -j 1 -c 1 -T 60 bench
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: 194615
tps = 3243.567115 (including connections establishing)
tps = 3243.771688 (excluding connections establishing)
statement latencies in milliseconds:
    0.000798    \set nbranches 1 * :scale
    0.000302    \set ntellers 10 * :scale
    0.000276    \set naccounts 100000 * :scale
    0.000330    \setrandom aid 1 :naccounts
    0.000265    \setrandom bid 1 :nbranches
    0.000278    \setrandom tid 1 :ntellers
    0.000298    \setrandom delta -5000 5000
    0.012818    BEGIN;
    0.065403    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE
aid = :aid;
    0.048516    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    0.058343    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE
tid = :tid;
    0.057763    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE
bid = :bid;
    0.043293    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    0.017087    END;


9.4:

$ pgbench -r -j 1 -c 1 -T 60 bench
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: 194130
latency average: 0.309 ms
tps = 3235.488190 (including connections establishing)
tps = 3235.560235 (excluding connections establishing)
statement latencies in milliseconds:
    0.000460    \set nbranches 1 * :scale
    0.000231    \set ntellers 10 * :scale
    0.000224    \set naccounts 100000 * :scale
    0.000258    \setrandom aid 1 :naccounts
    0.000252    \setrandom bid 1 :nbranches
    0.000266    \setrandom tid 1 :ntellers
    0.000272    \setrandom delta -5000 5000
    0.011724    BEGIN;
    0.083750    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE
aid = :aid;
    0.045553    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
    0.054412    UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE
tid = :tid;
    0.053371    UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE
bid = :bid;
    0.041501    INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
    0.015273    END;

configuration:

logging_collector = 'on'
wal_writer_delay = '10s'
vacuum_cost_delay = 50
synchronous_commit = 'off'
wal_buffers = '16MB'
checkpoint_segments = 64
shared_buffers = '2GB'
max_connections = 100
random_page_cost = 1.5