Message-ID: <507524A6.8000505@fuzzy.cz>
Date: Wed, 10 Oct 2012 09:32:54 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:15.0) Gecko/20120907 Thunderbird/15.0.1
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: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

On 10.10.2012 09:12, Strahinja Kustudi=C4=87 wrote:
> Hi everyone,
>=20
> 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.
>=20
> Here are the relevant configuration parameters I changed:
>=20
> shared_buffers =3D 10GB
> effective_cache_size =3D 90GB
> work_mem =3D 32MB
> maintenance_work_mem =3D 512MB
> checkpoint_segments =3D 64
> checkpoint_completion_target =3D 0.8
>=20
> 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?
>=20
> free is currently reporting (during the loading of data):
>=20
> $ free -m
>              total       used       free     shared    buffers     cach=
ed
> Mem:         96730      96418        311          0         71      931=
20
> -/+ buffers/cache:       3227      93502
> Swap:        21000         51      20949
>=20
> So it did a little swapping, but only minor, still I should probably
> decrease shared_buffers so there is no swapping at all.

That's hardly caused by shared buffers. The main point is that
effective_cache_size is just a hint to the optimizer how much cache
(shared buffers + page cache) to expect. So it's unlikely PostgreSQL is
going to allocate 100GB of RAM or something.

What have you set to the main /proc/sys/vm/ parameters? Mainly these thre=
e:

/proc/sys/vm/swappiness
/proc/sys/vm/overcommit_memory
/proc/sys/vm/overcommit_ratio


Tomas