Re: how to make PostgreSQL using "all" memory and chaching the DB completely there

2011/7/14 Christoph Anton Mitterer
<christoph(dot)anton(dot)mitterer(at)physik(dot)uni-muenchen(dot)de>:
> Hi.
>
> I've read through the documentation and while I've seen dozens of
> options which allow to set which kind of buffer/cache/shared-mem gets
> how big... I do not quite understand how to reach the following.
>
> What we have here at the institute is a quite powerful server, whit
> about 100GB RAM that has a PostgreSQL running on it (with several DBs).
> Currently it's 8.4 but we shall switch to 9.x eventually.
> The database is (physical) size is currently at about 30 GB.
>
> What I'd like to have, is that Postgresql uses at MAX say 50% (~50GB) of
> the available memory.
> And there it should completely cache the DB for _reading_. When any
> writes occur, these should still be sent "immediately" to disk.
> But I guess having the DB completely cached in RAM (and given the fact
> that we've got much more read requests than write requests) this should
> give us quite some performance boost.
>
>
> Any help/hints how to reasonable configure this would be highly
> appreciated :)

Generally speaking having the DB do all the caching via shared_buffers
has diminishing returns after 8 or 10 Gigs of caching. Let the OS
cache things, it's better at caching large data sets. Note that I'd
set swappiness to 0 or just turn off swap on a big memory machine.
We've had instances with Ubuntu 10.04 and stock up to date kernels
become pathological with large memory machines, and the easiest and
best solution was to simply turn off swap.