Re: DB cache size strategies

On Wednesday February 11 2004 9:18, Tom Lane wrote:
> "Ed L." <pgsql@bluepolka.net> writes:
> > In general, would it be true to say that if one does *not* anticipate
> > contention for kernel disk cache space from non-DB processes (e.g., the
> > dedicated db server), then you probably want to use theory (1)?  If one
> > *does* anticipate such contention (e.g. the all-on-one-box web-db app),
> > then you probably want to use theory (2) in order to ensure an adequate
> > cache?
>
> If the box is doing other things then I think you definitely want to
> keep shared_buffers pretty small, relying on the kernel to allocate the
> available resources as best it can.

Then what scenarios, if any, merit theory (2) over theory (1)?  I can think
of two where I'd expect that to be the case.

First, suppose DB processes are more important (and thus more deserving of
cache) than other processes in an all-on-one-box case.  For example, the
non-DB processes consisted strictly of various non-performance-critical
programs to analyze large log files, etc.  Would it then make sense to use
theory (2) to ensure those non-critical programs do not inadvertently
increase I/O for the DB processes?

Second, suppose we have 2 clusters running on a dedicated DB server, each
with a large enough dataset to cause the other's data to be completely
crowded out of the kernel cache during backups under theory (1), causing a
lot of disk I/O for the other as the other gradually reloads.  If we use
theory (2), allocating roughly half of available RAM to each DB cache
(minus breathing room for admin, OS), I would expect that over time, the
entire DB dataset for each cluster would work its way into each cluster's
DB cache and be largely immune to the activities of the other cluster.
We'd consider that a good thing.  Would this be an appropriate scenario for
theory (2)?