Thread: disk space usage enlarging despite vacuuming

disk space usage enlarging despite vacuuming

From
Tzvetan Tzankov
Date:
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

Re: disk space usage enlarging despite vacuuming

From
"Shridhar Daithankar"
Date:
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.


Re: disk space usage enlarging despite vacuuming

From
Doug McNaught
Date:
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

Re: disk space usage enlarging despite vacuuming

From
Tom Lane
Date:
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

Re: disk space usage enlarging despite vacuuming

From
Ron Snyder
Date:
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)
>

Re: disk space usage enlarging despite vacuuming

From
Tom Lane
Date:
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

Re: disk space usage enlarging despite vacuuming

From
Ron Snyder
Date:
> 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
>

Re: disk space usage enlarging despite vacuuming

From
Tom Lane
Date:
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

Re: disk space usage enlarging despite vacuuming

From
Ron Snyder
Date:

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

If you can't write it yourself, buy it, the story of microsoft

From
Dennis Gearon
Date:
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/
>


Re: disk space usage enlarging despite vacuuming

From
Tom Lane
Date:
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

Re: disk space usage enlarging despite vacuuming

From
Mike Benoit
Date:
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


Re: disk space usage enlarging despite vacuuming

From
Tom Lane
Date:
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

Re: disk space usage enlarging despite vacuuming

From
Tzvetan Tzankov
Date:
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
>
> .
>



Re: disk space usage enlarging despite vacuuming

From
Tom Lane
Date:
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