Thread: 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@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
"David Esposito" <pgsql-general@esposito.newnetco.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@sss.pgh.pa.us] > Sent: Thursday, July 07, 2005 11:53 PM > > "David Esposito" <pgsql-general@esposito.newnetco.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@esposito.newnetco.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@sss.pgh.pa.us] > Sent: Friday, July 08, 2005 9:52 AM > > "David Esposito" <pgsql-general@esposito.newnetco.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@esposito.newnetco.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@sss.pgh.pa.us] > Sent: Tuesday, July 12, 2005 10:14 AM > > "David Esposito" <pgsql-general@esposito.newnetco.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@esposito.newnetco.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
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@sss.pgh.pa.us] > Sent: Friday, July 08, 2005 9:52 AM > To: David Esposito > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] index bloat > > "David Esposito" <pgsql-general@esposito.newnetco.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 >
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Tuesday, July 12, 2005 12:26 PM > > "David Esposito" <pgsql-general@esposito.newnetco.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? > When I was first looking into this problem, I googled around a bit and saw that there is a message that's printed when the fsm_pages or fsm_relations is too low ... but I just double checked and can't find the phrase "max_fsm_pages" in any of my logs from the past 2 months (i only run postgres with log_min_error_statement of NOTICE so if it gets logged as an INFO, i probably wouldn't have it in the log files) ... Saturday 1:30am INFO: free space map: 525 relations, 125128 pages stored; 136480 total pages needed DETAIL: Allocated FSM size: 10000 relations + 1000000 pages = 6511 kB shared memory. Monday 1:30am INFO: free space map: 525 relations, 168816 pages stored; 171904 total pages needed DETAIL: Allocated FSM size: 10000 relations + 1000000 pages = 6511 kB shared memory. Tuesday 1:30am INFO: free space map: 528 relations, 172357 pages stored; 170096 total pages needed DETAIL: Allocated FSM size: 10000 relations + 1000000 pages = 6511 kB shared memory.