Re: Performance and Generic Config after install

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