From: Josh Berkus <josh@agliodbs.com>
To: Justin Clift <justin@postgresql.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: PostgresSQL Hackers Mailing List <pgsql-hackers@postgresql.org>
Cc: pgsql-advocacy@postgresql.org
Subject: Re: Changing the default configuration (was Re:
Date: Tue, 11 Feb 2003 09:48:39 -0800
Message-ID: <200302110948.39283.josh@agliodbs.com> (raw)
In-Reply-To: <3E493415.9090004@postgresql.org>
References: <303E00EBDD07B943924382E153890E5434A909@cuthbert.rcsinc.local>
<26582.1044984365@sss.pgh.pa.us>
<3E493415.9090004@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, justin@postgresql.org, tgl@sss.pgh.pa.us, merlin.moncure@rcsonline.com, pgsql-hackers@postgresql.org, pgsql-advocacy@postgresql.org
Subject: Re: Changing the default configuration (was Re:
In-Reply-To: <200302110948.39283.josh@agliodbs.com>
References: <303E00EBDD07B943924382E153890E5434A909@cuthbert.rcsinc.local> <26582.1044984365@sss.pgh.pa.us> <3E493415.9090004@postgresql.org> <200302110948.39283.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox