Обсуждение: how to make PostgreSQL using "all" memory and chaching the DB completely there

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 :)


Cheers,
Chris.

2011/7/14 Christoph Anton Mitterer
<christoph.anton.mitterer@physik.uni-muenchen.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.

>Christoph Anton Mitterer
<christoph.anton.mitterer@physik.uni-muenchen.de>
wrote:

> 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).

In one cluster (postmaster instance) or separate?

> Currently it's 8.4 but we shall switch to 9.x eventually. The
> database is (physical) size is currently at about 30 GB.

30 GB is the total of all the databases?

> What I'd like to have, is that Postgresql uses at MAX say 50%
> (~50GB) of the available memory.

The only way to do that is with OS limits, like ulimit.  Of course,
if you set the shared_buffers well under that and use reasonable
settings for other parameters, it won't tend to go above that unless
the RAM is unused by any other process.  If you really want to be
sure some amount of RAM is not used by *any* process, the best way
is to pull it out of the machine and set it on a shelf -- it will do
as much good there as sitting unused in a machine.  ;-)

> And there it should completely cache the DB for _reading_.

If there's sufficient unused RAM on the machine, it will do this
automatically.

> When any writes occur, these should still be sent "immediately" to
> disk.

The writes are persisted on COMMIT (before the COMMIT statement
completes), unless you configure for unsafe values.  Make sure that
fsync, full_page_writes, and synchronous_commit are all on.

Make sure you have a good RAID controller with battery-back-up
caching configured for write-back.

-Kevin

Christoph Anton Mitterer
<christoph.anton.mitterer@physik.uni-muenchen.de>
wrote:

> Well I'm still very unsure on how to configure many values...
> We've increased e.g. shared_buffers, temp_buffers, work_mem,
> maintenance_work_mem, max_stack_depth... and this greatly improved
> performance. But I can hardly judge to which values I should
> increase all these (and a few more).

Maybe the ellipses cover these, but wal_buffers and
checkpoint_segments should generally be adjusted, too.  I almost
always need to tweak some of the costing factors, too; but
appropriate settings there depend not only on your hardware, but
also your schema, data, and application mix.

The best source of information on this that I know is Greg Smith's
"PostgreSQL 9.0 High Performance" book.  (It also discusses older
versions, so don't let the "9.0" in the title put you off.)  Some of
these settings are best tuned through an iterative process of
monitoring while making small adjustments.

http://www.postgresql.org/docs/books/

-Kevin