X-Original-To: pgsql-advocacy@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8840C475B8A; Tue, 11 Feb 2003 12:49:36 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 75D74475AAC; Tue, 11 Feb 2003 12:49:35 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2834383; Tue, 11 Feb 2003 09:49:46 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Justin Clift , Tom Lane Subject: Re: Changing the default configuration (was Re: Date: Tue, 11 Feb 2003 09:48:39 -0800 User-Agent: KMail/1.4.3 Cc: Merlin Moncure , PostgresSQL Hackers Mailing List , pgsql-advocacy@postgresql.org References: <303E00EBDD07B943924382E153890E5434A909@cuthbert.rcsinc.local> <26582.1044984365@sss.pgh.pa.us> <3E493415.9090004@postgresql.org> In-Reply-To: <3E493415.9090004@postgresql.org> MIME-Version: 1.0 Message-Id: <200302110948.39283.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/22 X-Sequence-Number: 780 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.=20=20 Mostly-Read database, few users, good hardware, complex queries: =3D High shared buffers and sort mem, high geqo and join collapse threshol= ds, moderate fsm settings, defaults for WAL. Same as above with many users and simple queries (webserver) =3D same as above, except lower sort mem and higher connection limit High-Transaction Database =3D Moderate shared buffers and sort mem, high FSM settings, increase WAL file= s=20 and buffers. Workstation =3D Moderate to low shared buffers and sort mem, moderate FSM, defaults for WA= L,=20 etc. Low-Impact server =3D current defaults, more or less. While none of these settings will be *perfect* for anyone, they will be=20 considerably better than what's shipping with postgresql. And, based on m= y=20 "Learning Perl" knowledge, I'm pretty sure I could write the program.=20=20 All we'd need to do is argue out, on the PERFORMANCE list, what's a good va= lue=20 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=20 commented, period, regardless of what approach we take. I volunteer to wor= k=20 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 setting= s. No offense, Justin, but I don't know anyone else who's gotten your pg_autot= une=20 script to run other than you. And pg_bench has not been useful performance= =20 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=20 decent starting values. 2) I suggest that we do pg_autotune in Perl or Python or another higher-lev= el=20 language. This would enable several performance buffs who don't do C to= =20 contribute to it, and a performance-tuning script is a higher-level-languag= e=20 sort of function, anyway. --=20 Josh Berkus Aglio Database Solutions San Francisco