index bloat

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

"David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> Size of "problem" table: 6 million rows
> Ballpark guess on INSERT/UPDATE/DELETE queries on this table: over 1
> million/day
> ...
> I do a nightly VACUUM (not VACUUM FULL)

Given those parameters, you should expect a "slack" proportion of about
1/6th of the table. Many of the indexes you show seem to be in that
ballpark --- do you have any idea what's different about the two that
are not?

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, July 07, 2005 11:53 PM
>
> "David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> > Size of "problem" table: 6 million rows
> > Ballpark guess on INSERT/UPDATE/DELETE queries on this table: over 1
> > million/day
> > ...
> > I do a nightly VACUUM (not VACUUM FULL)
>
> Given those parameters, you should expect a "slack"
> proportion of about
> 1/6th of the table. Many of the indexes you show seem to be in that
> ballpark --- do you have any idea what's different about the two that
> are not?
>

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

Hmm, how are you getting 1/6? The ballpark seems to be about 50% or more for
those first 4 ... The thing is, they are all indexes on fields that don't
change over the life of the record so it would seem that the slack would
only build for DELETEs, not UPDATEs ... and the volume of DELETEs is
slightly less than 1 million/week (deleting the oldest records) ... the
campaign_email_pkey is a integer generated from a sequence so it would seem
like that index should be well-behaved since we always add to the right side
of the be of the btree and always delete from the left side ...

The numbers I'm showing above reflect about 4.5 weeks since the last full
REINDEX of the DB ... And they seem to be on a steady charge upward ... I've
been hoping that at some point they 'level off' and just have a constant
amount of slack in them but it seems like they're just growing without bound
..

Thanks again,
Dave

"David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> Hmm, how are you getting 1/6? The ballpark seems to be about 50% or more for
> those first 4 ...

Ooops, I got confused about which column was which.

Could we see the results of "vacuum verbose" on this table? Even
better, verbose output from two successive nightly runs?

regards, tom lane

Sure thing ... I turned on VERBOSE so I'll let it run for the next few days
(the weekend is kind of a bad time since activity on the database is low)
but by monday or tuesday I should have a few nightly runs to post to the
list ..

Thanks,
Dave

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, July 08, 2005 9:52 AM
>
> "David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> > Hmm, how are you getting 1/6? The ballpark seems to be
> about 50% or more for
> > those first 4 ...
>
> Ooops, I got confused about which column was which.
>
> Could we see the results of "vacuum verbose" on this table? Even
> better, verbose output from two successive nightly runs?
>
> regards, tom lane
>

As promised, here are two runs of VACUUM VERBOSE on the problem table ...
There was a lot of activity on the campaign_email table on Friday
(Saturday's VACUUM) as compared with Monday (Tuesday's VACUUM)

Thanks,
Dave

VACUUM VERBOSE from 1:30am Saturday July 9

INFO: vacuuming "xxx.campaign_email"
INFO: index "campaign_email_pkey" now contains 5881034 row versions in
41637 pages
DETAIL: 501822 index row versions were removed.
541 index pages have been deleted, 208 are currently reusable.
CPU 2.82s/4.36u sec elapsed 159.53 sec.
INFO: index "email_campaign_patron_unq" now contains 5881111 row versions
in 70329 pages
DETAIL: 501822 index row versions were removed.
819 index pages have been deleted, 310 are currently reusable.
CPU 4.95s/4.72u sec elapsed 328.90 sec.
INFO: index "email_patron_idx" now contains 5881154 row versions in 38110
pages
DETAIL: 501822 index row versions were removed.
46 index pages have been deleted, 4 are currently reusable.
CPU 3.45s/6.04u sec elapsed 213.03 sec.
INFO: index "email_campaign_idx" now contains 5881215 row versions in 31435
pages
DETAIL: 501822 index row versions were removed.
2016 index pages have been deleted, 896 are currently reusable.
CPU 2.33s/3.61u sec elapsed 179.46 sec.
INFO: index "campaign_email_referral_idx" now contains 5881219 row versions
in 31229 pages
DETAIL: 501822 index row versions were removed.
816 index pages have been deleted, 408 are currently reusable.
CPU 2.15s/4.10u sec elapsed 151.37 sec.
INFO: "campaign_email": removed 501822 row versions in 48373 pages
DETAIL: CPU 3.73s/5.55u sec elapsed 121.64 sec.
INFO: "campaign_email": found 501822 removable, 5880995 nonremovable row
versions in 93960 pages
DETAIL: 2 dead row versions cannot be removed yet.
There were 343940 unused item pointers.
0 pages are entirely empty.
CPU 21.75s/28.97u sec elapsed 1177.92 sec.
INFO: analyzing "patronmail.campaign_email"
INFO: "campaign_email": scanned 3000 of 93960 pages, containing 186577 live
rows and 7 dead rows; 3000 rows in sample, 5843592 estimated total rows

VACUUM VERBOSE from 1:30am Tuesday July 12

INFO: vacuuming "patronmail.campaign_email"
INFO: index "campaign_email_pkey" now contains 5583688 row versions in
42653 pages
DETAIL: 280860 index row versions were removed.
1539 index pages have been deleted, 1181 are currently reusable.
CPU 3.06s/3.71u sec elapsed 153.73 sec.
INFO: index "email_campaign_patron_unq" now contains 5583735 row versions
in 72158 pages
DETAIL: 280860 index row versions were removed.
2320 index pages have been deleted, 1786 are currently reusable.
CPU 4.94s/4.69u sec elapsed 329.98 sec.
INFO: index "email_patron_idx" now contains 5583779 row versions in 38226
pages
DETAIL: 280860 index row versions were removed.
174 index pages have been deleted, 171 are currently reusable.
CPU 3.08s/5.51u sec elapsed 212.61 sec.
INFO: index "email_campaign_idx" now contains 5583831 row versions in 31435
pages
DETAIL: 280860 index row versions were removed.
3266 index pages have been deleted, 2531 are currently reusable.
CPU 2.05s/3.27u sec elapsed 182.20 sec.
INFO: index "campaign_email_referral_idx" now contains 5583840 row versions
in 31632 pages
DETAIL: 280860 index row versions were removed.
1131 index pages have been deleted, 726 are currently reusable.
CPU 2.84s/4.17u sec elapsed 155.22 sec.
INFO: "campaign_email": removed 280860 row versions in 48536 pages
DETAIL: CPU 4.23s/5.11u sec elapsed 133.15 sec.
INFO: "campaign_email": found 280860 removable, 5583666 nonremovable row
versions in 93960 pages
DETAIL: 1 dead row versions cannot be removed yet.
There were 862562 unused item pointers.
0 pages are entirely empty.
CPU 22.22s/26.92u sec elapsed 1200.48 sec.
INFO: analyzing "patronmail.campaign_email"
INFO: "campaign_email": scanned 3000 of 93960 pages, containing 178601 live
rows and 4 dead rows; 3000 rows in sample, 5593783 estimated total rows

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Friday, July 08, 2005 9:52 AM
> To: David Esposito
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] index bloat
>
> "David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> > Hmm, how are you getting 1/6? The ballpark seems to be
> about 50% or more for
> > those first 4 ...
>
> Ooops, I got confused about which column was which.
>
> Could we see the results of "vacuum verbose" on this table? Even
> better, verbose output from two successive nightly runs?
>
> regards, tom lane
>

"David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> As promised, here are two runs of VACUUM VERBOSE on the problem table ...
> There was a lot of activity on the campaign_email table on Friday
> (Saturday's VACUUM) as compared with Monday (Tuesday's VACUUM)

Well, what these numbers show is that you have 5% to 10% daily turnover
of data in this table (maybe more --- are these two days representative,
do you think)? But anyway, taking that number as gospel, you'd expect
that the table and indexes would settle at about 10% free space
immediately after each VACUUM. That would represent a steady state:
just enough free space to get eaten up till the next VACUUM. The table
itself seems to have stabilized, and the "email_campaign_idx" index as
well -- note the latter didn't grow at all, and its internal free space
is in the 10% ballpark:

> INFO: index "email_campaign_idx" now contains 5881215 row versions in 31435
> pages
> DETAIL: 501822 index row versions were removed.
> 2016 index pages have been deleted, 896 are currently reusable.

> INFO: index "email_campaign_idx" now contains 5583831 row versions in 31435
> pages
> DETAIL: 280860 index row versions were removed.
> 3266 index pages have been deleted, 2531 are currently reusable.

I'm not sure why the other indexes don't seem to have reached their
steady-state 10% free yet. What can you tell us about the patterns
of data being inserted into these various indexes?

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, July 12, 2005 10:14 AM
>
> "David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> > As promised, here are two runs of VACUUM VERBOSE on the
> problem table ...
> > There was a lot of activity on the campaign_email table on Friday
> > (Saturday's VACUUM) as compared with Monday (Tuesday's VACUUM)
>
> Well, what these numbers show is that you have 5% to 10%
> daily turnover
> of data in this table (maybe more --- are these two days
> representative,
> do you think)? But anyway, taking that number as gospel, you'd expect
> that the table and indexes would settle at about 10% free space
> immediately after each VACUUM. That would represent a steady state:
> just enough free space to get eaten up till the next VACUUM.
> The table
> itself seems to have stabilized, and the "email_campaign_idx" index as
> well -- note the latter didn't grow at all, and its internal
> free space
> is in the 10% ballpark:
>
> > INFO: index "email_campaign_idx" now contains 5881215 row
> versions in 31435
> > pages
> > DETAIL: 501822 index row versions were removed.
> > 2016 index pages have been deleted, 896 are currently reusable.
>
> > INFO: index "email_campaign_idx" now contains 5583831 row
> versions in 31435
> > pages
> > DETAIL: 280860 index row versions were removed.
> > 3266 index pages have been deleted, 2531 are currently reusable.
>
> I'm not sure why the other indexes don't seem to have reached their
> steady-state 10% free yet. What can you tell us about the patterns
> of data being inserted into these various indexes?
>

These two days are fairly representative ... Friday's being a bit above
average, Monday's being a bit below ... the 10% turnover number sounds about
right ... and I expected to see what you describe: a rough 10% slack in the
data files which is why I am concerned about the growth of just a few
specific indexes ...

the data in this table is the past 50 days worth of history for an emailing
application ... when we send the email, we create the record then use that
record to track the bounce, opt-out, impression, and click activity for up
to 50 days then we roll up all of the data into a statistics table and purge
it ... As for the type of data that's in each of the indexes:

campaign_email_pkey - the primary key on the table to uniquely identify the
email sent; it's value is generated from a sequence ... obviously, the
values in this column don't change once the record is created ..

campaign_patron_unq - a concatenated index which ensures that a given email
address only receives one copy of a given email ... the values in the two
columns in this index don't change once the record is created

email_patron_idx - an index on the email address (actually an integer FK
which points at a table containing the email addresses) ... very high
cardinality ... i'd estimate that for a given key, there are at most 4 or 5
records ... the values in this column doesn't change once the record is
created

email_campaign_idx - an index on the email content that the person was sent
(an integer FK which points at the content in a seperate table) ... the
cardinality for this is pretty low ... there are probably only a few hundred
unique values in this index (still enough to make the index worthwhile, but
for a given key, thousands of records could come back) ... the values in
this column doesn't change once the record is created

The last detail that I thought of that shouldn't have any impact but I
figured was worth mentioning: all of these fields are NUMERIC(11,0) (it's a
long story involving an automated tool used to generate a schema definition
from an E/R diagram ... they should certainly just be INTEGERs, but that's
water under the bridge at this point .. :-)

Thanks again for your time and help,
Dave

"David Esposito" <pgsql-general(at)esposito(dot)newnetco(dot)com> writes:
> As promised, here are two runs of VACUUM VERBOSE on the problem table ...

BTW, the tail of the VACUUM VERBOSE output ought to have something about
overall usage of the FSM --- what does that look like?

regards, tom lane