Обсуждение: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL

Here are two benchmarks generated using pg_test_fsync:

T420 pg_test_fsync

60 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)       open_datasync                   23358.758 ops/sec      43 usecs/op       fdatasync                       21417.018 ops/sec      47 usecs/op       fsync                           21112.662 ops/sec      47 usecs/op       fsync_writethrough                            n/a       open_sync                       23082.764 ops/sec      43 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)       open_datasync                   11737.746 ops/sec      85 usecs/op       fdatasync                       19222.074 ops/sec      52 usecs/op       fsync                           18608.405 ops/sec      54 usecs/op       fsync_writethrough                            n/a       open_sync                       11510.074 ops/sec      87 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)        1 * 16kB open_sync write       21484.546 ops/sec      47 usecs/op        2 *  8kB open_sync writes      11478.119 ops/sec      87 usecs/op        4 *  4kB open_sync writes       5885.149 ops/sec     170 usecs/op        8 *  2kB open_sync writes       3027.676 ops/sec     330 usecs/op       16 *  1kB open_sync writes       1512.922 ops/sec     661 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)       write, fsync, close             17946.690 ops/sec      56 usecs/op       write, close, fsync             17976.202 ops/sec      56 usecs/op

Non-Sync'ed 8kB writes:       write                           343202.937 ops/sec       3 usecs/op

MacMini pg_test_fsync

60 seconds per test
Direct I/O is not supported on this platform.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)       open_datasync                      3780.341 ops/sec     265 usecs/op       fdatasync                          3117.094 ops/sec     321 usecs/op       fsync                              3156.298 ops/sec     317 usecs/op       fsync_writethrough                  110.300 ops/sec    9066 usecs/op       open_sync                          3077.932 ops/sec     325 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)       open_datasync                      1522.400 ops/sec     657 usecs/op       fdatasync                          2700.055 ops/sec     370 usecs/op       fsync                              2670.652 ops/sec     374 usecs/op       fsync_writethrough                   98.462 ops/sec   10156 usecs/op       open_sync                          1532.235 ops/sec     653 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)        1 * 16kB open_sync write          2634.754 ops/sec     380 usecs/op        2 *  8kB open_sync writes         1547.801 ops/sec     646 usecs/op        4 *  4kB open_sync writes          801.542 ops/sec    1248 usecs/op        8 *  2kB open_sync writes          405.515 ops/sec    2466 usecs/op       16 *  1kB open_sync writes          204.095 ops/sec    4900 usecs/op

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)       write, fsync, close                2747.345 ops/sec     364 usecs/op       write, close, fsync                3070.877 ops/sec     326 usecs/op

Non-Sync'ed 8kB writes:       write                              3275.716 ops/sec     305 usecs/op

This confirms the hardware IO capabilities of T420 but doesn't explain why MacMini is MUCH MORE FAST.

Now let’s propose some query profiling times.

B type set are transactions, so it's impossible for me to post EXPLAIN ANALYZE results. I've extracted two querys from a single transactions and executed the twos on both system. Here are the results:

T420

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

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

./configure CFLAGS="-O3 -fno-inline-functions -march=native" --with-openssl --with-libxml --with-libxslt --with-wal-blocksize=64 --with-blocksize=32 --with-wal-segsize=64 --with-segsize=1

I've also tried to disable Hyper-Threading with echo 0 > /sys/devices/system/cpu/cpuN/online where cpuN is the N-th logical CPU but nothing changed over B set queries. We have 2 CPU with 8 cores for a total of 16 physical cores and 16 logical cores.

Ok, a quick view on the system, and some things that may be important to note:

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

   * VD0: two 15k SAS disks (ext4, OS partition, WAL partition,
   RAID1)
   * VD1: ten 10k SAS disks (XFS, Postgres data partition, RAID5)

Well...usually RAID5 have the worst performance in writing...EVER!!! Have you tested this in another raid configuration? RAID10 is usually the best bet.

This system has the following configuration:

   * Ubuntu 14.04.2 LTS (GNU/Linux 3.13.0-48-generic x86_64)
   * 128GB RAM (DDR3, 8x16GB @1600Mhz)
   * two Intel Xeon E5-2640 v2 @2Ghz
   * Dell Perc H710 with 512MB RAM (Write cache: "WriteBack", Read
   cache: "ReadAhead", Disk cache: "disabled"):
       * VD0 (OS and WAL partition): two 15k SAS disks (ext4, RAID1)
       * VD1 (Postgres data partition): ten 10k SAS disks (XFS,
       RAID5)
   * PostgreSQL 9.4 (updated to the latest available version)
   * moved pg_stat_tmp to RAM disk

[...]> versions.

You did not mention any "postgres" configuration at all. If you let the default checkpoint_segments=3, that would be an IO hell for your disk controler...and the RAID5 making things worst...Can you show us the values of:

checkpoint_segments
shared_buffers
work_mem
maintenance_work_mem
effective_io_concurrency

I would start from there, few changes, and check again. I would change the RAID first of all things, and try those tests again.

Cheers.
Gerardo

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-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

Just looking at the 2 B_2 queries, I'm curious as to why is the execution plan different between the 2 machines. Is the optimiser stats updated on both databases?

Regards,

Wei Shan

On 1 April 2015 at 22:32, Aidan Van Dyk <aidan@highrise.ca> wrote:

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

 

Now let’s propose some query profiling times.

B type set are transactions, so it's impossible for me to post EXPLAIN ANALYZE results. I've extracted two querys from a single transactions and executed the twos on both system. Here are the results:

T420

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

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

Looking at the 2 B_2 queries (since they are so drastically different), the in-memory quicksorts stand out on the Dell as being *drastically* slower than the disk-based sorts on your mac-mini....

--

Regards,
Ang Wei Shan

Hi Pietro,

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

T420: went from 311seconds (default postgresql.conf) to 195seconds doing
tuning adjustments over RAID, kernel and postgresql.conf;
MacMini: 40seconds.

I'am afraid, the matter is, that PostgreSQL is not configured properly
(and so do operating system and probably controller, however
pg_test_fsync shows that things are not so bad there as with
postgresql.conf).

It is pretty useless to benchmark a database using out-of-the-box
configuration. You need at least configure shared memory related,
checkpoints-related and autovacuum-related settings. And as a first
step, please compare postgresql.conf on Mac and on the server:
sometimes (with some mac installers) default postgresql.conf can be
not the same as on server.

Best regards,
Ilya


--
Ilya Kosmodemiansky,

PostgreSQL-Consulting.com
tel. +14084142500
cell. +4915144336040
ik@postgresql-consulting.com

 

T420

work_mem = 512MB

 

MacMini

work_mem = 32MB

So that is why the T420 does memory sorts and the mini does disk sorts.

I'd start looking at why memory sorts on the T420 is so slow.   Check your numa settings, etc (as already mentioned).

For a drastic test, disable the 2nd socket on the dell, and just use one (eliminate any numa/QPI costs) and see how it compares to the no-numa MacMini.

If you want to see how bad the NUMA/QPI is, play with stream to benchmark memory performance.

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’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.

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.

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.

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.

The modern CPUs trying to be too smart.

try to run this code to disable CPUs c-states:

----> setcpulatency.c

#include <stdio.h>
#include <fcntl.h>
#include <stdint.h>

int main(int argc, char **argv) {
  int32_t l;
  int fd;

  if (argc != 2) {
     fprintf(stderr, "Usage: %s <latency in us>\n", argv[0]);
     return 2;
  }

  l = atoi(argv[1]);
  printf("setting latency to %d us\n", l);
  fd = open("/dev/cpu_dma_latency", O_WRONLY);
  if (fd < 0) {
     perror("open /dev/cpu_dma_latency");
     return 1;
  }

  if (write(fd, &l, sizeof(l)) != sizeof(l)) {
     perror("write to /dev/cpu_dma_latency");
     return 1;
  }

  while (1) pause();
}


——>

