Ühel kenal päeval, T, 2007-03-06 kell 18:28, kirjutas Jeff Davis: > On Tue, 2007-03-06 at 18:29 +0000, Heikki Linnakangas wrote: > > Jeff Davis wrote: > > > On Mon, 2007-03-05 at 21:02 -0700, Jim Nasby wrote: > > >> On Mar 5, 2007, at 2:03 PM, Heikki Linnakangas wrote: > > >>> Another approach I proposed back in December is to not have a > > >>> variable like that at all, but scan the buffer cache for pages > > >>> belonging to the table you're scanning to initialize the scan. > > >>> Scanning all the BufferDescs is a fairly CPU and lock heavy > > >>> operation, but it might be ok given that we're talking about large > > >>> I/O bound sequential scans. It would require no DBA tuning and > > >>> would work more robustly in varying conditions. I'm not sure where > > >>> you would continue after scanning the in-cache pages. At the > > >>> highest in-cache block number, perhaps. > > >> If there was some way to do that, it'd be what I'd vote for. > > >> > > > > > > I still don't know how to make this take advantage of the OS buffer > > > cache.
Maybe it should not ? Mostly there can be use of OS cache only if it is much bigger than shared buffer cache. It may make sense to forget about OS cache and just tell those who can make use of sync scans to set most of memory aside for shared buffers. Then we can do better predictions/lookups of how much of a table is actually in memory. Dual caching is usually not very beneficial anyway, not to mention about difficulties in predicting any doual-caching effects. > > Yep, I don't see any way to do that. I think we could live with that, > > though. If we went with the sync_scan_offset approach, you'd have to > > leave a lot of safety margin in that as well. > > > > Right, there would certainly have to be a safety margin with > sync_scan_offset. However, your plan only works when the shared buffers > are dominated by this sequential scan. Let's say you have 40% of > physical memory for shared buffers, and say that 50% are being used for > hot pages in other parts of the database. That means you have access to > only 20% of physical memory to optimize for this sequential scan, and > 20% of the physical memory is basically unavailable (being used for > other parts of the database). The simplest thing in case table si much bigger than buffer cache usable for it is to start the second scan at the point the first scan is traversing *now*, and hope that the scans will stay together. Or start at some fixed lag, which makes the first scan to be always the one issuing reads and second just freerides on buffers already in cache. It may even be a good idea to throttle the second scan to stay N pages behind if the OS readahead gets confused when same file is read from multiple processes. If the table is smaller than the cache, then just scan it without syncing. Trying to read buffers in the same order starting from near the point where ppages are still in shared buffer cache seems good mostly for case where table is as big as or just a little larger than cache. > In my current implementation, you could set sync_scan_offset to 1.0 > (meaning 1.0 x shared_buffers), giving you 40% of physical memory that > would be used for starting this sequential scan. In this case, that > should be a good margin of error, considering that as much as 80% of the > physical memory might actually be in cache (OS or PG cache). > > This all needs to be backed up by testing, of course. I'm just > extrapolating some numbers that look vaguely reasonable to me. If there is an easy way to tell PG "give me this page only if it is in shared cache already", then a good approach might be to start 2nd scan at the point where 1st is now, and move in both directions simultabeously, like this: First scan is at page N. Second scan: M=N-1 WHILE NOT ALL PAGES ARE READ: IF PAGE N IS IN CACHE : -- FOLLOW FIRST READER READ PAGE N N++ ELSE IF M>=0 AND PAGE M IS IN CACHE : -- READ OLDER CACHED PAGES READ PAGE M M-- ELSE IF FIRST READER STILL GOING: -- NO OLDER PAGES, WAIT FOR 1st WAIT FOR PAGE N TO BECOME AVAILABLE READ PAGE N N++ ELSE: -- BECOME 1st reader READ PAGE N N++ PROCESS PAGE -- IF N > PAGES_IF_TABLE: N=0 IF M < 0: M=PAGES_IF_TABLE This should work reasonably well for LRU caches and it may be made to work with clock sweep scheme if the sweep arranges pages to purge in file order. If we could make the IF PAGE x IS IN CACHE part also know about OS cache this could also make use of os cache. Do any of you know about a way to READ PAGE ONLY IF IN CACHE in *nix systems ? -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings