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