Re: database size grows (even after vacuum (full and analyze))....

Hello all,

My subject has been discussed before. I've browsed through the archives
and did what I could understand (I'm doing "vaccum analyze" on each of
my tables every 30 minutes and "vacuum full" on each of the tables every
2 hour).

My database gets about 30 INSERTs per minute, about 60 UPDATEs per
minute and about 30 DELETEs per minute in a way that line counts tend to
stabilize. The test is done in such a way that I can asure a constant
average rate of INSERTs and the same average rate of DELETEs (after a
certain life time for each record).

Dispite this, the file system size still grows (at about 500KB in about
1000 minutes).

I've placed here a pdf file showing my situation. It shows that line
counts tend do stabilize. But pg data size slowlly increases (the sudden
drops are the result of vacuum full, time unit is 5 minutes).
http://robotica.estg.ipvc.pt/software/linux/dbsizes.pdf

Can anyone tell me if this is ok, or should I atend to this matter. My
system is supposed to work continuouslly without maintenance.

Thank you

joao

On Wed, May 03, 2006 at 12:02:18 +0100,
Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> wrote:
>
> My database gets about 30 INSERTs per minute, about 60 UPDATEs per
> minute and about 30 DELETEs per minute in a way that line counts tend to
> stabilize. The test is done in such a way that I can asure a constant
> average rate of INSERTs and the same average rate of DELETEs (after a
> certain life time for each record).
>
> Dispite this, the file system size still grows (at about 500KB in about
> 1000 minutes).

How often are you vacuuming the table?

On Wed, 2006-05-03 at 15:45, Bruno Wolff III wrote:
> On Wed, May 03, 2006 at 12:02:18 +0100,
> Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> wrote:
> > certain life time for each record).
> >
> > Dispite this, the file system size still grows (at about 500KB in about
> > 1000 minutes).
>
> How often are you vacuuming the table?

I wrote that on my original e-mail: I do a "VACUUM ANALYZE" every 30
minutes and a "VACUUM FULL" every 2 hours.

Should I do anything else ? Thanks.

tahnks

joao

On Wed, May 03, 2006 at 18:15:37 +0100,
Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> wrote:
> On Wed, 2006-05-03 at 15:45, Bruno Wolff III wrote:
> > On Wed, May 03, 2006 at 12:02:18 +0100,
> > Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> wrote:
> > > certain life time for each record).
> > >
> > > Dispite this, the file system size still grows (at about 500KB in about
> > > 1000 minutes).
> >
> > How often are you vacuuming the table?
>
> I wrote that on my original e-mail: I do a "VACUUM ANALYZE" every 30
> minutes and a "VACUUM FULL" every 2 hours.

Sorry about that, I obviously missed that.

> Should I do anything else ? Thanks.

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.
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.) A third possible issue is index bloat, which
can happen on older versions (7.4ish) of Postgres where key values increase (or
decrease) montonicly.

> 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.

bruno,

thanks for the feedback.

some things;

1) How can I check these possible 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.) A third possible issue is index bloat, which
> can happen on older versions (7.4ish) of Postgres where key values increase (or
> decrease) montonicly.

2) what is FSM ?

3) mybe you are right. one of the tables has a UNIQUE clause that
includes an ever growing INT column (the UNIQUE clause also includes
some TEXT columns). The other two tables also include one column that is
an ever growing INT, too, with no special clauses. But I'm using Pg7.2
(as originally included in Fedora Core 3). Is this something to care
about or should I simply let it be ?

Thanks

joao

Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> writes:
> On Wed, 2006-05-03 at 15:45, Bruno Wolff III wrote:
>> How often are you vacuuming the table?

> I wrote that on my original e-mail: I do a "VACUUM ANALYZE" every 30
> minutes and a "VACUUM FULL" every 2 hours.

That's not a good maintenance procedure. Just do the plain vacuums and
forget the VACUUM FULL. If the table row count is fairly stable then
plain vacuums are all you need. I suspect VACUUM FULL is making things
worse not better --- it'll compact the tables, but at the price of
bloating the indexes.

You might want to pay some attention to exactly where the space is
disappearing to --- which files are getting bigger?

If you find you are suffering from index bloat, a very occasional
REINDEX (maybe once a week or less) will fix that, but VACUUM FULL
won't help it at all. I wouldn't recommend doing this unless
proven necessary, however.

regards, tom lane

ok, I understand that.

Thank you. I'll try it.

joao

On Wed, 2006-05-03 at 19:12, Tom Lane wrote:
> Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> writes:
> > On Wed, 2006-05-03 at 15:45, Bruno Wolff III wrote:
> >> How often are you vacuuming the table?
>
> > I wrote that on my original e-mail: I do a "VACUUM ANALYZE" every 30
> > minutes and a "VACUUM FULL" every 2 hours.
>
> That's not a good maintenance procedure. Just do the plain vacuums and
> forget the VACUUM FULL. If the table row count is fairly stable then
> plain vacuums are all you need. I suspect VACUUM FULL is making things
> worse not better --- it'll compact the tables, but at the price of
> bloating the indexes.
>
> You might want to pay some attention to exactly where the space is
> disappearing to --- which files are getting bigger?
>
> If you find you are suffering from index bloat, a very occasional
> REINDEX (maybe once a week or less) will fix that, but VACUUM FULL
> won't help it at all. I wouldn't recommend doing this unless
> proven necessary, however.
>
> regards, tom lane

thx

joao

On Wed, May 03, 2006 at 18:57:24 +0100,
Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt> wrote:
>
> 1) How can I check these possible open transactions ?

http://developer.postgresql.org/docs/postgres/monitoring-ps.html

> 2) what is FSM ?

http://developer.postgresql.org/docs/postgres/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Hi Tom and all,

> I wrote that on my original e-mail: I do a "VACUUM ANALYZE" every 30
> > minutes and a "VACUUM FULL" every 2 hours.
>
> That's not a good maintenance procedure. Just do the plain vacuums and
> forget the VACUUM FULL. If the table row count is fairly stable then
> plain vacuums are all you need. I suspect VACUUM FULL is making things
> worse not better --- it'll compact the tables, but at the price of
> bloating the indexes.
>
> You might want to pay some attention to exactly where the space is
> disappearing to --- which files are getting bigger?

Sorry for the long silence. I've been making experiments and reading
about all of this. The 'data' directory is the one growing in size.

>
> If you find you are suffering from index bloat, a very occasional
> REINDEX (maybe once a week or less) will fix that, but VACUUM FULL
> won't help it at all. I wouldn't recommend doing this unless
> proven necessary, however.

I've checked (and read about) REINDEX and applied it. Nevertheless I
still get a litlle continuous growth (about 200Bytes/minute) in the
'data' directory. I must say: REINDEX causes a sudden drop in filesystem
usage but (about 30 minutes later) size returns to where it droped and
continues growing.

What would you say I'dd do next ?

thx
jmf

PS: this problem brought me to an excelent book about Pg. I'm suprised
with the great things I didn't know about Pg...

>
> regards, tom lane

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(at)estg(dot)ipvc(dot)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(at)wolff(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Mon 5/8/2006 7:08 PM
To: Bruno Wolff III
Cc: João Miguel Ferreira; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] database size grows (even after vacuum (full and analyze))....

Bruno Wolff III <bruno(at)wolff(dot)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