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

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.  A good example of
the need for this is when I was tuning "effective_cache" I thought that was
creating a cache for Postgres when in fact as it was pointed out to me, it's
just hinting to postgres the size of the OS cache.  Lots of ways for people
to get really confused here.

Also some people have said I should have used MySQL and to be honest I did
consider trying it out.  Because Postgres is hard to tune a lot of people
think it's slower than MySQL.  So anything that improves the quality of the
documentation and makes it easier to tune will improve Postgres' reputation
which will in turn encourage more people to use it!

Michael

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Sean
> Chittenden
> Sent: Thursday, July 03, 2003 9:05 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Moving postgresql.conf tunables into 2003...
>
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>