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

Hi Pietro,

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.

----- Original Message -----
> From: "Pietro Pugni" <pietro.pugni@gmail.com>
> To: ik@postgresql-consulting.com
> Cc: "pgsql-performance" <pgsql-performance@postgresql.org>
> Sent: Thursday, April 2, 2015 12:57:22 PM
> Subject: Re: [PERFORM] Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL

> Hi Ilya,
> thank your for your response.
> Both system were configured for each test I’ve done. On T420 I’ve optimized the
> kernel following the official Postgres documentation (
> http://www.postgresql.org/docs/9.4/static/kernel-resources.html ):
> kernel.shmmax=68719476736
> kernel.shmall=16777216
> vm.overcommit_memory=2
> vm.overcommit_ratio=90
>
>
> RAID controllers were configured as following:
> - Write cache: WriteBack
> - Read cache: ReadAhead
> - Disk cache (only T420): disabled to take full advantage of WriteBack cache
> (BBU is charged and working)
> - NCQ (only MacMini because it’s a SATA option): enabled (this affects a lot the
> overall performance)
>
> For postgresql.conf:
>
> T420
> Normal operations
> autovacuum = on
> maintenance_work_mem = 512MB
> work_mem = 512MB
> wal_buffers = 64MB
> effective_cache_size = 64GB # this helps A LOT in disk write speed when creating
> indexes
> shared_buffers = 32GB
> checkpoint_segments = 2000
> checkpoint_completion_target = 1.0
> effective_io_concurrency = 0 # 1 doesn’t make any substantial difference
> max_connections = 10 # 20 doesn’t make any difference
>
> Data loading (same as above with the following changes):
> autovacuum = off
> maintenance_work_mem = 64GB
>
>
> MacMini
> Normal operations
> autovacuum = on
> maintenance_work_mem = 128MB
> work_mem = 32MB
> wal_buffers = 32MB
> effective_cache_size = 800MB
> shared_buffers = 512MB
> checkpoint_segments = 32
> checkpoint_completion_target = 1.0
> effective_io_concurrency = 1
> max_connections = 20
>
> Data loading (same as above with the following changes):
> autovacuum = off
> maintenance_work_mem = 6GB
>
>
> Best regards,
> Pietro
>
>
>
> Il giorno 01/apr/2015, alle ore 16:27, Ilya Kosmodemiansky
> <ilya.kosmodemiansky@postgresql-consulting.com> ha scritto:
>
>> 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