On Wed, 6 Jun 2007, Tom Lane wrote:
If we don't know how to tune them, how will the users know?
I can tell you a good starting set for them to on a Linux system, but you first have to let me know how much memory is in the OS buffer cache, the typical I/O rate the disks can support, how many buffers are expected to be written out by BGW/other backends at heaviest load, and the current setting for /proc/sys/vm/dirty_background_ratio. It's not a coincidence that there are patches applied to 8.3 or in the queue to measure all of the Postgres internals involved in that computation; I've been picking away at the edges of this problem.
Getting this sort of tuning right takes that level of information about the underlying system. If there's a way to internally auto-tune the values this patch operates on (which I haven't found despite months of trying), it would be in the form of some sort of measurement/feedback loop based on how fast data is being written out. There really are way too many things involved to try and tune it based on anything else; the underlying OS/hardware mechanisms that determine how this will go are complicated enough that it might as well be a black box for most people.
One of the things I've been fiddling with the design of is a testing program that simulates database activity at checkpoint time under load. I think running some tests like that is the most straightforward way to generate useful values for these tunables; it's much harder to try and determine them from within the backends because there's so much going on to keep track of.
I view the LDC mechanism as being in the same state right now as the background writer: there are a lot of complicated knobs to tweak, they all do *something* useful for someone, and eliminating them will require a data-collection process across a much wider sample of data than can be collected quickly. If I had to make a guess how this will end up, I'd expect there to be more knobs in LDC than everyone would like for the 8.3 release, along with fairly verbose logging of what is happening at checkpoint time (that's why I've been nudging development in that area, along with making logs easier to aggregate). Collect up enough of that information, then you're in a position to talk about useful automatic tuning--right around the 8.4 timeframe I suspect.
-- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
