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

On Thu, 3 Jul 2003, 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'm the guy who advocates settings of 1 to 2, and that still sounds low to
me. :-)  I'm wondering if the effective_cache_size was set properly, as
well as there be enough buffers allocated.

I generally set effective cache size to 100,000 pages (800 megs or so) on
my box, which is where it sits most days.  with this setting I've found
that settings of under 1 are not usually necessary to force the planner to
take the path of righteousness (i.e. the fastest one :-) 1.2 to 1.4 are
optimal to me.

Since theoretically a random page of of 1 means no penalty to move the
heads around, and there's ALWAYS a penalty for moving the heads around, we
have to assume:

1: That either the planner is making poor decisions on some
other variable, and we can whack the planner in the head with a really low
random page count.

OR

2: The other settings are suboptimal (buffers, sort_mem,
effective_cache_size, etc...) and lowering random page costs helps there.

I've always wondered if most performance issues aren't a bit of both.

The answer, of course, is fixing the planner so that a random_page_cost of
anything less than 1 would never be needed, since by design, anything
under 1 represents a computer that likely doesn't exist (in theory of
course.)  A 1 would be a machine that was using solid state hard drives
and had the same cost in terms of OS paths to do random accesses as
sequential.

What constants in the planner, and / or formulas would be the likely
culprits I wonder?  I've wandered through that page and wasn't sure what
to play with.

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