Thread: Index bloat in 7.2
I've got a box running 7.2.1 (yes, I know :-() in which an index for a rapidly turning over (and regularly vacuumed) table is growing steadily in size. The index in question is on a timestamp field that is just set to now() on the entry of the row, to enable the query that clears out old data to an archive to run efficiently. Reindexing shrinks it back to a reasonable size. Other indexes reach an equilibrium size and stay there. The behaviour is fine on a system running 7.4.x: the index stays at a sensible number of pages. Is this likely to be related to a known issue with 7.2 that got fixed, or have I got potentially more serious problems? Thanks Julian Scarfe
"Julian Scarfe" <julian.scarfe@ntlworld.com> writes:
> I've got a box running 7.2.1 (yes, I know :-() in which an index for a
> rapidly turning over (and regularly vacuumed) table is growing steadily in
> size. The index in question is on a timestamp field that is just set to
> now() on the entry of the row, to enable the query that clears out old data
> to an archive to run efficiently. Reindexing shrinks it back to a
> reasonable size. Other indexes reach an equilibrium size and stay there. The
> behaviour is fine on a system running 7.4.x: the index stays at a sensible
> number of pages.
That's exactly what I'd expect ...
regards, tom lane
Clinging to sanity, julian.scarfe@ntlworld.com ("Julian Scarfe") mumbled into her beard:
> I've got a box running 7.2.1 (yes, I know :-() in which an index for
> a rapidly turning over (and regularly vacuumed) table is growing
> steadily in size. The index in question is on a timestamp field
> that is just set to now() on the entry of the row, to enable the
> query that clears out old data to an archive to run efficiently.
> Reindexing shrinks it back to a reasonable size. Other indexes reach
> an equilibrium size and stay there. The behaviour is fine on a
> system running 7.4.x: the index stays at a sensible number of pages.
>
> Is this likely to be related to a known issue with 7.2 that got fixed, or
> have I got potentially more serious problems?
The "empty pages not reclaimed" problem is something that did indeed
get fixed in the post-7.2 days. I _think_ it was 7.4, but it might
have been 7.3.
When we were running 7.2, we used to fairly regularly (e.g. - about
every other month) need to schedule maintenance windows in order to
reindex tables in order to resolve this issue. Some indices on
heavily-update tables would get pretty big "dead zones" that only
reindexing would fix.
The last it was discussed, there still seemed to be a _theoretical_
possibility of there still being a pathological case even in 7.4, but
nobody has reported it in practice. That case would result if you
dropped down to 1 index entry remaining "live" per page. That would
be a very "sparse" handling of things, leaving >98% of the page empty,
and there's no obvious mechanism to merge such pages back together.
But as you're deleting _all_ old entries, that would clear out the
relevant index pages entirely, so that they could be reclaimed.
In short, 7.4.x is indeed a good resolution to your issue.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/sgml.html
"I would guess that he really believes whatever is politically
advantageous for him to believe." -- Alison Brooks, referring to
Michael Portillo, on soc.history.what-if
From: "Christopher Browne" <cbbrowne@acm.org> > The "empty pages not reclaimed" problem is something that did indeed > get fixed in the post-7.2 days. I _think_ it was 7.4, but it might > have been 7.3. > In short, 7.4.x is indeed a good resolution to your issue. From: "Tom Lane" <tgl@sss.pgh.pa.us> > > That's exactly what I'd expect ... Thanks both. So it sounds like: a) the issue is controllable with a regular (and in our case, just occasional) reindex without any long term negative consequences b) Only a dump-restore major version upgrade (which we'll do next time we can take the system out for long enough) will avoid the issue. Julian
On Mon, Dec 06, 2004 at 08:48:04AM -0000, Julian Scarfe wrote: > b) Only a dump-restore major version upgrade (which we'll do next time we > can take the system out for long enough) will avoid the issue. "Long enough" could be a minutes or seconds issue if you use Slony-I, I've heard ... (Of course you'd still need to fix your apps, which may take somewhat longer than that.) -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) Y una voz del caos me habló y me dijo "Sonríe y sé feliz, podría ser peor". Y sonreí. Y fui feliz. Y fue peor.
> On Mon, Dec 06, 2004 at 08:48:04AM -0000, Julian Scarfe wrote: > >> b) Only a dump-restore major version upgrade (which we'll do next >> time we >> can take the system out for long enough) will avoid the issue. On 6 Dec 2004, at 16:18, Alvaro Herrera wrote: > > "Long enough" could be a minutes or seconds issue if you use Slony-I, > I've heard ... (Of course you'd still need to fix your apps, which may > take somewhat longer than that.) A good point Alvaro, but I don't think Slony-I is compatible with 7.2, which is the version I'm starting from. For upgrades from 7.3 onwards, it's certainly a route worth considering. Julian
Quoth alvherre@dcc.uchile.cl (Alvaro Herrera): > On Mon, Dec 06, 2004 at 08:48:04AM -0000, Julian Scarfe wrote: > >> b) Only a dump-restore major version upgrade (which we'll do next time we >> can take the system out for long enough) will avoid the issue. > > "Long enough" could be a minutes or seconds issue if you use Slony-I, > I've heard ... (Of course you'd still need to fix your apps, which may > take somewhat longer than that.) Unfortunately, Slony-I does not support versions of PostgreSQL earlier than 7.3.3. It needs namespace support... -- (format nil "~S@~S" "cbbrowne" "gmail.com") http://www.ntlug.org/~cbbrowne/advocacy.html How come you don't ever hear about gruntled employees?