you can use i7z (https://code.google.com/p/i7z/) to see the percentage of CPU power to be used.

Changing CPU from C1 to C0 takes quite some time and for DB workload not optimal (if you need a
high throughout and any given moment).

I see ~65% boost when run './setcpulatency 0'.

Tigran.

there's also huge page
/sys/kernel/mm/transparent_hugepage/enabled
can you try to disable it?

Also test on the dell:
select tmp.cf, tmp.dt from grep_studi.tmp;
and
select tmp.cf, tmp.dt from grep_studi.tmp order by tmp.cf;
in Query B_2
the sort is 9 time slower on the dell, you have to find why…

32 GB for buffers is too high for the queries in your test but it
doesn't matter.

NUMA stands for "Non-Uniform-Memory-Access" .  It's basically the "label" for systems which have memory attached to different cpu sockets, such that accessing all of the memory from a paritciular cpu thread has different costs based on where the actual memory is located (i.e. on some other socket, or the local socket).

QPI is the the intel "QuickPath Interconnect". It's the communication path between CPU sockets.   Memory ready by one cpu thread that has to come from another cpu socket's memory controller goes through QPI.

Google has lots of info on these, and how they impact performance, etc.

If you want to see how bad the NUMA/QPI is, play with stream to benchmark memory performance.

With stream you refer to this: https://sites.utexas.edu/jdm4372/tag/stream-benchmark/ ? Do you suggest me some way to do this kind of tests?

Ya, that's the one.  I don't have specific tests in mind.

A more simple "overview" might be "numactl —hardware”

Did you already post the results of:

cat /proc/sys/vm/zone_reclaim_mode

Also, how big did you say your dataset is? Based on the output of free, you're certainly not using all the memory you have.  That could be just because you haven't accessed that much of your dataset, or it could be because zone reclaim is preventing you from using your entire amount of RAM as file system cache.

On Tue, Apr 7, 2015 at 6:27 AM, Pietro Pugni <pietro.pugni@gmail.com> wrote:

Hi Jeff,

sorry for the latency but server was down due to a error I made in the sysctl.conf file.

Yes, but are the defaults for those two systems?  on psql, use \l to see.

\l returns the following:

T420 (Postgres 9.4.1)

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+----------+----------+-------------+-------------+-----------------------

 grep      | grep     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(4 rows)

MacMini (Postgres 9.0.13)

                                    List of databases

       Name        |   Owner    | Encoding | Collation | Ctype |    Access privileges    

-------------------+------------+----------+-----------+-------+-------------------------

 caldav            | caldav     | UTF8     | C         | C     | 

 collab            | collab     | UTF8     | C         | C     | 

 device_management | _devicemgr | UTF8     | C         | C     | 

 pen               | pen        | UTF8     | C         | C     | 

 postgres          | _postgres  | UTF8     | C         | C     | 

 roundcubemail     | roundcube  | UTF8     | C         | C     | 

 template0         | _postgres  | UTF8     | C         | C     | =c/_postgres           +

                   |            |          |           |       | _postgres=CTc/_postgres

 template1         | _postgres  | UTF8     | C         | C     | =c/_postgres           +

                   |            |          |           |       | _postgres=CTc/_postgres

(8 rows)

The difference between the "C" and the "en_US" collation is entirely sufficient to explain the difference in performance.  "C" is the fastest possible collation as it never needs to look ahead or consult tables, it just compares raw bytes.

Cheers,

Jeff

On Tue, Apr 7, 2015 at 6:27 AM, Pietro Pugni <pietro.pugni@gmail.com> wrote:

Hi Jeff,

sorry for the latency but server was down due to a error I made in the sysctl.conf file.

Yes, but are the defaults for those two systems?  on psql, use \l to see.

\l returns the following:

T420 (Postgres 9.4.1)

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+----------+----------+-------------+-------------+-----------------------

 grep      | grep     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

(4 rows)

MacMini (Postgres 9.0.13)

                                    List of databases

       Name        |   Owner    | Encoding | Collation | Ctype |    Access privileges    

-------------------+------------+----------+-----------+-------+-------------------------

 caldav            | caldav     | UTF8     | C         | C     | 

 collab            | collab     | UTF8     | C         | C     | 

 device_management | _devicemgr | UTF8     | C         | C     | 

 pen               | pen        | UTF8     | C         | C     | 

 postgres          | _postgres  | UTF8     | C         | C     | 

 roundcubemail     | roundcube  | UTF8     | C         | C     | 

 template0         | _postgres  | UTF8     | C         | C     | =c/_postgres           +

                   |            |          |           |       | _postgres=CTc/_postgres

 template1         | _postgres  | UTF8     | C         | C     | =c/_postgres           +

                   |            |          |           |       | _postgres=CTc/_postgres

(8 rows)

The difference between the "C" and the "en_US" collation is entirely sufficient to explain the difference in performance.  "C" is the fastest possible collation as it never needs to look ahead or consult tables, it just compares raw bytes.

Cheers,

Jeff

Hi Jeff,

is there a way to set a default collection during compiling or in the configuration file? I have never specified one, so I suppose that somewhere on MacMini “C” collation type is set as the default one.

Thank you a lot.

Best regards,

 Pietro

Ciao Pietro,

stavo seguendo thread sulla mailing list Postgresql.

Puoi farmi un piccolo riassunto delle conclusioni perchè non sono sicuro di aver capito tutto?

Alla fine la differenza di performance tra il DELL R420 e il Mac MINI è dovuta al tipo di "collate" utilizzato nell'inizializzazione del DB?