Re: Performance and Generic Config after install

Patches welcome. :)

BTW, -docs or -www might be a better place to discuss this.

On Mon, Oct 02, 2006 at 05:11:20PM -0400, Brandon Aiken wrote:
> I think the problem would be partly mitigated be better or more obvious
> documentation that makes it clear that a) PostgreSQL is probably not
> configured optimally, and b) where exactly to go to get server
> optimization information.  Even basic docs on postgresql.conf seem
> lacking.  The fact that something like these exist:
>
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
>
> http://www.varlena.com/GeneralBits/Tidbits/perf.html#conf
>
> Should be a giant red flag that documentation is a tad sparse for the
> config file.  Those docs would take hours of work, they're the only
> thing I've really found, *and they're still 3 years out of date*.
>
> It took me a lot of digging to find the docs on optimizing PostgreSQL
> from postgresql.org.  It's in the documentation area, but it's not
> documented in the manual at all that I could find (which is highly
> counter-intuitive).  Instead, it's listed under 'Technical
> Documentation' (making the manual casual documentation?  I thought all
> Postgre docs were technical.) then under 'Community Guides and Docs',
> and finally under the subheading Optimizing (note that the above links
> are listed here):
>
> http://www.postgresql.org/docs/techdocs.2
>
> Either the server installer or the (preferably) the manual needs to make
> it very clear about this documentation.  If nobody can find it nobody
> will use it, and it's very well hidden at the moment.
>
> The manual gets updated with every release, but more and more I realize
> that the manual isn't comprehensive.  The manual explains the SQL syntax
> and how PostgreSQL interprets the relational model, but it has very
> little information on how to really *use* PostgreSQL as a server.  The
> manual is all app dev and no sysadmin.  For example, *what* compile time
> options are available?  I know they exist, but I've never seen them
> listed.
>
> For another example, take a look at this so-called detailed guide to
> installing PG on Fedora, which is linked from the 'Technical
> Documentation' area of postgresql.org:
>
> http://dancameron.org/pages/how-to-install-and-setup-postgresql-for-fedo
> ralinux/
>
> Now, really, this 'guide' is little more than what yum command to run
> and which config lines to edit to limit remote TCP connections.
>
> Now take a look at the first comment:
> "Thanks for the advice. For an Oracle DBA this really helped me in
> comming up to speed on Postgres administration."
>
> There should be an Administration Guide companion to the Manual.
>
> --
> Brandon Aiken
> CS/IT Systems Engineer
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Davis
> Sent: Monday, October 02, 2006 2:58 PM
> To: Oisin Glynn
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Performance and Generic Config after install
>
> On Mon, 2006-10-02 at 14:40 -0400, Oisin Glynn wrote:
> > As an aside to the "[GENERAL] Advantages of PostgreSQL" thread going
> on
> > today, I have wondered why the initial on install config of PostgreSQL
>
> > is (according to most posts) very conservative.  I can see how this
> > would be a plus when people may be getting PostgreSQL as part of an OS
>
> > in the Linux world who may never/rarely use it.
> >
> > I know that in reality tuning and sizing all of the parameters is a
> very
> > database specific thing, but it would seem that if some default
> choices
> > would be available it would give those testing/evaluation and trying
> to
> > get started a shot at quicker progress. Obviously they would still
> need
> > to tune to your own application.
> >
> > Some dreadfully named, possibly pointless options?
> >
> > Unoptimized / low performance -  Low load on Server   (The current out
>
> > of the box)
>
> Keep in mind that PostgreSQL doesn't really restrict itself as a whole.
> If you set the settings too low, and throw costly queries at it, the
> load on the server will be very high. We don't want to imply that
> PostgreSQL's settings restrict it's cpu, memory, or disk usage as a
> whole.
>
> > Production Non Dedicated   - PostgreSQL is one of the apps sharing
> > server but is important.
> > Production Dedicated Server   - The only purpose of this box is to run
>
> > PostgreSQL
> >
>
> In my opinion, this is more the job of distributions packaging it.
> Distributions have these advantages when they are choosing the settings:
>
> (1) They have more information about the target computer
> (2) They have more information about the intended use of the system as a
> whole
> (3) They have more ability to ask questions of the user
>
> PostgreSQL itself can't easily do those things in a portable way. If
> someone is compiling from source, it is more reasonable to expect them
> to know what settings to use.
>
> However, that said, I think that distributions certainly do take a cue
> from the default settings in the source distribution. That's why lately
> the default settings have been growing more aggressive with each
> release.
>
> Regards,
>     Jeff Davis
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)