Example : let's imagine a "cache priority" setting.
Which we can presume the DBA will set incorrectly because the tools
needed to set that right aren't easy to use.
LOL, yes.
Jim threw out that you can just look at the page hit percentages
instead. That's not completely true. If you've had some nasty query
blow out your buffer cache, or if the server has been up a looong time
and the total stas don't really reflect recent reality, what's in the
buffer cache and what the stats say have been historical cached can
diverge.
Yes :
- perform huge query on table A
- table A is now in cache
- perform huge query on table B
- table B is now in cache, A isn't
- perform huge query on table A again
- postgres still thinks table A is cached and chooses a bad plan
This would not examine whatever is in the OS' cache, though.
Yeah, but now that shared_buffers can be set to a large part of physical
RAM, does it still matters ?
Point is, postgres knows what is in the shared_buffers, so it can make a
good decision. Postgres doesn't know what the OS has in cache, so it could
only make a wild guess. I would rather err on the side of safety...
I don't know that it's too unrealistic to model the OS as just being an
extrapolated bigger version of the buffer cache. I can think of a
couple of ways those can diverge:
1) Popular pages that get high usage counts can end up with a higher
representation in shared_buffers than the OS
2) If you've being doing something like a bulk update, you can have lots
of pages that have been written recently in the OS cache that aren't
really accounted for fully in shared_buffers, because they never get a
high enough usage count to stay there (only used once) but can fill the
OS cache as they're spooled up to write.
Especially on CHECKPOINT
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
