DB cache size strategies

[Ed L.]

Here's some of my current notions on pgsql performance tuning strictly as it
relates to pgsql tuning parameters in the context of a dedicated linux or
hpux server. I'm particularly focusing on the shared_buffers setting. I
invite any corrective or confirming feedback. I realize there are many
other hugely important performance factors outside this scope.

One key aspect of pgsql performance tuning is to adjust the memory
consumption settings (shared_buffers , sort_mem, etc) large enough to hold
as much of the database in shared memory as possible while not causing page
swap-ins. I understand that both page swap-outs and swap space usage is
normal and OK, but lots of page swap-ins are bad). In other words, for
absolute fastest performance, we want a database buffer cache hit rate of
as close to 100% as possible.

Now, how do we accurately measure this cache hit rate at any one point?
Well, here's what I currently know as the best measure for a given cluster
of databases:

SELECT SUM(pg_stat_get _db_blocks_hit( d.oid))
/ SUM(pg_stat_get _db_blocks_fetc hed(d.oid)) AS hit_rate
FROM pg_database d;

How do we identify when we have sub-optimal pgsql configuration parameters
in this regard? My understanding: to whatever extent the hit rate is
below 100%, our shared buffers are not large enough to hold the data we're
accessing.

How do we fix the problem? We want to increase the shared_buffers setting
and/or maybe some combination sort_mem, wal_buffers, vacuum_mem, reset our
stats, and resume monitoring. I assume which of these we increase for
maximum benefit depends on the sources of the cache overflow (routine
queries, sorting, vacuuming), but that shared_buffers is the most impactful
in practice. Again, we want to do this without causing page swap-ins.
When you see swap-ins, you've gone too far. If we experience swap-ins and
less than 100% cache hit rate by any significant amount, then we need more
RAM or less RAM consumption.

The end result is that page swap-ins (sar -W) and cache hit rate (query
above) are two very key measures to use as warning bells.

Glad to hear your thoughts.

Ed
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Tom Lane]

"Ed L." <pg***@bluepolk a.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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

"Ed L." <pg***@bluepolk a.net> writes:

Then what scenarios, if any, merit theory (2) over theory (1)?

I'd only consider a large-cache setting on a machine that's dedicated to
running the database (where "dedicated" means "that's the only thing you
care about performance of", as in your first scenario). Even then I'd
test it against the other way. As Andrew Sullivan notes nearby, our
experience has been that the PostgreSQL buffer manager isn't all that
efficient about managing large caches. It's possible that Jan's current
work will change that situation in 7.5, but I'd still test first ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Ed L.]

On Wednesday February 11 2004 9:18, Tom Lane wrote:

"Ed L." <pg***@bluepolk a.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)?


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Ed L.]

On Wednesday February 11 2004 9:57, Tom Lane wrote:

"Ed L." <pg***@bluepolk a.net> writes:

Then what scenarios, if any, merit theory (2) over theory (1)?

I'd only consider a large-cache setting on a machine that's dedicated to
running the database (where "dedicated" means "that's the only thing you
care about performance of", as in your first scenario). Even then I'd
test it against the other way. As Andrew Sullivan notes nearby, our
experience has been that the PostgreSQL buffer manager isn't all that
efficient about managing large caches. It's possible that Jan's current
work will change that situation in 7.5, but I'd still test first ...

Great. Thx to all for feedback, very informative, interesting, and helpful
in practice.

Ed

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martijn van Oosterhout]

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 <kl*****@svana. 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAKo4FY5T wig3Ge+YRAvkMAK C0mNBg4P6Tr3Rp6 anJuaYal9qkNwCd HK6A
bdzipH++nDu2VnF CLAhhP5Y=
=0vKr
-----END PGP SIGNATURE-----

[Rick Gigger]

Has anyone discussed adding to postgres the ability to figure this out
on it's own. Couldn't it gather some statistics about the kind of
resources that it is actually using and adjust accordingly. You could
give it a max amount to use for the shared buffers but if it was so high
that it degraded performance postgres could just cut back on what it
actually used.

Is this even feasile? Correct me if I am wrong but it seems that most
other dbs seem to work this way.

It would make installing a nice tuned postgres a much more turn key
operation.

rick
Ed L. wrote:

On Wednesday February 11 2004 9:57, Tom Lane wrote:

"Ed L." <pg***@bluepolk a.net> writes:

Then what scenarios, if any, merit theory (2) over theory (1)?

I'd only consider a large-cache setting on a machine that's dedicated to
running the database (where "dedicated" means "that's the only thing you
care about performance of", as in your first scenario). Even then I'd
test it against the other way. As Andrew Sullivan notes nearby, our
experience has been that the PostgreSQL buffer manager isn't all that
efficient about managing large caches. It's possible that Jan's current
work will change that situation in 7.5, but I'd still test first ...

Great. Thx to all for feedback, very informative, interesting, and helpful
in practice.

Ed

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

On Wednesday 11 February 2004 21:40, Rick Gigger wrote:

Has anyone discussed adding to postgres the ability to figure this out
on it's own. Couldn't it gather some statistics about the kind of
resources that it is actually using and adjust accordingly. You could
give it a max amount to use for the shared buffers but if it was so high
that it degraded performance postgres could just cut back on what it
actually used.

Is this even feasile? Correct me if I am wrong but it seems that most
other dbs seem to work this way.

It would make installing a nice tuned postgres a much more turn key
operation.

What if making the DB run faster makes everything else run slower? How does it
know whether 0.1sec i/o time was just it, or if there was another process
contending for disk access?

Then again, maybe interactive speed isn't important, but your bulk update is.
Or, perhaps your report can wait, but a realtime response is vital.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match