disk space usage enlarging despite vacuuming

From: Tzvetan Tzankov <ceco(at)noxis(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: disk space usage enlarging despite vacuuming
Date: 2003-05-14 12:02:09
Message-ID: opro5vpvk40gzlyf@mail.noxis.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hallo,

I use debian package postgresql 7.3.2r1-2, it is set to vacuum every 5
hours and once weekly (sunday) vacuum -f, aditionally there are some
session tables which vacuum at 5 minutes, dispite this the disk usage
enlarges with 300-400MB for about 2 days and in sundey with the full vacuum
very few MB-s are recovered.

Once already happened that the full disk is used and we had to stop the
server and reload all the data in a clean initdb

The full dump of all DBs is as follows

-rw-r--r-- 1 ogi ogi 53M May 13 16:58 auto.pgd.tgz
-rw-r--r-- 1 ogi ogi 13M May 13 16:59 direct.pgd.tgz
-rw-r--r-- 1 ogi ogi 8.3K May 13 16:57 dom.pgd
-rw-r--r-- 1 ogi ogi 3.8M May 13 16:58 music.pgd
-rw-r--r-- 1 ogi ogi 1.4M May 13 16:56 store.pgd
-rw-r--r-- 1 ogi ogi 6.0M May 13 17:00 wallet.pgd

(the first two are dumped with large objects, that's why they are tgz)

with dbsize from the contrib I find in the moment that

auto -> 67MB (fairly well)
direct -> 279MB !!!
dom -> 3MB (fairly well)
music -> 201MB !!!
store -> 8MB (fairly well)
wallet -> 821MB !!!!

I've changed
max_fsm_relations = 2000
max_fsm_pages = 20000

as I've read that this would help, but it doesn't

Now I'll explain something about the DBs and their usage, which may be
useful:
auto is not very ofter modified and does not have any data changed
frequently, uses large objects, foreign keys and triggers.
direct is very hi loaded, the hiest load of all (I have written some
functions in C for that), has some tables on 5-minute-vacuum, uses large
objects, foreign keys and triggers, temporal tables.
dom is very rarely used and non problematic
music - doesn't have any usage of large objects, foreign keys and triggers,
only used frequently and has temporal tables, also has some 5-minute-
vacuums
store - used not rarely, have sessions, foreign keys and triggers (and does
not have problems with it, as it is seen from the sizes)
wallet - very strange, not user frequently but all the time with
modifications on the tables, also has some 5-minute-vacuums and has
temporal tables

so may have missed some table from being 5-minute-vacuumed, but don't think
it is the problem since they are 5-hour-vacuumed, the only thing I see is
that all the problematic tables use temporal tables ...

any kind of help would be appreciated, and I'll provide more info if needed

thanks in advance
ceco

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Manfred Koizar 2003-05-14 14:12:39 Re: Serialization, Locking...implement processing Queue with a table
Previous Message Madhavi Daroor 2003-05-14 11:17:03 Schedule Jobs in Postgres