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.kosmodemian...@postgresql-consulting.com> ha scritto: > Hi Pietro, > > On Wed, Apr 1, 2015 at 3:56 PM, Pietro Pugni <pietro.pu...@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 > i...@postgresql-consulting.com
