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

What are the odds of going through and revamping some of the tunables
in postgresql.conf for the 7.4 release?  I was just working with
someone on IRC and on their 7800 RPM IDE drives, their
random_page_cost was ideally suited to be 0.32: a far cry from 4.
Doing so has been a win across the board and the problem query went
from about 40sec (seq scan) down to 0.25ms (using idx, higher than
0.32 resulted in a query time jump to 2sec, and at 0.4 it went back up
to a full seq scan at 40sec).

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?

## random_page_cost -- units are one sequential page fetch cost
#random_page_cost = 4           # default - very conservative
#random_page_cost = 0.9         # IDE 5200 RPM, 8MB disk cache
#random_page_cost = 0.3         # IDE 7800 RPM, 4MB disk cache
#random_page_cost = 0.1         # SCSI RAID 5, 10,000RPM, 64MB cache
#random_page_cost = 0.05        # SCSI RAID 1+0, 15,000RPM, 128MB cache
#...

## next_hardware_dependent_tunable....
#hardware_dependent_tunable

I know these tables could get somewhat lengthy or organized
differently, but given the file is read _once_ at _startup_, seen by
thousands of DBAs, is visited at least once for every installation (at
the least to turn on TCP connections), is often the only file other
than pg_hba.conf that gets modified or looked at, this could be a very
nice way of introducing DBAs to tuning PostgreSQL and reducing the
number of people crying "PostgreSQL's slow."  Having postgresql.conf a
clearing house for tunable values for various bits of hardware would
be a huge win for the community and would hopefully radically change
this database's perception.  At the top of the file, it would be
useful to include a blurb to the effect of:

# 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
# tuning.  The values below are not authoritative, merely contributed
# suggestions from PostgreSQL DBAs and committers who have
# successfully tuned their databases.  Please take these values as
# advisory only and remember that they will very likely have to be
# adjusted according to your site's specific needs.  If you have a
# piece of hardware that isn't mentioned below and have tuned your
# configuration aptly and have found a suggested value that the
# PostgreSQL community would benefit from, please send a description
# of the hardware, the name of the tunable, and the tuned value to
# performance@PostgreSQL.org to be considered for inclusion in future
# releases.
#
# It should also go without saying that the PostgreSQL Global
# Development Group and its community of committers, contributors,
# administrators, and commercial supporters are absolved from any
# responsibility or liability with regards to the use of its software
# (see this software's license for details).  Any data loss,
# corruption, or performance degradation is the responsibility of the
# individual or group of individuals using/managing this installation.
#
# Hints to DBAs:
#
# *) Setup a regular backup schedule (hint: pg_dump(1)/pg_dumpall(1) +
#    cron(8))
#
# *) Tuning: Use psql(1) to test out values before changing values for
#    the entire database.  In psql(1), type:
#
#    1) SHOW [tunabe_name];
#    2) SET [tunable_name] = [value];
#    3) [run query]
#    4) [repeat adjustments as necessary before setting a value here in
#       the postgresql.conf].
#    5) [Send a SIGHUP signal to the backend to have the config values
#       re-read]
#
# *) Never use kill -9 on the backend to shut it down.
#
# *) VACUUM ANALYZE your databases regularly.
#
# *) Use EXPLAIN ANALYZE [query] to tune queries.
#
# *) Read the online documentation at:
#    http://www.postgresql.org/docs/
#
#     -- PostgreSQL Global Development Group

Just a thought.  A bit lengthy, but given that out of the box most
every value is set to be extremely conservative (detrimentally so, esp
since the majority of users aren't running PostgreSQL in embedded
devices, are on reasonably new hardware > 3 years old), and the config
is only read in once and generally the only file viewed by DBAs, it'd
make PostgreSQL more competitive in the performance dept if there were
some kind of suggested values for various tunables.  Having someone
whine, "my PostgreSQL database is slow" is really getting old when its
really not and it's a lack of tuning that is at fault, lowering the
bar to a successful and speedy PostgreSQL installation would be a win
for everyone.  The person who I was helping also had the same data,
schema, and query running on MySQL and the fastest it could go was
2.7s (about 40M rows in the table).

<gets_off_of_soap_box_to_watch_and_listen/> -sc

--
Sean Chittenden