Hello all,
Executive summary: I have btree index bloat ... I have read all of the
threads I could find on the problem and wanted to confirm that there are no
tuning parameters that could at least reduce the severity of the problem
Detail:
PostgreSQL 8.0.1 on RHEL3
Overall Database Size: 9GB
Size of "problem" table: 6 million rows
Ballpark guess on INSERT/UPDATE/DELETE queries on this table: over 1
million/day
postgresql.conf:
shared_buffers = 32768
work_mem = 4096
maintenance_work_mem = 262144
max_fsm_pages = 1000000
max_fsm_relations = 10000
(all other tuning parameters are at their defaults)
Index size is in MB 'Clean DB' 'Live DB' 'Slack'
------------------------------------------------------
campaign_patron_unq 215.5 498.1 282.6
campaign_email_pkey 143.1 295.3 152.1
email_patron_idx 143.1 290.8 147.7
referral_idx 95.2 223.7 128.5
email_campaign_idx 143.1 221.5 78.4
email_detail_last_mod_idx 126.1 161.6 35.5
The way I've measured the 'slack' in the index is by restoring a pg_dump of
the Live DB to a clean machine and comparing the relpages
SELECT oid, relowner, relname, relpages FROM pg_class ORDER BY relpages
DESC;
I do a nightly VACUUM (not VACUUM FULL) and have my max_fsm_pages and
max_fsm_relations set to high levels ... I've never seen any log entries
suggesting that I bump either of these values up ...
Needless to say, these indexes take over an hour to REINDEX on our live
server which is a large problem due to the exclusive locking .. I saw this
guy's post (dated April 2005) but saw no responses to it ... Is
non-exclusive-locking REINDEX in the works the same way that
non-exclusive-locking VACUUM was introduced a few versions ago?
http://www.mail-archive.com/pgsql-general@postgresql.org/msg59655.html
Until then, are there any other tuning parameters I can set to at least
minimize the severity of the problem?
Thanks in advance,
Dave
