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

[Pietro Pugni <pietro.pugni@xxxxxxxxx>]

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.

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.

B_2 query is of the form:

WITH soggetti AS (

 SELECT ... FROM ... GROUP BY ...)

SELECT ... INTO ... FROM soggetti, ... WHERE ... 

(I omit the … part because they’re not relevant)

Best regards,

 Pietro

PS it’s the first time for me on this list so I don’t 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 <jeff.janes@xxxxxxxxx> ha scritto:

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

This question was posted originally on http://dba.stackexchange.com/questions/96444/cant-get-dell-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’m 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

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

MacMini

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