Re: Changing the default configuration (was Re:

Tom, Justin,

> > Uh ... do we have a basis for recommending any particular sets of
> > parameters for these different scenarios? This could be a good idea
> > in the abstract, but I'm not sure I know enough to fill in the details.

Sure.
Mostly-Read database, few users, good hardware, complex queries:
= High shared buffers and sort mem, high geqo and join collapse thresholds,
moderate fsm settings, defaults for WAL.
Same as above with many users and simple queries (webserver) =
same as above, except lower sort mem and higher connection limit
High-Transaction Database =
Moderate shared buffers and sort mem, high FSM settings, increase WAL files
and buffers.
Workstation =
Moderate to low shared buffers and sort mem, moderate FSM, defaults for WAL,
etc.
Low-Impact server = current defaults, more or less.

While none of these settings will be *perfect* for anyone, they will be
considerably better than what's shipping with postgresql. And, based on my
"Learning Perl" knowledge, I'm pretty sure I could write the program.

All we'd need to do is argue out, on the PERFORMANCE list, what's a good value
for each profile. That's the tough part. The Perl script is easy.

> > A lower-tech way to accomplish the same result is to document these
> > alternatives in postgresql.conf comments and encourage people to review
> > that file, as Steve Crawford just suggested. But first we need the raw
> > knowledge.

That's also not a bad approach ... the CONF file should be more heavily
commented, period, regardless of what approach we take. I volunteer to work
on this with other participants.

> Without too much hacking around, you could pretty easily adapt the
> pg_autotune code to do proper profiles of a system with different settings.

No offense, Justin, but I don't know anyone else who's gotten your pg_autotune
script to run other than you. And pg_bench has not been useful performance
measure for any real database server I have worked on so far.

I'd be glad to help improve pg_autotune, with two caveats:
1) We will still need to figure out the "profiles" above so that we have
decent starting values.
2) I suggest that we do pg_autotune in Perl or Python or another higher-level
language. This would enable several performance buffs who don't do C to
contribute to it, and a performance-tuning script is a higher-level-language
sort of function, anyway.

--
Josh Berkus
Aglio Database Solutions
San Francisco