The purpose of a regular aka "lazy" vacuum is to mark dead tuples
generated from updates and deletions as reusable by future inserts and
updates. It doesn't recover any disk space, but allows currently wasted
space to be reused. "Full" vacuums, otoh, serve the purpose of
reclaiming unused space.
You're attachments of vacuum output don't seem right to me, as the 6:00
output had no user defined tables in it, only system tables.
However, based on your 4:00 output, I'd say you need to start doing more
frequent lazy vacuums on user_logs_digest and user_traffic. Try and
determine how long it takes for 15% of those tables to be updated or
delete/insert and then set a cron task to do a lazy vacuum analyze at
that frequency. (This is in additional to regular vacuuming on the rest
of the database)
It's possible your suffering index bloat as well and you might need to
do some regular reindexing, but I'd try the above first and see if it
helps.
Robert Treat
On Fri, 2003-01-03 at 06:48, Gaetano Mendola wrote:
> Hi all, happy new year.
>
> I have a Postgresql server 7.2.2 running 24/24 7/7,
> is not so much stressed till now but how you can imagine
> the DB disk space grow each day.
> I scheduled twice a day a vacuum but I don't see any space,
> on the disk, free after these two vacuum.
> The only way to obtain same space free is do a reindex on a table with
> 13 indexes, but anyway is not the same free space that I expect to obtain
> with a vacuum. I tried once a week to do a vacuum full, and in this case
> I obtain a lot of space but since last week I can't do anymore a vacuum full
> otherwise the process that do vacuum hang and all future connection to the
> DB hang too till I reach the 64 max connections.
> I tried too to do a reindex on another big table ( ~10^6 rows ) but I cant
> otherwise all processes hang.
> My question is:
>
> What is the meaning of the vacuum if the space on the DB continue to grown?
> The vacuum operazion don't seem mark some rows to be again utilizable.
> You can find attached the result of a vacuum sheduled at 4:00 AM and of a
> vacuum analyze scheduled at 6:00 AM.
> And a picture of how the disk usage continue to grown... :-(
>
> What is going on ? Any suggestion ?
>
>
> Ciao
> Gaetano
