Re: Configuring for maximum memory usage

On Thu, Oct 30, 2008 at 9:05 AM, Joshua D. Drake <jd(at)commandprompt(dot)com>wrote:

> On Thu, 2008-10-30 at 16:58 +0100, Christiaan Willemsen wrote:
> > Joshua D. Drake wrote:
> > >
> > > PostgreSQL is only going to use what it needs. It relies on the OS for
> > > much of the caching etc...
> > >
> > So that would actually mean that I could raise the setting of the ARC
> > cache to far more than 8 GB? As I said, our database is 250 GB, So I
> > would expect that postgres needs more than it is using right now...
> > Several tables have over 500 million records (obviously partitioned).
> > At the moment we are doing queries over large datasets, So I would
> > assume that postgress would need a bit more memory than this..
>
> Well I actually can't answer this definitely. My knowledge of Solaris is
> slimmer than my knowledge of other operating systems. However it appears
> from a brief google that ARC cache is some predefined file level cache
> that you can set with Solaris? If so, then you want that to be high
> enough to keep your most active relations hot in cache.
>
> Remember that PostgreSQL doesn't cache anything on its own so if you do
> want to hit disk it has to be in file cache.

By my understanding, this is absolutely false. Postgres caches pages from
tables/indexes in shared_buffers. You can make this very large if you wish.

Solaris ZFS ARC is the filesystem cache area for ZFS, it will yield to other
apps by default, but you will
get better performance and consistency if you limit it to not compete with
apps you know you want in memory, like a database.

In older versions, the postgres shared_buffers page cache was not very
efficient, and the OS page caches were jsut better, so setting
shared_buffers too large was a bad idea.
However, postgres uses a reasonable eviction algorithm now that doesn't
evict recently used items as readily as it used to, or let full table scans
kick out heavily accessed data (8.3 +).
The current tradeoff is that going from postgres to the OS cache incurs CPU
overhead for reading. But The OS may be better at caching more relevant
pages.
A good OS cache, like the ZFS ARC, is much more sophisticated in the
algorithms used for determining what to cache and what to evict, and so it
may be better at limiting disk usage. But accessing it versus the postgres
page cache in shared_buffers incurs extra CPU cost, as both caches must look
for, load, and potentially evict, rather than one.

>
>
> > >
> > > You are missing effective_cache_size. Try setting that to 32G.
> > That one was set to 24 GB. But this setting only tells posgres how much
> > caching it can expect from the OS? This is not actually memory that it
> > will allocate, is it?
>
> That is correct it is not an actual allocation but it does vastly effect
> your query plans. PostgreSQL uses this parameter to help determine if a
> someone is likely to be cached (see comment about file cache above).
>

It should be set to the expected size of the OS file cache (the size of the
ZFS ARC cache in this case).
However, it will have very little impact on large data queries that don't
use indexes. It has larger impact
for things that may do index scans when shared_buffers is small comapred to
file system cache + shared_buffers.

>
> > >
> > > You also didn't mention checkpoint_segments (which isn't memory but
> > > still important) and default_statistics_target (which isn't memory but
> > > still important).
> > >
> > is at the moment set to:
> >
> > checkpoint_segments = 40
> > default_statistics_target is set to default (I think that is 10)
> >
>
> 10 is likely way too low. Try 150 and make sure you analyze after.
>
> As I recall some other databases allow you to say, "you have this much
> memory, use it". PostgreSQL doesn't do that. You just give it pointers
> and it will use as much as it needs within the limits of the OS. The key
> word here is needs.
>
> There is obviously some variance to that (like work_mem).
>
> Joshua D. Drake
>
>
>
> --
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>