Message-ID: <50752DB2.6060004@ulb.ac.be>
Date: Wed, 10 Oct 2012 10:11:30 +0200
From: Julien Cigar <jcigar@ulb.ac.be>
Organization: Belgian Biodiversity Platform
User-Agent: Mozilla/5.0 (X11; FreeBSD i386;
	rv:12.0) Gecko/20120503 Thunderbird/12.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: shared_buffers/effective_cache_size on 96GB server
References: 
 <CADKbJJUTr9ZWkbajR-7_6qaAsWH0=09=2AMo67C_Az-raKZE4w@mail.gmail.com>
In-Reply-To: 
 <CADKbJJUTr9ZWkbajR-7_6qaAsWH0=09=2AMo67C_Az-raKZE4w@mail.gmail.com>
Content-Type: multipart/mixed; boundary="------------090909050505020604030900"

This is a multi-part message in MIME format.
--------------090909050505020604030900
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 10/10/2012 09:12, Strahinja Kustudić wrote:
> Hi everyone,

Hello,

>
> I have a Postgresql 9.1 dedicated server with 16 cores, 96GB RAM and 
> RAID10 15K SCSI drives which is runing Centos 6.2 x64. This server is 
> mainly used for inserting/updating large amounts of data via 
> copy/insert/update commands, and seldom for running select queries.
>
> Here are the relevant configuration parameters I changed:
>
> shared_buffers = 10GB

Generally going over 4GB for shared_buffers doesn't help.. some of the 
overhead of bgwriter and checkpoints is more or less linear in the size 
of shared_buffers ..

> effective_cache_size = 90GB

effective_cache_size should be ~75% of the RAM (if it's a dedicated server)

> work_mem = 32MB

with 96GB of RAM I would raise default work_mem to something like 128MB

> maintenance_work_mem = 512MB

again, with 96GB of ram you can raise maintenance_work_mem to something 
like 4GB

> checkpoint_segments = 64
> checkpoint_completion_target = 0.8
>
> My biggest concern are shared_buffers and effective_cache_size, should 
> I increase shared_buffers and decrease effective_cache_size? I read 
> that values above 10GB for shared_buffers give lower performance, than 
> smaller amounts?
>
> free is currently reporting (during the loading of data):
>
> $ free -m
> total used free shared buffers cached
> Mem: 96730 96418 311 0 71 93120
> -/+ buffers/cache: 3227 93502
> Swap: 21000 51 20949
>
> So it did a little swapping, but only minor, still I should probably 
> decrease shared_buffers so there is no swapping at all.
>
> Thanks in advance,
> Strahinja

Julien


-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.


--------------090909050505020604030900
Content-Type: text/x-vcard; charset=utf-8;
 name="jcigar.vcf"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="jcigar.vcf"

YmVnaW46dmNhcmQNCmZuOkp1bGllbiBDaWdhcg0KbjpDaWdhcjtKdWxpZW4NCm9yZztxdW90
ZWQtcHJpbnRhYmxlOkJlbGdpYW4gQmlvZGl2ZXJzaXR5IFBsYXRmb3JtO1VMQiAoVW5pdmVy
c2l0PUMzPUE5IExpYnJlIGRlIEJydXhlbGxlcykNCmFkcjo7OztCcnVzc2Vsczs7O0JlbGdp
dW0NCmVtYWlsO2ludGVybmV0OmpjaWdhckB1bGIuYWMuYmUNCnRlbDt3b3JrOiszMigwKTI2
NTA1NzUyDQp4LW1vemlsbGEtaHRtbDpGQUxTRQ0KdXJsOmh0dHA6Ly93d3cuYmlvZGl2ZXJz
aXR5LmJlDQp2ZXJzaW9uOjIuMQ0KZW5kOnZjYXJkDQoNCg==
--------------090909050505020604030900--