X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 74589B439EA for ; Fri, 4 Jul 2003 02:39:26 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 95891-02 for ; Thu, 3 Jul 2003 23:39:15 -0300 (ADT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by svr1.postgresql.org (Postfix) with ESMTP id 83B4AB438DD for ; Thu, 3 Jul 2003 23:39:15 -0300 (ADT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3180471; Thu, 03 Jul 2003 19:39:27 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Sean Chittenden , pgsql-performance@postgresql.org Subject: Re: Moving postgresql.conf tunables into 2003... Date: Thu, 3 Jul 2003 19:38:15 -0700 User-Agent: KMail/1.4.3 References: <20030703190502.GR72567@perrin.int.nxad.com> In-Reply-To: <20030703190502.GR72567@perrin.int.nxad.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200307031938.15357.josh@agliodbs.com> X-Archive-Number: 200307/42 X-Sequence-Number: 2335 Sean, Tom, Rod, Michael, Brian, Ron: I'm going to paste everything into one monumental response. So be prepared= to=20 scroll. Sean Asks: > What are the odds of going through and revamping some of the tunables > in postgresql.conf for the 7.4 release?=20=20 Poor. The time to do this would have been 3 weeks ago, when I announced t= hat=20 I was re-organizing them and that Bruce was changing many names. We're pas= t=20 Feature Freeze now, and we have a *lot* of bug-checking to do with the=20 back-end changes. > I know Josh is working on revamping the postgresql.conf file, but > would it be possible to include suggested values for various bits of > hardware and then solicit contributions from admins on this list who > have tuned their DB correctly? Sure, but this is not a short-term project. I started this list, and have= =20 100% of list e-mails archived, and I can tell you that there is little=20 agreement on many of the parameters ... plus I think we'd need about 15-25= =20 e-mails about the best way to implement it, as my ideas are different from= =20 yours and Tom's are different from both of us. I'd also suggest that this is a good thing to do *after* we have created a= =20 comprehensive benchmarking package that allows us to difinitively test the= =20 argued values for various parameters. Right now, the "conventional wisdom"= =20 we have is strictly anecdotal; for example, all of the discussions on this= =20 list about the value of shared_buffers encompasses only about 14 servers an= d=20 3 operating systems. > # The default values for PostgreSQL are extremely conservative and are > # likely far from ideal for a site's needs. Included in this > # configuration, however, are _suggested_ values to help aid in This sort of narrative belongs in the SGML docs, not in a CONF file. In fa= ct,=20 one could argue that we should take *all* commentary out of the CONF file i= n=20 order to force people to read the docs. Michael Says: > I don't have much to add because I'm pretty new to Postgres and have been > soliciting advice here recently, but I totally agree with everything you > said. I don't mind if it's in the postgres.conf file or in a faq that is > easy to find, I just would like it to be in one place.=20 I spent a bunch of hours this last period re-organizing the official docs s= o=20 that they are easier to read. Check them out in the 7.4 dev docs. To=20 further enhance this, Shridhar and I will be putting together a broad=20 commentary and putting it on one of the postgresql web sites. Eventually= =20 when recommendations are tested a lot of this commentary will make its way= =20 into the official docs. Ron Says: > the database. As I'm the DBA only on a part-time basis it is really time= =20 > consuming to have to 1) find all relevant documentation and 2) learn it= =20 > sufficiently to try to tune the db properly and 3) forget about most of= =20 > it until we set up a new project in another year. I like postgresql and= =20 > have convinced two of our clients to use it, but if I could fine tune it= =20 > so it could 'fly', it would be easier for me (and others) to get more=20 > people to use it. Database performance tuning will always be a "black art," as it necessitate= s a=20 broad knowledge of PostgreSQL, OS architecture, and computer hardware. So = I=20 doubt that we can post docs that would allow any 10% time DBA to make=20 PostgreSQL "fly", but hopefully over the next year we can make enough=20 knowledge public to allow anyone to make PostgreSQL "sprint". Tom Comments: > I was arguing awhile back for bumping the default shared_buffers up, > but the discussion trailed off with no real resolution. I think we ran up against the still far-too-low SHMMAX settings in most=20 *nixes. We could raise this default once we can supply a script which wil= l=20 help the user bump up the OS's memory settings at, say, initDB time. Brian Suggests: > I'm curious how many of the configuration values can be determined=20 > automatically, or with the help of some script. It seem like there=20 > could be some perl script in contrib that could help figure this out.=20= =20 > Possibly you are asked a bunch of questions and then the values are=20 > computed based on that. Something like: This would be great! Wanna be in charge of it? Sean Replies: > Someone was working on a thing called pg_autotune or some such program > that'd do exactly what you're thinking of.=20=20 Justin. Unfortunately, pg_autotune didn't get very far, plus its design is= =20 not very friendly to collaborative programming. So it's the right idea, bu= t=20 needs to be reworked from the whiteboard, probably in Perl. Kevin Brown and I followed that up by trying to build a downloadable publi= c=20 domain database that could be used for benchmarking. However, he got an FT= =20 job and I got distracted by prep for 7.4. So, a little help? --=20 -Josh Berkus Aglio Database Solutions San Francisco