Re: Configuration WAS: New project launched : PostgreSQL

On Tue, 2006-01-31 at 12:11 -0800, Josh Berkus wrote:
> Jeffery,
>
> > > PostgreSQL *desperately* needs a better means of dealing with
> > > configuration (though I guess I shouldn't be pushing too hard for this
> > > since the current state of affairs brings me business). Any
> > > improvement in this area would be very welcome.
> > > http://pgfoundry.org/projects/configurator/ is something worth looking
> > > at.
> >
> > An ideal facility would be a program that analyzes the workload at
> > runtime and adjusts accordingly. That doesn't sound too hard, within
> > some unambitious boundary. If anyone would like to work on this, I'd be
> > happy to contribute.
>
> It seems pretty hard to *me*, compared with static configuration. If you
> have ideas for runtime analysis of configuration criteria, I'd be thrilled
> to hear them. From my perspective, most of them depend on backend
> monitoring that we don't have yet (like querying how full the FSM is).

I agree that some instrumentation of the backend might be needed. But
several of the performance-critical parameters seem tractable:

Effective cache size - should be easy to monitor the system for this
Shared buffers - easy to start from a rule-of-thumb and monitor usage
Work mem - trace the size and frequency of temp files
Wal buffers - trace the average or 80th percentile number of pages
generated by transactions
Commit delay - track the concurrency level and avg distance btw commits
Checkpoint segments - should be very easy to auto-adjust
Random page cost - should possible to determine this at runtime
Vacuum* - may be possible to determine vacuum impact on concurrent
queries