Re: shared_buffers/effective_cache_size on 96GB server

On 10.10.2012 09:12, Strahinja Kustudić wrote:
> Hi everyone,
>
> 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
> effective_cache_size = 90GB
> work_mem = 32MB
> maintenance_work_mem = 512MB
> 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.

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 three:

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

Tomas