pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (65+ messages)  latest in thread

Message-ID: <200302110948.39283.josh@agliodbs.com>
Permalink:  ../200302110948.39283.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200302110948.39283.josh@agliodbs.com

 · 

reply

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