Re: Moving postgresql.conf tunables into 2003... - Mailing list pgsql-performance

Sean, Tom, Rod, Michael, Brian, Ron:

I'm going to paste everything into one monumental response.  So be prepared to
scroll.

Sean Asks:
> What are the odds of going through and revamping some of the tunables
> in postgresql.conf for the 7.4 release?

Poor.   The time to do this would have been 3 weeks ago, when I announced that
I was re-organizing them and that Bruce was changing many names.  We're past
Feature Freeze now, and we have a *lot* of bug-checking to do with the
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
100% of list e-mails archived, and I can tell you that there is little
agreement on many of the parameters ... plus I think we'd need about 15-25
e-mails about the best way to implement it, as my ideas are different from
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
comprehensive benchmarking package that allows us to difinitively test the
argued values for various parameters.  Right now, the "conventional wisdom"
we have is strictly anecdotal; for example, all of the discussions on this
list about the value of shared_buffers encompasses only about 14 servers and
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
<snip>

This sort of narrative belongs in the SGML docs, not in a CONF file.  In fact,
one could argue that we should take *all* commentary out of the CONF file in
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.

I spent a bunch of hours this last period re-organizing the official docs so
that they are easier to read.   Check them out in the 7.4 dev docs.   To
further enhance this, Shridhar and I will be putting together a broad
commentary and putting it on one of the postgresql web sites.  Eventually
when recommendations are tested a lot of this commentary will make its way
into the official docs.

Ron Says:
> the database. As I'm the DBA only on a part-time basis it is really time
> consuming to have to 1) find all relevant documentation and 2) learn it
> sufficiently to try to tune the db properly and 3) forget about most of
> it until we set up a new project in another year. I like postgresql and
> have convinced two of our clients to use it, but if I could fine tune it
> so it could 'fly', it would be easier for me (and others) to get more
> people to use it.

Database performance tuning will always be a "black art," as it necessitates a
broad knowledge of PostgreSQL, OS architecture, and computer hardware.  So I
doubt that we can post docs that would allow any 10% time DBA to make
PostgreSQL "fly", but hopefully over the next year we can make enough
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
*nixes.   We could raise this default once we can supply a script which will
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
> automatically, or with the help of some script.  It seem like there
> could be some perl script in contrib that could help figure this out.
> Possibly you are asked a bunch of questions and then the values are
> 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.

Justin.  Unfortunately, pg_autotune didn't get very far, plus its design is
not very friendly to collaborative programming.  So it's the right idea, but
needs to be reworked from the whiteboard, probably in Perl.
    Kevin Brown and I followed that up by trying to build a downloadable public
domain database that could be used for benchmarking.  However, he got an FT
job and I got distracted by prep for 7.4.  So, a little help?

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco