disk space usage enlarging despite vacuuming

Поиск
Список
Период
Сортировка
От Tzvetan Tzankov
Тема disk space usage enlarging despite vacuuming
Дата
Msg-id opro5vpvk40gzlyf@mail.noxis.net
обсуждение исходный текст
Ответы Re: disk space usage enlarging despite vacuuming  ("Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>)
Re: disk space usage enlarging despite vacuuming  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Skip Montanaro
Дата:
Сообщение: Broken upgrade_tips link
Следующее
От: "Dave Page"
Дата:
Сообщение: Re: [webmaster] Broken upgrade_tips link