Обсуждение: Re: database size grows (even after vacuum (full and analyze))....

Please keep replies copied to the list so that others can learn from and
contribute to the discussion. I don't remember where this was, but it looks
like general is probably reasonable.

On Mon, May 08, 2006 at 15:02:20 +0100,
  Joao Miguel Ferreira <jmf@estg.ipvc.pt> wrote:
> Bruno and all,
>
> > There are a couple of possibilities worth checking. One is that there aren't
> > idle transactions staying open for a long time. These would prevent vacuum
> > from removing deleted rows as these transactions could still see them.
>
> I've checked that. No open transactions.
>
> > Another possibility is that the FSM is too low and there isn't enough space to
> > track all of rows that can be recovered. (Vacuuming more often will also
> > reduce the needed FSM setting.)
> Didn't check this!
>
> > A third possible issue is index bloat, which
> > can happen on older versions (7.4ish) of Postgres where key values increase (or
> > decrease) montonicly.
>
> REINDEX(ing) the indexes causes a sudden drop in filesystem usage, but
> after a while it size gets back to the value when it dropped and still
> grwoing (sime 100 to 300 Bytes/minute).
>
> Yes, my tables contains ever grwoing values afected by a UNIQUE
> constraint.
>
> What would I do next ?
>
> jmf
> (Pg is 7.2, rpm install, Fedora Core 3)

7.2 is subject to index bloat for indexes where the column increase monotonicly
and old values are deleted. In the short run you will want to schedule
regular reindexes.

In the long run, you should upgrade. 7.2 is essentially without support. I
beleive there is still a RHEL version using it that is in support, so a
critical fix might get back ported.

On Mon, 2006-05-08 at 18:07, Bruno Wolff III wrote:
> Please keep replies copied to the list so that others can learn from and
> contribute to the discussion. I don't remember where this was, but it looks
> like general is probably reasonable.

Sorry. I didn't notice. I'll keep that in mind.

> >
> > Yes, my tables contains ever grwoing values afected by a UNIQUE
> > constraint.
> >
> > What would I do next ?
> >
> > jmf
> > (Pg is 7.2, rpm install, Fedora Core 3)
>
> 7.2 is subject to index bloat for indexes where the column increase monotonicly
> and old values are deleted. In the short run you will want to schedule
> regular reindexes.

Well... that seems to answer my questions. Thanks.

>
> In the long run, you should upgrade. 7.2 is essentially without support. I
> beleive there is still a RHEL version using it that is in support, so a
> critical fix might get back ported.

I'll do that.

thanks

jmf

Bruno Wolff III <bruno@wolff.to> writes:
> In the long run, you should upgrade. 7.2 is essentially without support. I
> beleive there is still a RHEL version using it that is in support, so a
> critical fix might get back ported.

No, Red Hat never shipped a RHEL version using 7.2.* (they went straight
from 7.1 to 7.3).  This is not unrelated to the fact that the community
dropped support for 7.2, actually --- I'm sure we'd not be maintaining
7.3 anymore either, if I weren't personally on the hook to support 7.3
for RHEL3.

Bottom line is there's no one out there maintaining 7.2 at all, and even
7.3 and 7.4 are really not getting anything but the most critical bug fixes.

            regards, tom lane

Ok.

I get the point. I'm using 7.2 because that's the one I got from the original Fedora Core 3 CD's.

I'll upgrade to the most recent.

Thank you all for your support.

jmf



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Mon 5/8/2006 7:08 PM
To: Bruno Wolff III
Cc: João Miguel Ferreira; pgsql-general@postgresql.org
Subject: Re: [GENERAL] database size grows (even after vacuum (full and analyze))....

Bruno Wolff III <bruno@wolff.to> writes:
> In the long run, you should upgrade. 7.2 is essentially without support. I
> beleive there is still a RHEL version using it that is in support, so a
> critical fix might get back ported.

No, Red Hat never shipped a RHEL version using 7.2.* (they went straight
from 7.1 to 7.3).  This is not unrelated to the fact that the community
dropped support for 7.2, actually --- I'm sure we'd not be maintaining
7.3 anymore either, if I weren't personally on the hook to support 7.3
for RHEL3.

Bottom line is there's no one out there maintaining 7.2 at all, and even
7.3 and 7.4 are really not getting anything but the most critical bug fixes.

                        regards, tom lane