Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL

Il giorno 02/apr/2015, alle ore 14:29, didier <did447(at)gmail(dot)com> ha scritto:

> Hi,
>
> On Thu, Apr 2, 2015 at 12:47 PM, Pietro Pugni <pietro(dot)pugni(at)gmail(dot)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