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

[Pietro Pugni <pietro.pugni@xxxxxxxxx>]

Il giorno 02/apr/2015, alle ore 14:29, didier <did447@xxxxxxxxx> ha scritto:

Hi,

On Thu, Apr 2, 2015 at 12:47 PM, Pietro Pugni <pietro.pugni@xxxxxxxxx> 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