Thread: 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! Learn more!
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! Learn more!
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)