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