Skip site navigation (1) Skip section navigation (2)

Re: shared_buffers/effective_cache_size on 96GB server

From: Strahinja Kustudić <strahinjak(at)nordeus(dot)com>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: shared_buffers/effective_cache_size on 96GB server
Date: 2012-10-10 20:12:51
Message-ID: CADKbJJXDiK6TP-gaj2vDuSF7fjoASQ3WecWQev2YVHo6GKNwmQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
@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
>

In response to

Responses

pgsql-performance by date

Next:From: Claudio FreireDate: 2012-10-10 20:48:18
Subject: Re: shared_buffers/effective_cache_size on 96GB server
Previous:From: Jeff JanesDate: 2012-10-10 19:30:46
Subject: Re: shared_buffers/effective_cache_size on 96GB server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group