Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YdfXf-0005gt-Vs for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 13:52:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YdfXf-0006ri-EU for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 13:52:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YdfXd-0006ph-UD for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 13:52:18 +0000 Received: from mail-wi0-x22a.google.com ([2a00:1450:400c:c05::22a]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YdfXa-00010h-DN for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 13:52:16 +0000 Received: by widjs5 with SMTP id js5so9869656wid.1 for ; Thu, 02 Apr 2015 06:52:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=content-type:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=j/o9pTfNXX0ME/gbzbV/yDVWyplahC8a7Dd1MLQ9yIw=; b=SHRBOpJjMD6/gBHFchPQYbDt3LKu0M2UW2GkIB9ZuzjP8Xr8XFTXd3qmGm0q0X+C2c itACWhY5osKXyZkV0ciMtJ69Yh5kQuokyXPyusIya8lrI+Pf0GbYELZ9mbz+Qbcj3UOz Q/93jOUPYAx/wj+zko6riMzBeBoh6k5m7yngDPuM063NMJi12zQ/Irv+lHhp6wl+1dxJ DxExNSVkx4E9npLoyjxRF2qYnXsJrkhu7hvOaXhAXmD230BGHpOkRw8AEY1yAhawsRXk M6nYW8eWsx0jTC0LqAGFaFc8jWNK0bgBXFTkveZvXUnijW5wJOOsgXGG73Q9PWZo96f5 TqvQ== X-Received: by 10.194.19.10 with SMTP id a10mr95830396wje.153.1427982732913; Thu, 02 Apr 2015 06:52:12 -0700 (PDT) Received: from [192.168.2.2] (2-230-140-245.ip202.fastwebnet.it. [2.230.140.245]) by mx.google.com with ESMTPSA id ff4sm19562328wib.9.2015.04.02.06.52.11 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 02 Apr 2015 06:52:11 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_7B67996E-DBB5-481F-8961-7F6983CBD4D5" Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\)) Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL From: Pietro Pugni In-Reply-To: Date: Thu, 2 Apr 2015 15:52:10 +0200 Cc: "pgsql-performance@postgresql.org" Message-Id: References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com> <2F18FF84-5408-4AE8-9F2B-9110F76DED86@gmail.com> To: didier X-Mailer: Apple Mail (2.1878.6) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_7B67996E-DBB5-481F-8961-7F6983CBD4D5 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 Il giorno 02/apr/2015, alle ore 14:29, didier ha = scritto: > Hi, >=20 > On Thu, Apr 2, 2015 at 12:47 PM, Pietro Pugni = wrote: >> Hi Jeff, >> thank you for your response. >> I=92m using Postgres 9.0 on MacMini because I=92ve noticed that it=92s = quite fast >> compared to different Ubuntu machines on which I=92ve 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. >=20 You=92re referring to disk scheduler? I=92ve tried to change it on T420 = with no significant variations over performance. I=92ve 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=92t = report >> all test results in my first mail). >=20 >>=20 >> I=92ve searched just now what a collation is because I=92ve never = explicitly >> used one before, so I think it uses the default one. >=20 > What's the output of free and sysctl -a | grep vm.zone_reclaim_mode >=20 > Search the mailing list for zone_reclaim_mode there's some tips. >=20 vm.zone_reclaim_mode =3D 0 I=92ve also set these options in /etc/sysctl.conf: kernel.shmmax=3D68719476736 kernel.shmall=3D16777216 vm.overcommit_memory=3D2 vm.overcommit_ratio=3D90 I=92ll 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=92s = difficult to fully understand how to achieve this. Thank you again, Pietro= --Apple-Mail=_7B67996E-DBB5-481F-8961-7F6983CBD4D5 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252
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=92m using Postgres 9.0 on MacMini because I=92ve noticed = that it=92s quite fast
compared to different Ubuntu machines on which = I=92ve 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=92re = referring to disk scheduler? I=92ve tried to change it on T420 with no = significant variations over performance.
I=92ve 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=92t report
all test results = in my first mail).


I=92v= e searched just now what a collation is because I=92ve 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 =3D = 0

I=92ve also set these options in = /etc/sysctl.conf:
kernel.shmmax=3D68719476736
kernel.shmall=3D16777216
vm.overcommit_memory=3D2
vm.overcommit_ratio=3D90

= I=92ll 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



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=92s difficult to = fully understand how to achieve this.

Thank you = again,
 Pietro
= --Apple-Mail=_7B67996E-DBB5-481F-8961-7F6983CBD4D5--