From: Greg Smith <greg@2ndquadrant.com>
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: FabrÃcio dos Anjos Silva <fabricio.silva@linkcom.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: How does PG know if data is in memory?
Date: Sun, 03 Oct 2010 23:07:27 -0400
Message-ID: <4CA944EF.5040702@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTik5hnOcU=spN0wmWmR6_wHN=sKfVNzJeDuPTVW_@mail.gmail.com>
References: <AANLkTin5G0jXUbeNdC+1s9LQpk1ZpvDuX-6n90oonen3@mail.gmail.com>
<AANLkTik5hnOcU=spN0wmWmR6_wHN=sKfVNzJeDuPTVW_@mail.gmail.com>
Samuel Gendler wrote:
> As to your question about increasing shared_buffers to be some
> significant proportion of available RAM - apparently, that is not a
> good idea. I've seen advice that said you shouldn't go above 8GB for
> shared_buffers and I've also seen 12GB suggested as an upper limit,
> too. On my host with 48GB of RAM, I didn't see much difference
> between 8GB and 12GB on a fairly wide variety of tests, so mine is set
> at 8GB with an efective_cache_size of 36GB.
The publicly discussed tests done at Sun suggested 10GB was the
effective upper limit on Solaris before performance started dropping
instead of increasing on some of their internal benchmarks. And I've
heard privately from two people who have done similar experiments on
Linux and found closer to 8GB to be the point where performance started
to drop. I'm hoping to get some hardware capable of providing some more
public results in this area, and some improvements if we can get better
data about what causes this drop in efficiency.
Given that some write-heavy workloads start to suffer considerable
checkpoint issues when shared_buffers is set to a really high value,
there's at least two reasons to be conservative here. The big win is
going from the tiny default to hundreds of megabytes. Performance keeps
going up for many people into the low gigabytes range, but the odds of
hitting a downside increase too. Since PostgreSQL uses the OS cache,
too, I see some sytems with a whole lot of RAM where the 512MB - 1GB
range still ends up being optimal, just in terms of balancing the
improvements you get from things being in the cache vs. the downsides of
heavy checkpoint writes.
--
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance" Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, sgendler@ideasculptor.com, fabricio.silva@linkcom.com.br
Subject: Re: How does PG know if data is in memory?
In-Reply-To: <4CA944EF.5040702@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox