Re: shared_buffers/effective_cache_size on 96GB server

@Bruce Thanks for your articles, after reading them all I don't think
disabling swap is a good idea now. Also you said to see the
effective_cache_size I should check it with free. My question is should I
use the value that free is showing as cached, or a little lower one, since
not everything in the cache is because of Postgres.

@Claudio So you are basically saying that if I have set
effective_cache_size to 10GB and I have 10 concurrent processes which are
using 10 different indices which are for example 2GB, it would be better to
set the effective_cache size to 1GB? Since if I leave it at 10GB each
running process query planner will think the whole index is in cache and
that won't be true? Did I get that right?

@Jeff I have 4 drives in RADI10. The database has around 80GB of indices.
I'm not experiencing any slow downs, I would just like to increase the
performance of update/insert, since it needs to insert a lot of data and to
make the select queries faster since they are done on a lot of big tables.
I am experiencing a lot of performance problems when autovacuum kicks in
for a few big tables, since it slows downs things a lot. I didn't notice
any swapping and I know those 51MB which were swapped were just staying
there, so swap isn't an issue at all.

Strahinja Kustudić | System Engineer | Nordeus

On Wed, Oct 10, 2012 at 9:30 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Wed, Oct 10, 2012 at 12:12 AM, Strahinja Kustudić
> <strahinjak(at)nordeus(dot)com> 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.
>
> How many drives in the RAID?
>
> > This server is mainly used
> > for inserting/updating large amounts of data via copy/insert/update
> > commands, and seldom for running select queries.
>
> Are there a lot of indexes?
>
> >
> > 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?
>
> Are you experiencing performance problems? If so, what are they?
>
> > I read that
> > values above 10GB for shared_buffers give lower performance, than smaller
> > amounts?
>
> There are reports that large shared_buffers can lead to latency
> spikes. I don't know how sensitive your work load is to latency,
> though. Nor how much those reports apply to 9.1.
>
> >
> > 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,
>
> The kernel has, over the entire time the server has been up, found 51
> MB of process memory to swap. That doesn't really mean anything. Do
> you see active swapping going on, like with vmstat?
>
>
> Cheers,
>
> Jeff
>