Re: Large PostgreSQL servers

On Wed, Mar 21, 2012 at 2:31 PM, Kjetil Nygård <polpot78(at)gmail(dot)com> wrote:
> Hi,
>
> We are considering to migrate some of our databases to PostgreSQL.
>
> We wonder if someone could give some hardware / configuration specs for
> large PostgreSQL installations.
> We're interested in:
>        - Number of CPUs
>        - Memory on the server
>        - shared_buffers
>        - Size of the database on disk
>
>
>
> PS: I have read in "PosgreSQL 9.0 High Performance" that one should not
> use more than 8GB for shared_buffers. But Robert Haas and comments say
> that one can use a lot more.
> http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html

If your database (or at least, the portion of it that sees regular
activity) fits completely in shared_buffers, it's a win because they
are faster than the o/s filesystem cache and they don't have to get
paged in and out. OTOH, if your database does not fit, you can get
performance issues relating to them getting pushed in and out.
Another disadvantage of large shared buffers settings is it reduces
the amount of memory for other things, like temporary demands (sorts,
large result sets) or cached structures like plpgsql plans. Once you
go over 50% memory into shared, it's pretty easy to overcommit your
server and burn yourself. Of course, 50% of 256GB server is a very
different animal than 50% of a 4GB server.

Here's the takeaway for shared_buffers.
*) it's a nuanced setting. for single user workloads its affects are
usually undetectable
*) it's more important for high write activity workloads. for low
user high read olap type workloads, I usually set it lower, perhaps
even to 256mb -- it doesn't help all that much and i'd rather have
that memory be on demand for the o/s
*) don't be afraid to buck the conventional wisdom if you're not
seeing the performance you think you should be getting (especially on
writes). higher or lower shared_buffers can work
*) lots of other variables are at play -- o/s page flush policy for example.
*) it's unclear right now what the upcoming revolution in faster
storage means for database configuration and tuning. my gut feeling
is that it's going to be generally less important as databases become
primarily cpu,lock, and algorithm (query plan) bound.
*) beware memory over commit.

merlin