Обсуждение: disk space usage enlarging despite vacuuming
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
On 14 May 2003 at 15:02, Tzvetan Tzankov wrote: > 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. Try reindexing. Space went into indexes is not recovered with vacuum.. That might help.. HTH Bye Shridhar -- Jim Nasium's Law: In a large locker room with hundreds of lockers, the few people using the facility at any one time will all have lockers next to each other so that everybody is cramped.
Tzvetan Tzankov <ceco@noxis.net> writes: > 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. It's probably index growth, which isn't fixed by VACUUM. Do a REINDEX after the weekly VACUUM FULL and you should see that space reclaimed. 7.4 should fix this IIRC. -Doug
Tzvetan Tzankov <ceco@noxis.net> writes:
> 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.
There isn't enough info here to really tell what's going on; you need to
look at the individual tables and indexes of the problem databases to
see where the space is going. (pg_class's relpages column will give
you the right data, if you vacuum first.)
A first guess is that the problem is index bloat, but that's really
theorizing in advance of the data...
regards, tom lane
Tom Lane wrote: > Tzvetan Tzankov <ceco@noxis.net> writes: > > 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. > > There isn't enough info here to really tell what's going on; > you need to > look at the individual tables and indexes of the problem databases to > see where the space is going. (pg_class's relpages column will give > you the right data, if you vacuum first.) (Sorry if it seems I'm hijacking the thread-- my problems seem pretty similar to Tzvetan's, and I thought our problems might be related.) I've noticed similar behavior, and have tracked it down to the pg_largeobject table. pg_largeobject is using about 50 Gigs of disk space in my currently running server (7.2.1), but going through a dump-n-restore cycle always ends up freeing up some disk. Doing it today (restored into a test 7.3.2 database, but results are similar to restoring into 7.2.1) freed up about 13 gigs. (The dump file was 30 gigs, and was current as of about 7am this morning.) We've got a 'vacuum analyze' loop going all the time, and we reindex the database every night. All of that freed space is from the pg_largeobject table space using less space than before. (39 gigs in the new database, compared to ~52 from the old.) When I look at the number of relpages reported for pg_largeobject in the old and new servers, they accurately represent the disk space in use. I guess for me, the question is now: why did the old server think that pg_largeobject has more tuples than the new server does now, and where did they go? Any chance that the client apps have a transaction open on some large_objects (keeping them from being deleted), but the tuple gets deleted through a dump and restore cycle? If that's the case, shouldn't the tuple also get deleted when I stop and restart the server? -ron > > A first guess is that the problem is index bloat, but that's really > theorizing in advance of the data... > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) >
Ron Snyder <snyder@roguewave.com> writes:
> I've noticed similar behavior, and have tracked it down to the
> pg_largeobject table. pg_largeobject is using about 50 Gigs of disk space
> in my currently running server (7.2.1), but going through a dump-n-restore
> cycle always ends up freeing up some disk.
What's your turnover rate for updating or deleting large objects?
I'm guessing that you have the FSM parameters (in postgresql.conf) set
too small to allow the system to keep track of all the free space in
pg_largeobject. Ideally, VACUUM logs all the space it frees into
the FSM, and this space is then reused by subsequent inserts/updates,
and you run out of it right about the time of the next VACUUM run.
But if the FSM is too small then some space "leaks" in each cycle
and you have continuing growth of the table.
There is some code in CVS tip to help you determine whether FSM is large
enough or not, but in 7.2 or 7.3 you gotta work it out for yourself :-(
regards, tom lane
> What's your turnover rate for updating or deleting large objects? There's probably only about 10K additions/day, and there should be about 7500 deletions/day. > I'm guessing that you have the FSM parameters (in postgresql.conf) set > too small to allow the system to keep track of all the free space in > pg_largeobject. Ideally, VACUUM logs all the space it frees into > the FSM, and this space is then reused by subsequent inserts/updates, > and you run out of it right about the time of the next VACUUM run. > But if the FSM is too small then some space "leaks" in each cycle > and you have continuing growth of the table. Doh! I meant to include this info, because I knew you'd want it. max_fsm_pages is 100K, and max_fsm_relations is left at the default (of 100). Thanks, -ron > There is some code in CVS tip to help you determine whether > FSM is large > enough or not, but in 7.2 or 7.3 you gotta work it out for > yourself :-( > > regards, tom lane >
Ron Snyder <snyder@roguewave.com> writes:
>> What's your turnover rate for updating or deleting large objects?
> There's probably only about 10K additions/day, and there should be about
> 7500 deletions/day.
How large are the objects in question?
>> I'm guessing that you have the FSM parameters (in postgresql.conf) set
>> too small to allow the system to keep track of all the free space in
>> pg_largeobject.
> Doh! I meant to include this info, because I knew you'd want it.
> max_fsm_pages is 100K, and max_fsm_relations is left at the default (of
> 100).
100 is almost certainly too small for max_fsm_relations (we've changed
the default to 1000 as of 7.3.something). How many active databases do
you have, and how many user tables?
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Monday, May 19, 2003 3:29 PM > To: Ron Snyder > Cc: Tzvetan Tzankov; pgsql-general@postgresql.org > Subject: Re: [GENERAL] disk space usage enlarging despite vacuuming > > > Ron Snyder <snyder@roguewave.com> writes: > >> What's your turnover rate for updating or deleting large objects? > > There's probably only about 10K additions/day, and there > should be about > > 7500 deletions/day. > > How large are the objects in question? They average 24K (or less). > > >> I'm guessing that you have the FSM parameters (in > postgresql.conf) set > >> too small to allow the system to keep track of all the > free space in > >> pg_largeobject. > > > Doh! I meant to include this info, because I knew you'd want it. > > max_fsm_pages is 100K, and max_fsm_relations is left at the > default (of > > 100). > > 100 is almost certainly too small for max_fsm_relations (we've changed > the default to 1000 as of 7.3.something). How many active > databases do > you have, and how many user tables? In that database cluster, there are 4 databases (template0, template1, pgqv, quickview). A '\d' for the first three says "No relations", and for the last one lists 17. (15 tables, 1 view, 1 sequence). Running the following query for the quickview database: "Select count(*) from pg_indexes where tablename not like 'pg%';" count ----- 31 Thanks, -ron
Or, learn how it works, and write conflicting standards and flood the market with applications using them.
Microsoft just bought the core Caldera enhancements:
http://money.cnn.com/markets/hotstocks/
>
Ron Snyder <snyder@roguewave.com> writes:
>>>> What's your turnover rate for updating or deleting large objects?
>>> There's probably only about 10K additions/day, and there
>>> should be about 7500 deletions/day.
>>
>> How large are the objects in question?
> They average 24K (or less).
So an average update or delete touches at least three pages of
pg_largeobject, probably more. It'd probably be reasonable to estimate
that about 5 * 17500 pages of pg_largeobject have free space on them
after a typical day's activity. That means you need 87500 FSM page
slots just to keep track of pg_largeobject space, never mind what's
going on in your user tables.
You didn't say how large your user tables are, or what kind of update
traffic they see, but I'll bet 100K slots is not near enough for you.
>> 100 is almost certainly too small for max_fsm_relations (we've changed
>> the default to 1000 as of 7.3.something). How many active
>> databases do
>> you have, and how many user tables?
> In that database cluster, there are 4 databases (template0, template1, pgqv,
> quickview). A '\d' for the first three says "No relations", and for the
> last one lists 17. (15 tables, 1 view, 1 sequence).
Let's see ... in 7.2 there are 30 FSM-able system catalogs per database
(count the pg_class entries with relkind 'r' or 't'). Ignoring
template0 which is never vacuumed, you have 105 FSM-able relations in this
cluster. I'd suggest bumping up the setting at least a little bit...
regards, tom lane
I assume your talking about the MAX_FSM_RELATIONS setting in
postgresql.conf?
What are the drawbacks to setting this too high? My database has about
1million (very small row) inserts, and 1 million deletes each day, with
1 table exceeding 5.5million rows, and another just under 1million.
Currently MAX_FSM_RELATIONS is set to 10,000.
select count(*) from pg_class where not relkind in ('i','v');
count
-------
144
(1 row)
select sum(relpages) from pg_class where relkind in ('r','t');
sum
-------
77918
(1 row)
I remember reading MAX_FSM_RELATIONS should be higher then the first
query, and lower then the last query, but thats a huge difference. What
would be the advantages/disadvantages to setting MAX_FSM_RELATIONS to
75,000?
Where does MAX_FSM_PAGES fall in to this?
On Mon, 2003-05-19 at 16:35, Tom Lane wrote:
> Ron Snyder <snyder@roguewave.com> writes:
> >>>> What's your turnover rate for updating or deleting large objects?
> >>> There's probably only about 10K additions/day, and there
> >>> should be about 7500 deletions/day.
> >>
> >> How large are the objects in question?
>
> > They average 24K (or less).
>
> So an average update or delete touches at least three pages of
> pg_largeobject, probably more. It'd probably be reasonable to estimate
> that about 5 * 17500 pages of pg_largeobject have free space on them
> after a typical day's activity. That means you need 87500 FSM page
> slots just to keep track of pg_largeobject space, never mind what's
> going on in your user tables.
>
> You didn't say how large your user tables are, or what kind of update
> traffic they see, but I'll bet 100K slots is not near enough for you.
>
> >> 100 is almost certainly too small for max_fsm_relations (we've changed
> >> the default to 1000 as of 7.3.something). How many active
> >> databases do
> >> you have, and how many user tables?
>
> > In that database cluster, there are 4 databases (template0, template1, pgqv,
> > quickview). A '\d' for the first three says "No relations", and for the
> > last one lists 17. (15 tables, 1 view, 1 sequence).
>
> Let's see ... in 7.2 there are 30 FSM-able system catalogs per database
> (count the pg_class entries with relkind 'r' or 't'). Ignoring
> template0 which is never vacuumed, you have 105 FSM-able relations in this
> cluster. I'd suggest bumping up the setting at least a little bit...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Best Regards,
Mike Benoit
NetNation Communications Inc.
Systems Engineer
Tel: 604-684-6892 or 888-983-6600
---------------------------------------
Disclaimer: Opinions expressed here are my own and not
necessarily those of my employer
Mike Benoit <mikeb@netnation.com> writes:
> I assume your talking about the MAX_FSM_RELATIONS setting in
> postgresql.conf?
> What are the drawbacks to setting this too high?
You waste space in shared memory. IIRC the multiplier is about 50 bytes
per FSM_RELATION slot, so setting it to 1000 costs you ~50K ... hardly
enough to sneeze at anymore.
> Currently MAX_FSM_RELATIONS is set to 10,000.
Well, that's half a meg, which might be more than you care to waste
(certainly the space would be more usefully spent on FSM_PAGES slots).
Unless you're planning a vast expansion of number of tables or
databases, I would think 1000 would do ya.
> Where does MAX_FSM_PAGES fall in to this?
RELATIONS is the number of tables to track in FSM. PAGES is the total
number of pages to track (across all tables). You want to be sure you
can track all the pages that have useful amounts of free space in them.
regards, tom lane
I did reindex which regained only 200-300 MB for all DBs (the situation now is even more bad) only the DB wallet, now is 1.3G !!! after full vacuum and reindexall with relation_size from dbsize I got the following things for the system tables, wich are not very frustraiting I think pg_attribute 11M pg_class 8M pg_depend 2.5M pg_type 1.5M (if the returned value is bytes ;-)), I put only the tables that are larger then 1M) mine tables are also with normal sizes, the sume of them is about 100M in this DB there are no large objects at all what else can I check and how?? I should mention, that this is a situtation 2-3 weeks after a fresh install with initdb thanks in advance ceco Tom Lane wrote: > Tzvetan Tzankov <ceco@noxis.net> writes: > >>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. > > > There isn't enough info here to really tell what's going on; you need to > look at the individual tables and indexes of the problem databases to > see where the space is going. (pg_class's relpages column will give > you the right data, if you vacuum first.) > > A first guess is that the problem is index bloat, but that's really > theorizing in advance of the data... > > regards, tom lane > > . >
Tzvetan Tzankov <ceco@noxis.net> writes:
> only the DB wallet, now is 1.3G !!! after full vacuum and reindexall
> with relation_size from dbsize I got the following things for the system
> tables, wich are not very frustraiting I think
> pg_attribute 11M
> pg_class 8M
> pg_depend 2.5M
> pg_type 1.5M
> (if the returned value is bytes ;-)), I put only the tables that are
> larger then 1M)
> mine tables are also with normal sizes, the sume of them is about 100M
> in this DB there are no large objects at all
Then you've still got about 1.2G left to account for, no?
Have you looked at the sizes of indexes, as opposed to tables?
regards, tom lane