From: Pietro Pugni <pietro.pugni@gmail.com>
To: ik@postgresql-consulting.com
Cc: pgsql-performance@postgresql.org <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 12:57:22 +0200
Message-ID: <9A77A65B-061D-4712-A21D-83B02DF18C57@gmail.com> (raw)
In-Reply-To: <CAG95seWrfjHFwQEtn=0D-k0ex+Ag7cbOuFuaz4jgMhC9NJxp=A@mail.gmail.com>
References: <732B95A3-FEC8-4623-9AC5-98FD30F00863@gmail.com>
<CAG95seWrfjHFwQEtn=0D-k0ex+Ag7cbOuFuaz4jgMhC9NJxp=A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: pietro.pugni@gmail.com, ik@postgresql-consulting.com
Subject: Re: Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL
In-Reply-To: <9A77A65B-061D-4712-A21D-83B02DF18C57@gmail.com>
* 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