Hi Ilya,
thank your for your response.
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
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