Il giorno 02/apr/2015, alle ore 14:29, didier <did...@gmail.com> ha scritto:
> Hi, > > On Thu, Apr 2, 2015 at 12:47 PM, Pietro Pugni <pietro.pu...@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
