Bug: Buffer cache is not scan resistant
in [PgSql]
|
Prev: xlogViewer / xlogdump
Next: CVS corruption/mistagging?
From: Tom Lane on 5 Mar 2007 15:18 "Simon Riggs" <simon(a)2ndquadrant.com> writes: > Best way is to prove it though. Seems like not too much work to have a > private ring data structure when the hint is enabled. The extra > bookeeping is easily going to be outweighed by the reduction in mem->L2 > cache fetches. I'll do it tomorrow, if no other volunteers. [ shrug... ] No one has yet proven to my satisfaction that L2 cache has anything to do with this. The notion that you can read a new disk page into a shared buffer and have that buffer still be live in the processor cache is so obviously bogus that I think there must be some other effect at work. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
From: Jeff Davis on 5 Mar 2007 15:19 On Mon, 2007-03-05 at 09:09 +0000, Heikki Linnakangas wrote: > In fact, the pages that are left in the cache after the seqscan finishes > would be useful for the next seqscan of the same table if we were smart > enough to read those pages first. That'd make a big difference for > seqscanning a table that's say 1.5x your RAM size. Hmm, I wonder if > Jeff's sync seqscan patch adresses that. > Absolutely. I've got a parameter in my patch "sync_scan_offset" that starts a seq scan N pages before the position of the last seq scan running on that table (or a current seq scan if there's still a scan going). If the last scan is not still in progress, the pages are less likely to be in cache. If the pages are in cache, great; if not, it doesn't matter where we start anyway. If the last scan is still in progress, those recently-read pages are very likely to be in cache (shared buffers or OS buffer cache). Regards, Jeff Davis ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
From: Tom Lane on 5 Mar 2007 15:30 Jeff Davis <pgsql(a)j-davis.com> writes: > Absolutely. I've got a parameter in my patch "sync_scan_offset" that > starts a seq scan N pages before the position of the last seq scan > running on that table (or a current seq scan if there's still a scan > going). Strikes me that expressing that parameter as a percentage of shared_buffers might make it less in need of manual tuning ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo(a)postgresql.org so that your message can get through to the mailing list cleanly
From: Jeff Davis on 5 Mar 2007 15:45 On Mon, 2007-03-05 at 15:30 -0500, Tom Lane wrote: > Jeff Davis <pgsql(a)j-davis.com> writes: > > Absolutely. I've got a parameter in my patch "sync_scan_offset" that > > starts a seq scan N pages before the position of the last seq scan > > running on that table (or a current seq scan if there's still a scan > > going). > > Strikes me that expressing that parameter as a percentage of > shared_buffers might make it less in need of manual tuning ... > The original patch was a percentage of effective_cache_size, because in theory it may be helpful to have this parameter larger than shared buffers. Synchronized Scannning can take advantage of OS buffer cache as well. Someone convinced me to change it to be an independent variable. I don't have a strong opinion, but now I have three different opinions: (1) Independent parameter (2) Percentage of shared_buffers (3) Percentage of effective_cache_size Regards, Jeff Davis ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
From: Tom Lane on 5 Mar 2007 16:03
Jeff Davis <pgsql(a)j-davis.com> writes: > On Mon, 2007-03-05 at 15:30 -0500, Tom Lane wrote: >> Strikes me that expressing that parameter as a percentage of >> shared_buffers might make it less in need of manual tuning ... > The original patch was a percentage of effective_cache_size, because in > theory it may be helpful to have this parameter larger than shared > buffers. Synchronized Scannning can take advantage of OS buffer cache as > well. I didn't say you couldn't allow it to be more than 100% ;-). But basing it on effective_cache_size strikes me as a bad idea because that parameter is seldom better than a wild guess. shared_buffers at least means something. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq |