Question about PG cache selection strategy
Hi,
I'm running a very large and frequently updated database on a machine with relatively limited memory (you can safely assume that the database disk usage to available memory has a ratio of 10:1 - so clearly not all the pages can be retained in memory).
The naive approach would presume that 9 out of 10 requests would hit the disk, in reality that is not true and yet I have observed some oddities. Clearly the caching algorithm favors caching the indices to data (since they are more frequently accessed) but there is another case where *recently written* entries are often requested shortly after and I am not sure that they get prioritized correctly by the cache. My question is whether the cache algorithm caches only READ pages or also WRITTEN pages?
If it does, is there to set some prioritization ratio for these per table? It would enable to tune the caching for everyone specific needs (if needed).
Thanks
Sefer.
_________________________________________________________________
All-in-one security and maintenance for your PC. Get a free 90-day trial!
http://www.windowsonecare.com/purchase/trial.aspx?sc_cid=wl_wlmail
On Nov 23, 2006, at 8:37 AM, Sefer Tov wrote:
oddities. Clearly the caching algorithm favors caching the indices
to data (since they are more frequently accessed) but there is
another case where *recently written* entries are often requested
shortly after and I am not sure that they get prioritized correctly
by the cache. My question is whether the cache algorithm caches
only READ pages or also WRITTEN pages?
If it does, is there to set some prioritization ratio for these per
table? It would enable to tune the caching for everyone specific
needs (if needed).
Any writes to a page happen via shared_buffers, so yes, writes are
cached. Once a page is dirtied, it will stay that way until it's
written out by the background writer, or a checkpoint writes it out.
I believe that the buffer management algorithms can decide that page
should be removed from the buffer, which would also write it out.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)