From: Mkrtchyan, Tigran <tigran.mkrtchyan@desy.de>
To: Pietro Pugni <pietro.pugni@gmail.com>
Cc: ik@postgresql-consulting.com, pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
Date: Thu, 2 Apr 2015 14:22:57 +0200 (CEST)
Message-ID: <1095510641.1517209.1427977377666.JavaMail.zimbra@desy.de> (raw)
In-Reply-To: <9A77A65B-061D-4712-A21D-83B02DF18C57@gmail.com>
References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com>
<CAG95seWrfjHFwQEtn=0D-k0ex+Ag7cbOuFuaz4jgMhC9NJxp=A@mail.gmail.com>
<9A77A65B-061D-4712-A21D-83B02DF18C57@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tigran.mkrtchyan@desy.de, pietro.pugni@gmail.com
Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
In-Reply-To: <1095510641.1517209.1427977377666.JavaMail.zimbra@desy.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox