From: | "David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | index bloat |
Date: | 2005-07-07 20:48:54 |
Message-ID: | 200507072048.j67Kms5f019755@relay1.nnco.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-07-07 20:49:00 | Re: find objects under a specific tablespace |
Previous Message | Michael Fuhr | 2005-07-07 20:41:09 | Re: table name as variable within Function |