Re: 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(dot)kosmodemiansky(at)postgresql-consulting(dot)com> ha scritto:

> Hi Pietro,
>
> On Wed, Apr 1, 2015 at 3:56 PM, Pietro Pugni <pietro(dot)pugni(at)gmail(dot)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(at)postgresql-consulting(dot)com