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

Hear, hear!
Well said Sean. I know that there has been disagreement about this in
the past (recommending settings, with some very good reasons), however
as a programmer/part-time DBA, something like this would be extremely
useful. Our company recently developed a web-based app for a client who
is very happy with their product (we used postgresql db) and we are just
beginning to revive a standalone app using postgresql instead of db2 as
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.

Ron St.Pierre

BTW I'm looking forward to Josh's configuration doc.

Sean Chittenden wrote:

>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
>
>
>