TOAST tables keeps growing!

[Thomas Madsen]

Hi.

I'm running a production server with PostgreSQL version 7.2.1, that does
email scans.
That means up to tens of thousands of entries in the database (log and
quarantine) each day, and an equal amount removed every night.

The problem is that TOAST tables keeps springing forth and consume disk
space.
We do a VACUUM every night, but it does not reduce or stop the TOAST
table growth.

I searched the mailing list for answers to this problem, but found only
other people describing the same problem.
Namely, that their max_fsm_pages were too low and that postgresql now
has lost track of the surplus TOAST data pages.

In our postgresql.conf we ourselves had the default value of 10000 for
max_fsm_pages, which I have now raised to around a million.
I restarted psql and did a vacuum to see if that would reduce the disk
usage, but no change was seen.

So the question(s) is:
What can I do to reclaim the wasted TOAST diskspace?
Can I find and eliminate the lost TOAST tuples somehow?


Thanks!

Best regards,
Thomas M. Madsen.

[Stephan Szabo]

Post by Thomas Madsen
I'm running a production server with PostgreSQL version 7.2.1, that does

You really should upgrade. If not to 7.4.x, at least to the last 7.2
release.

Post by Thomas Madsen
The problem is that TOAST tables keeps springing forth and consume disk
space.
We do a VACUUM every night, but it does not reduce or stop the TOAST
table growth.
I searched the mailing list for answers to this problem, but found only
other people describing the same problem.
Namely, that their max_fsm_pages were too low and that postgresql now
has lost track of the surplus TOAST data pages.
In our postgresql.conf we ourselves had the default value of 10000 for
max_fsm_pages, which I have now raised to around a million.
I restarted psql and did a vacuum to see if that would reduce the disk
usage, but no change was seen.

Generally, raising fsm and revacuuming (without full) will mean that more
of the space will get re-used so that it should stop growing from day to
day, but it's not really going to remove space already taken (excepting
blank pages at the end if it can get appropriate locks I think).

Vacuum Full should remove the blank space at the cost of an exclusive lock
on the table.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Post by Thomas Madsen
What can I do to reclaim the wasted TOAST diskspace?

vacuum full might help.

Post by Thomas Madsen
Can I find and eliminate the lost TOAST tuples somehow?

Well, one thing to ask is whether it's the toast *table* that's
bloating, or the *index* on the toast table. (VACUUM VERBOSE
would help you find out.)

If it's the table, then more frequent plain vacuums and/or increasing
FSM should fix it. If the index is growing while the table stays
about the same, you could try periodic REINDEXes, but the only real
solution is to move up to PG 7.4.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Thomas Madsen]

-----Original Message-----
Sent: 13. august 2004 21:02
To: Thomas Madsen
Subject: Re: [ADMIN] TOAST tables keeps growing!

Post by Thomas Madsen
What can I do to reclaim the wasted TOAST diskspace?

vacuum full might help.

Yes, vacuum full did the trick!

Post by Thomas Madsen
Can I find and eliminate the lost TOAST tuples somehow?

Well, one thing to ask is whether it's the toast *table*
that's bloating, or the *index* on the toast table. (VACUUM
VERBOSE would help you find out.)
If it's the table, then more frequent plain vacuums and/or
increasing FSM should fix it. If the index is growing while
the table stays about the same, you could try periodic
REINDEXes, but the only real solution is to move up to PG 7.4.

The bloating was primarily caused by the TOAST tables (several gigs
surplus), but the TOAST indexes are also slowly bloating as we have
indexes on a UNIX timestamp column. I will have to choose between
reindexing and upgrading...

For now I will keep an eye on the TOAST tables to see if the new FSM
value (1,000,000) keeps the TOAST tables at a reasonable size.

Thank you for the input!

Cheers,
Thomas.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Thomas Madsen]

-----Original Message-----
Sent: 13. august 2004 19:36
To: Thomas Madsen
Subject: Re: [ADMIN] TOAST tables keeps growing!

Post by Thomas Madsen
I'm running a production server with PostgreSQL version 7.2.1, that
does

You really should upgrade. If not to 7.4.x, at least to the
last 7.2 release.

Yes I know, I'm painfully aware of the need to upgrade :))

Post by Thomas Madsen
The problem is that TOAST tables keeps springing forth and consume
disk space. We do a VACUUM every night, but it does not

reduce or stop

Post by Thomas Madsen
the TOAST table growth.
I searched the mailing list for answers to this problem, but found
only other people describing the same problem. Namely, that their
max_fsm_pages were too low and that postgresql now has lost

track of

Post by Thomas Madsen
the surplus TOAST data pages.
In our postgresql.conf we ourselves had the default value

of 10000 for

Post by Thomas Madsen
max_fsm_pages, which I have now raised to around a million. I
restarted psql and did a vacuum to see if that would reduce

the disk

Post by Thomas Madsen
usage, but no change was seen.

Generally, raising fsm and revacuuming (without full) will
mean that more of the space will get re-used so that it
should stop growing from day to day, but it's not really
going to remove space already taken (excepting blank pages at
the end if it can get appropriate locks I think).
Vacuum Full should remove the blank space at the cost of an
exclusive lock on the table.

Thanks, vacuum full on the table itself worked just perfect! ;)

Cheers,
Thomas.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings