Thread: feature: dynamic DB cache resizing
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? Ed
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.