Date: Thu, 2 Apr 2015 14:22:57 +0200 (CEST)
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>
Message-ID: <1095510641.1517209.1427977377666.JavaMail.zimbra@desy.de>
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>
Subject: Re: Can't get Dell PE T420 (Perc H710) perform better
 than a MacMini with PostgreSQL
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Thread-Topic: Can't get Dell PE T420 (Perc H710) perform better than a MacMini
 with PostgreSQL
Thread-Index: gaiXWYcFS24RsaP8Ar1JIG5GoKv0Mg==
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi Pietro,

The modern CPUs trying to be too smart.

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

----> setcpulatency.c=20

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

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

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

   l =3D atoi(argv[1]);
   printf("setting latency to %d us\n", l);
   fd =3D open("/dev/cpu_dma_latency", O_WRONLY);
   if (fd < 0) {
      perror("open /dev/cpu_dma_latency");
      return 1;
   }
=20=20=20
   if (write(fd, &l, sizeof(l)) !=3D sizeof(l)) {
      perror("write to /dev/cpu_dma_latency");
      return 1;
   }
=20=20=20
   while (1) pause();
}


---->

you can use i7z (https://code.google.com/p/i7z/) to see the percentage of C=
PU power to be used.
Changing CPU from C1 to C0 takes quite some time and for DB workload not op=
timal (if you need a=20
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=E2=80=99ve done. On T420 I=E2=
=80=99ve optimized the
> kernel following the official Postgres documentation (
> http://www.postgresql.org/docs/9.4/static/kernel-resources.html ):
> kernel.shmmax=3D68719476736
> kernel.shmall=3D16777216
> vm.overcommit_memory=3D2
> vm.overcommit_ratio=3D90
>=20
>=20
> RAID controllers were configured as following:
> - Write cache: WriteBack
> - Read cache: ReadAhead
> - Disk cache (only T420): disabled to take full advantage of WriteBack ca=
che
> (BBU is charged and working)
> - NCQ (only MacMini because it=E2=80=99s a SATA option): enabled (this af=
fects a lot the
> overall performance)
>=20
> For postgresql.conf:
>=20
> T420
> Normal operations
> autovacuum =3D on
> maintenance_work_mem =3D 512MB
> work_mem =3D 512MB
> wal_buffers =3D 64MB
> effective_cache_size =3D 64GB # this helps A LOT in disk write speed when=
 creating
> indexes
> shared_buffers =3D 32GB
> checkpoint_segments =3D 2000
> checkpoint_completion_target =3D 1.0
> effective_io_concurrency =3D 0 # 1 doesn=E2=80=99t make any substantial d=
ifference
> max_connections =3D 10 # 20 doesn=E2=80=99t make any difference
>=20
> Data loading (same as above with the following changes):
> autovacuum =3D off
> maintenance_work_mem =3D 64GB
>=20
>=20
> MacMini
> Normal operations
> autovacuum =3D on
> maintenance_work_mem =3D 128MB
> work_mem =3D 32MB
> wal_buffers =3D 32MB
> effective_cache_size =3D 800MB
> shared_buffers =3D 512MB
> checkpoint_segments =3D 32
> checkpoint_completion_target =3D 1.0
> effective_io_concurrency =3D 1
> max_connections =3D 20
>=20
> Data loading (same as above with the following changes):
> autovacuum =3D off
> maintenance_work_mem =3D 6GB
>=20
>=20
> Best regards,
> Pietro
>=20
>=20
>=20
> Il giorno 01/apr/2015, alle ore 16:27, Ilya Kosmodemiansky
> <ilya.kosmodemiansky@postgresql-consulting.com> ha scritto:
>=20
>> Hi Pietro,
>>=20
>> On Wed, Apr 1, 2015 at 3:56 PM, Pietro Pugni <pietro.pugni@gmail.com> wr=
ote:
>>> T420: went from 311seconds (default postgresql.conf) to 195seconds doing
>>> tuning adjustments over RAID, kernel and postgresql.conf;
>>> MacMini: 40seconds.
>>=20
>> 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).
>>=20
>> 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.
>>=20
>> Best regards,
>> Ilya
>>=20
>>=20
>> --
>> Ilya Kosmodemiansky,
>>=20
>> PostgreSQL-Consulting.com
>> tel. +14084142500
>> cell. +4915144336040
> > ik@postgresql-consulting.com


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance