Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Ydcf6-0007B6-Ks for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 10:47:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Ydcf5-0006q9-A5 for pgsql-performance@arkaria.postgresql.org; Thu, 02 Apr 2015 10:47:47 +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 1Ydcf4-0006q3-5z for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 10:47:46 +0000 Received: from mail-wg0-x22d.google.com ([2a00:1450:400c:c00::22d]) by makus.postgresql.org with esmtps (TLS1.2:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Ydcf0-00060m-Ed for pgsql-performance@postgresql.org; Thu, 02 Apr 2015 10:47:44 +0000 Received: by wgbdm7 with SMTP id dm7so81228596wgb.1 for ; Thu, 02 Apr 2015 03:47:40 -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=OWf+gDGuZodTnuj//QzqoGQsKqY1KikUjb7seeGI958=; b=DKoclDvMwzYxrV8ikDz30jJZ0MjrWw9ZMNSZzMyV3wd2IDfryqWIIN35xq/tx17VcB N4bGaHHeKXp4hY2sF9+/TZaPY/3Oqr2NHdMAbmZTcXzNppfXvNUS7pVqHL8tlF5nKA1a 4oaH1nc4GzKioKAfd1uTLAuBMz2gWn4W4sEiUP9BFQMDLPQXoFmgbGa06c6MCJaxLntk G/JeElxG7WBgXbYDJAD5LTak1anUVZI4ctMItX3XH32Qfu8IjsEJh3i49CLVncFtq5GW oxuBV9ctBx6tImEWrPsYsYwZlSC7waKqw+nlOdvNSIaro076zOroG+QZPrYEsd21ugMM XDVw== X-Received: by 10.194.177.167 with SMTP id cr7mr88256423wjc.19.1427971660642; Thu, 02 Apr 2015 03:47:40 -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 d9sm7147519wib.20.2015.04.02.03.47.39 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 02 Apr 2015 03:47:40 -0700 (PDT) Content-Type: multipart/alternative; boundary="Apple-Mail=_6EDE33D3-504D-48EB-9FA8-9B648821AEC2" 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 12:47:38 +0200 Cc: "pgsql-performance@postgresql.org" Message-Id: <2F18FF84-5408-4AE8-9F2B-9110F76DED86@gmail.com> References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com> To: Jeff Janes 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=_6EDE33D3-504D-48EB-9FA8-9B648821AEC2 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=windows-1252 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. 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=92ve searched just now what a collation is because I=92ve never = explicitly used one before, so I think it uses the default one. B_2 query is of the form: WITH soggetti AS ( SELECT ... FROM ... GROUP BY ...) SELECT ... INTO ... FROM soggetti, ... WHERE ...=20 (I omit the =85 part because they=92re not relevant) Best regards, Pietro PS it=92s the first time for me on this list so I don=92t know if you = read the other answers. I reported the postgresql.conf for both systems Il giorno 01/apr/2015, alle ore 18:38, Jeff Janes = ha scritto: > On Wed, Apr 1, 2015 at 6:56 AM, Pietro Pugni = wrote: > This question was posted originally on = http://dba.stackexchange.com/questions/96444/cant-get-dell-pe-t420-perc-h7= 10-perform-better-than-a-macmini-with-postgresql and they suggested to = post it on this mailing list. >=20 > It's months that I'm trying to solve a performance issue with = PostgreSQL. I=92m able to give you all the technical details needed. >=20 > SYSTEM CONFIGURATION >=20 > Our deployment machine is a Dell PowerEdge T420 with a Perc H710 RAID = controller configured in this way: >=20 > two Intel Xeon E5-2640 v2 @2Ghz > PostgreSQL 9.4 (updated to the latest available version) > My personal low cost and low profile development machine is a MacMini = configured in this way: >=20 > one Intel i7 @2.2Ghz > PostgreSQL 9.0.13 (the original built-in shipped with OS X Server) >=20 > Using such different versions of PostgreSQL seems like a recipe for = frustration. >=20 >=20 > Here are two benchmarks generated using pg_test_fsync: >=20 >=20 > This is unlikely to be important for the type of workload you = describe. Fsyncs are the bottleneck for many short transactions, but = not often the bottleneck for very large transactions. >=20 >=20 >=20 > T420 >=20 > Query B_2 [95664.832 ms + 0.523 ms] http://explain.depesz.com/s/v06 >=20 > MacMini >=20 > Query B_2 [44890.813 ms] http://explain.depesz.com/s/y7Dk >=20 >=20 >=20 > What collation is used for both databases? Perhaps the T420 is using = a much slower collation. >=20 > How can you sort 2,951,191 but then materialize 4,458,971 rows out of = that? I've never seen that before. (Or, in the other plan, put = 2,951,191 rows into the sort from the CTE but get 4,458,971 out of the = sort? >=20 > Cheers, >=20 > Jeff --Apple-Mail=_6EDE33D3-504D-48EB-9FA8-9B648821AEC2 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=windows-1252
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.
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=92ve searched just now what a = collation is because I=92ve never explicitly used one before, so I think = it uses the default one.

B_2 query is of the = form:
WITH soggetti AS = (
 SELECT ... FROM ... = GROUP BY ...)
SELECT ... = INTO ... FROM soggetti, ... WHERE ... 

(I omit the =85 part because = they=92re not relevant)

Best = regards,
 Pietro

PS it=92s the = first time for me on this list so I don=92t know if you read the other = answers. I reported the postgresql.conf for both = systems




<= div>
Il giorno 01/apr/2015, alle ore 18:38, Jeff Janes <jeff.janes@gmail.com> ha = scritto:

On Wed, Apr 1, 2015 at 6:56 AM, Pietro Pugni <pietro.pugni@gmail.com> = wrote:

This question was posted = originally on http://dba.stackexchange.com/questions/96444/cant-get-de= ll-pe-t420-perc-h710-perform-better-than-a-macmini-with-postgresql = and they suggested to post it on this mailing list.

It's months = that I'm trying to solve a performance issue with PostgreSQL. I=92m able = to give you all the technical details needed.

SYSTEM CONFIGURATION

Our deployment machine = is a Dell PowerEdge T420 with a Perc H710 RAID controller configured in = this way:

  • two Intel Xeon E5-2640 v2 @2Ghz
  • PostgreSQL 9.4 (updated to the latest available version)

My personal low cost and low profile development machine is a = MacMini configured in this way:

  • one Intel i7 @2.2Ghz
  • PostgreSQL 9.0.13 (the original built-in shipped with OS X = Server)

Using such = different versions of PostgreSQL seems like a recipe for = frustration.


Here are two = benchmarks generated using = pg_test_fsync:


This is unlikely to be important for the type of workload you = describe.  Fsyncs are the bottleneck for many short transactions, = but not often the bottleneck for very large = transactions.


T420

Q= uery B_2 [95664.832 ms + 0.523 ms] http://explain.depesz.com/s/v06

Ma= cMini

Query B_2 [44890.813 ms] http://explain.depesz.com/s/y7Dk



What collation is used for both = databases?  Perhaps the T420 is using a much slower = collation.

How can you sort 2,951,191 but then materialize = 4,458,971 rows out of that?  I've never seen that before. =  (Or, in the other plan, put 2,951,191 rows into the sort from = the CTE but get 4,458,971 out of the sort?

Cheers,

Jeff

= --Apple-Mail=_6EDE33D3-504D-48EB-9FA8-9B648821AEC2--