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 по дате отправления: