Re: DB cache size strategies

On Wed, Feb 11, 2004 at 09:47:35AM -0700, Ed L. wrote:
> 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?

Are you assuming that shared memory will not be swapped out? If you allocate
lots of the shared memory to the cluster that it's not going to used anyway
most of the time, you're just going to find it's being swapped.

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

I'd expect the kernel the swap out enough of the shared memory in each
cluster to match the actual working set. Although, I imagine PostgreSQL's
buffer management may affect the way it's gets swapped. I have no idea
whether PostgreSQL's buffer management code does a lot of page touching.

Note, some OS's don't swap out shared memory, so the above doesn't apply to
them.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce