From: Pietro Pugni <pietro.pugni@gmail.com>
To: didier <did447@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
Date: Thu, 2 Apr 2015 15:52:10 +0200
Message-ID: <FDDBF5B8-99E0-412C-8863-02145167218C@gmail.com> (raw)
In-Reply-To: <CAJRYxu+B09b2E6Oad7a7gcTWFRJNHDunY7zTb78==a7yvPF9Jg@mail.gmail.com>
References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com>
<CAMkU=1wsRYdWrQ2m5wshTJw41wncOJeHXATxzVbCbYXbmoxTFQ@mail.gmail.com>
<2F18FF84-5408-4AE8-9F2B-9110F76DED86@gmail.com>
<CAJRYxu+B09b2E6Oad7a7gcTWFRJNHDunY7zTb78==a7yvPF9Jg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Il giorno 02/apr/2015, alle ore 14:29, didier <did447@gmail.com> ha scritto:
> Hi,
>
> On Thu, Apr 2, 2015 at 12:47 PM, Pietro Pugni <pietro.pugni@gmail.com> wrote:
>> Hi Jeff,
>> thank you for your response.
>> I’m using Postgres 9.0 on MacMini because I’ve noticed that it’s quite fast
>> compared to different Ubuntu machines on which I’ve worked with different
>> (and more performant) hardware.
>> The built-in Postgres version on OS X Server is impossible to update. I
>> should stop it and install a parallel and independent distribution which has
>> not been optimized by Apple. On opensource.appel.com they have different
>> Postgres versions but the latest one is 9.2.x. They stopped updating it in
>> 2012.
> If you want you can compile 9.0 on OSX and double check.
> I don't remember well but ITSM that a fsync used by psql was a noop on OSX.
>
You’re referring to disk scheduler? I’ve tried to change it on T420 with no significant variations over performance.
I’ve also tried different fsync options with no improvements.
>> pg_test_fsync tells me that T420 disk iops are ~7 times faster than MacMini,
>> which is ok, but queries run ~2-5 times slower (for brevity I didn’t report
>> all test results in my first mail).
>
>>
>> I’ve searched just now what a collation is because I’ve never explicitly
>> used one before, so I think it uses the default one.
>
> What's the output of free and sysctl -a | grep vm.zone_reclaim_mode
>
> Search the mailing list for zone_reclaim_mode there's some tips.
>
vm.zone_reclaim_mode = 0
I’ve also set these options in /etc/sysctl.conf:
kernel.shmmax=68719476736
kernel.shmall=16777216
vm.overcommit_memory=2
vm.overcommit_ratio=90
I’ll search the mailing list.
> For testing you can also use the mac mini config with the dell, at
> least it should give you the same plan.
> With your example disks don't seem to matter, it's all in memory.
The same transaction took 106s on MacMini; 129s on T420 with my optimized configuration; 180s on T420 using MacMini configuration.
Query plans for B_1 and B_2 queries with the two configurations on T420:
T420 with optimal postgresql.conf
Query B_1 [55999.649 ms + 0.639 ms] http://explain.depesz.com/s/LbM
Query B_2 [95664.832 ms + 0.523 ms] http://explain.depesz.com/s/v06
T420 with MacMini postgresql.conf
Query B_1 [51280.208ms + 0.699ms] http://explain.depesz.com/s/wlb
Query B_2 [177278.205ms + 0.428ms] http://explain.depesz.com/s/rzr
MacMini
Query B_1 [56315.614 ms] http://explain.depesz.com/s/uZTx
Query B_2 [44890.813 ms] http://explain.depesz.com/s/y7Dk> Keep in mind that a psql query is still single thread so the mac and
> the dell should get more or less the same speed for in memory queries.
Yes I know ;) With 128GB I try to maximize RAM usage, but it’s difficult to fully understand how to achieve this.
Thank you again,
Pietro=
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pietro.pugni@gmail.com, did447@gmail.com
Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
In-Reply-To: <FDDBF5B8-99E0-412C-8863-02145167218C@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox