feature: dynamic DB cache resizing

Ed L. wrote:

We have ~75 pgsql clusters running in environments where downtime
comes at a premium cost. We often run multiple clusters on a
single box, and find it necessary to adjust the size of the
static DB cache as we add or move clusters. Unfortunately, that
means some downtime. It would be extremely useful in minimizing
downtime to be able to dynamically resize the DB cache without
having to restart the cluster. How hard would that be?

We can't think of how to do because it is all shared memory shared by
ever forked process --- does that help explain its complexity?
;-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Ed L. wrote:

We have ~75 pgsql clusters running in environments where downtime
comes at a premium cost. We often run multiple clusters on a
single box, and find it necessary to adjust the size of the
static DB cache as we add or move clusters. Unfortunately, that
means some downtime. It would be extremely useful in minimizing
downtime to be able to dynamically resize the DB cache without
having to restart the cluster. How hard would that be?

We can't think of how to do because it is all shared memory shared by
ever forked process --- does that help explain its complexity?

There isn't any particularly good reason to be resizing shared_buffers
on the fly anyway; much easier to let the kernel adapt the size of its
disk cache instead. Best practice for shared_buffers is to set it
somewhere in the range of 10K to 50K and forget it.

If Ed was thinking of something other than shared_buffers, he should
explain what.

regards, tom lane

On Monday December 5 2005 3:17 pm, Tom Lane wrote:

There isn't any particularly good reason to be resizing
shared_buffers on the fly anyway; much easier to let the
kernel adapt the size of its disk cache instead.  Best
practice for shared_buffers is to set it somewhere in the
range of 10K to 50K and forget it.

Oh, how I wish it were so on these boxes. However, HP gurus tell
me that OS dynamic buffer caches larger than ~800MB +/- slop
have diminishing returns due to contention between vhand and
others. Therefore, to most effectively take advantage of a big
multi-cluster box with gobs of RAM for DB caching, it seems to
me I need to specifically allocate the available RAM among the
DB clusters. [This is a pain and I'd much rather the OS did it
for me.] Of course, we don't know how many clusters we'll have
and of what size when we start. Thus, the need for resizing the
DB caches as new clusters come online. Does that make sense?

Ed

On Mon, 2005-12-05 at 16:30, Ed L. wrote:

On Monday December 5 2005 3:17 pm, Tom Lane wrote:

There isn't any particularly good reason to be resizing
shared_buffers on the fly anyway; much easier to let the
kernel adapt the size of its disk cache instead. Best
practice for shared_buffers is to set it somewhere in the
range of 10K to 50K and forget it.

Oh, how I wish it were so on these boxes. However, HP gurus tell
me that OS dynamic buffer caches larger than ~800MB +/- slop
have diminishing returns due to contention between vhand and
others. Therefore, to most effectively take advantage of a big
multi-cluster box with gobs of RAM for DB caching, it seems to
me I need to specifically allocate the available RAM among the
DB clusters. [This is a pain and I'd much rather the OS did it
for me.] Of course, we don't know how many clusters we'll have
and of what size when we start. Thus, the need for resizing the
DB caches as new clusters come online. Does that make sense?

What OS are you running? In the case of Linux, you're usually better
off letting the kernel handle the caching, although there are times when
giving a fair bit of it to postgresql can help.

Have you actually benchmarked your setup with most memory used as kernel
cache versus most allocated amongst, say, 10 to 20 backends to get a
feel for whether the HP gurus really were right?

Never take anyone's word as the truth until you've tested it for
yourself and proven it one way or another.