From: Sean Chittenden <sean@chittenden.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Moving postgresql.conf tunables into 2003...
Date: Thu, 3 Jul 2003 17:06:46 -0700
Message-ID: <20030704000646.GV72567@perrin.int.nxad.com> (raw)
In-Reply-To: <2422.1057264735@sss.pgh.pa.us>
References: <20030703190502.GR72567@perrin.int.nxad.com>
<2422.1057264735@sss.pgh.pa.us>
> > What are the odds of going through and revamping some of the tunables
> > in postgresql.conf for the 7.4 release?
>
> I was arguing awhile back for bumping the default shared_buffers up,
> but the discussion trailed off with no real resolution.
>
> > 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.
>
> It is not physically sensible for random_page_cost to be less than
> one. The system only lets you set it there for experimental
> purposes; there is no way that postgresql.conf.sample will recommend
> it. If you needed to push it below one to force indexscans, there
> is some other problem that needs to be solved. (I'd wonder about
> index correlation myself; we know that that equation is pretty
> bogus.)
Could be. I had him create a multi-column index on the date and a
non-unique highly redundant id. This is a production machine so the
load times are heavier now than they were earlier. The stats sample
was increased to 1000 too to see if that made any difference in the
planners estimations.
mss_masterlog=> SHOW random_page_cost;
random_page_cost
------------------
4
(1 row)
mss_masterlog=> EXPLAIN ANALYZE SELECT srca, COUNT(srca) FROM mss_fwevent WHERE
mss_masterlog-> sensorid = 7 AND evtime > (now() - '6 hours'::INTERVAL)
mss_masterlog-> AND NOT action GROUP BY srca ORDER BY COUNT DESC LIMIT 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=240384.69..240384.74 rows=20 width=12) (actual time=24340.04..24340.39 rows=20 loops=1)
-> Sort (cost=240384.69..240426.80 rows=16848 width=12) (actual time=24340.02..24340.14 rows=21 loops=1)
Sort Key: count(srca)
-> Aggregate (cost=237938.36..239201.95 rows=16848 width=12) (actual time=24322.84..24330.73 rows=23 loops=1)
-> Group (cost=237938.36..238780.75 rows=168478 width=12) (actual time=24322.57..24328.45 rows=320 loops=1)
-> Sort (cost=237938.36..238359.55 rows=168478 width=12) (actual time=24322.55..24324.34 rows=320 loops=1)
Sort Key: srca
-> Seq Scan on mss_fwevent (cost=0.00..223312.60 rows=168478 width=12) (actual time=24253.66..24319.87 rows=320 loops=1)
Filter: ((sensorid = 7) AND (evtime > (now() - '06:00'::interval)) AND (NOT "action"))
Total runtime: 24353.67 msec
(10 rows)
mss_masterlog=> SET enable_seqscan = false;
SET
mss_masterlog=> EXPLAIN ANALYZE SELECT srca, COUNT(srca) FROM mss_fwevent WHERE
mss_masterlog-> sensorid = 7 AND evtime > (now() - '6 hours'::INTERVAL)
mss_masterlog-> AND NOT action GROUP BY srca ORDER BY COUNT DESC LIMIT 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2459596.79..2459596.84 rows=20 width=12) (actual time=162.92..163.25 rows=20 loops=1)
-> Sort (cost=2459596.79..2459638.91 rows=16848 width=12) (actual time=162.90..163.01 rows=21 loops=1)
Sort Key: count(srca)
-> Aggregate (cost=2457150.46..2458414.05 rows=16848 width=12) (actual time=135.62..143.46 rows=23 loops=1)
-> Group (cost=2457150.46..2457992.85 rows=168478 width=12) (actual time=135.35..141.22 rows=320 loops=1)
-> Sort (cost=2457150.46..2457571.66 rows=168478 width=12) (actual time=135.33..137.14 rows=320 loops=1)
Sort Key: srca
-> Index Scan using mss_fwevent_evtime_sensorid_idx on mss_fwevent (cost=0.00..2442524.70 rows=168478 width=12) (actual time=68.36..132.84 rows=320 loops=1)
Index Cond: ((evtime > (now() - '06:00'::interval)) AND (sensorid = 7))
Filter: (NOT "action")
Total runtime: 163.60 msec
(11 rows)
mss_masterlog=> SET enable_seqscan = true;
SET
mss_masterlog=> SET random_page_cost = 0.32;
SET
mss_masterlog=> EXPLAIN ANALYZE SELECT srca, COUNT(srca) FROM mss_fwevent WHERE
mss_masterlog-> sensorid = 7 AND evtime > (now() - '6 hours'::INTERVAL)
mss_masterlog-> AND NOT action GROUP BY srca ORDER BY COUNT DESC LIMIT 20;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=227274.85..227274.90 rows=20 width=12) (actual time=28.42..28.75 rows=20 loops=1)
-> Sort (cost=227274.85..227316.97 rows=16848 width=12) (actual time=28.41..28.52 rows=21 loops=1)
Sort Key: count(srca)
-> Aggregate (cost=224828.52..226092.11 rows=16848 width=12) (actual time=20.26..28.13 rows=23 loops=1)
-> Group (cost=224828.52..225670.91 rows=168478 width=12) (actual time=19.99..25.86 rows=320 loops=1)
-> Sort (cost=224828.52..225249.72 rows=168478 width=12) (actual time=19.98..21.76 rows=320 loops=1)
Sort Key: srca
-> Index Scan using mss_fwevent_evtime_sensorid_idx on mss_fwevent (cost=0.00..210202.76 rows=168478 width=12) (actual time=0.35..17.61 rows=320 loops=1)
Index Cond: ((evtime > (now() - '06:00'::interval)) AND (sensorid = 7))
Filter: (NOT "action")
Total runtime: 29.09 msec
(11 rows)
And there 'ya have it. The times are different from when I had him
send me the queries this morning, but they're within an order of
magnitude difference between each and show the point. Oh, today they
did a bunch of pruning of old data (nuked June's data)... the runtime
differences are basically the same though.
> > 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?
>
> I think such material belongs in the SGML docs, not hidden away in a
> config file that people may not look at...
The config file isn't hidden though and is very visible in the tuning
process and to DBAs. I don't know if a PostgreSQL distributions ship
with TCP connections enabled by default (FreeBSD doesn't), so the
config is always seen and viewed by DBAs. If it's not the TCP
connections setting, it's the max connections setting or sort_mem,
etc... having the values dup'ed in the SGML, however, would be good
too, but it's of most practical relevance in the actual config: as an
admin setting up a DB, I'd rather not have to fish around on
postgresql.org to find a recommended setting, having it inline and
just having to uncomment it is by far and away the most DBA friendly
and likely to be used in the wild by admins.
-sc
--
Sean Chittenden
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: sean@chittenden.org, tgl@sss.pgh.pa.us
Subject: Re: Moving postgresql.conf tunables into 2003...
In-Reply-To: <20030704000646.GV72567@perrin.int.nxad.com>
References: <20030703190502.GR72567@perrin.int.nxad.com> <2422.1057264735@sss.pgh.pa.us> <20030704000646.GV72567@perrin.int.nxad.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox