Message-ID: <541B59AD.9020109@catalyst.net.nz>
Date: Fri, 19 Sep 2014 10:16:13 +1200
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.1.1
MIME-Version: 1.0
To: "Mkrtchyan, Tigran" <tigran.mkrtchyan@desy.de>
CC: Merlin Moncure <mmoncure@gmail.com>,
 postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: postgres 9.3 vs. 9.4
References: <45142416.42827.1411033201079.JavaMail.zimbra@desy.de>
 <88549888.42916.1411034301200.JavaMail.zimbra@desy.de>
 <CAHyXU0xSJR=r4savNfMcpzxBh9ZzgMc4aZtD4DxramZiexkYSw@mail.gmail.com>
 <541B4704.9090305@catalyst.net.nz>
 <609416895.45709.1411074654752.JavaMail.zimbra@desy.de>
In-Reply-To: <609416895.45709.1411074654752.JavaMail.zimbra@desy.de>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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...


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance