Re: table size growing out of control - Mailing list pgsql-general

On Tue, 2002-07-16 at 19:18, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> writes:
> > But as I understand it, the standard, non-blocking vacuum marks
> > unused pages for reuse by the backend.  That approach can only
> > "remember" so many recovered pages.  Adjusting the free space map
> > setting improves that, so if you have a lot of turnover in your
> > tables, you can increase the FSM and vacuum more frequently.  You
> > still need up to double the size of the table, however, to
> > accommodate the turnover.
>
> Only if your vacuum schedule is to vacuum once per 100% turnover of
> the table contents.  If you vacuum as often as, say, 10% of the table
> rows are updated or deleted, then you should see the table size
> remaining at about 10% over the minimum possible size.  So it's a
> straight tradeoff of CPU expenditure versus disk space.  Tables that
> get a lot of update activity need to be vacuumed often to keep them
> from bloating.
>
> Or at least that's the theory.  It can fall down if your FSM size
> is too small to let all the free space be tracked.  We've also seen
> some reports since 7.2 release of tables growing when it didn't appear
> that they should, but I'm unconvinced yet whether those cases were
> PG bugs or application problems (eg, old open transactions preventing
> VACUUM from reclaiming space).
>
> > Is there a way to verify the existence of the old open transactions?
>
> select * from pg_stat_activity;
>
> or
>
> ps aux | grep postgres

those are the ways I had been thinking, here are some results:

rms=# select * from pg_stat_activity;
  datid   | datname | procpid | usesysid | usename  | current_query
----------+---------+---------+----------+----------+---------------
11914305 | rms     |    2355 |        1 | postgres |
11914305 | rms     |   29985 |        1 | postgres |
11914305 | rms     |    4586 |        1 | postgres |
11914305 | rms     |    1999 |        1 | postgres |
11914305 | rms     |    5290 |        1 | postgres |
11914305 | rms     |   21195 |        1 | postgres |
11914305 | rms     |    5179 |        1 | postgres |
11914305 | rms     |    2755 |        1 | postgres |
11914305 | rms     |    4708 |        1 | postgres |
11914305 | rms     |    2334 |        1 | postgres |
11914305 | rms     |   21196 |        1 | postgres |
11914305 | rms     |    2759 |        1 | postgres |
11914305 | rms     |    2690 |        1 | postgres |
11914305 | rms     |   21203 |        1 | postgres |
11914305 | rms     |   26541 |        1 | postgres |
11914305 | rms     |   21202 |        1 | postgres |
11914305 | rms     |    5280 |        1 | postgres |
11914305 | rms     |   21205 |        1 | postgres |
11914305 | rms     |    5047 |        1 | postgres |
11914305 | rms     |    4864 |        1 | postgres |
11914305 | rms     |    5291 |        1 | postgres |
11914305 | rms     |    5250 |        1 | postgres |
(22 rows)

** with current_query blank I'm assuming we have nothing hanging around
that would interfere right?. **

rms=#     SELECT relname, relkind, relpages, relpages / 128 AS MB FROM
pg_class WHERE relname LIKE 'health_%';
        relname        | relkind | relpages | mb
-----------------------+---------+----------+-----
health_ex_group       | i       |     7433 |  58
health_exception_test | r       |    18165 | 141

rms=# vacuum analyze verbose health_exception_test;
NOTICE:  --Relation health_exception_test--
NOTICE:  Index health_ex_group: Pages 9698; Tuples 5715: Deleted 639447.
CPU 0.72s/2.97u sec elapsed 12.20 sec.
NOTICE:  Removed 639447 tuples in 6585 pages.
CPU 0.91s/0.70u sec elapsed 8.04 sec.
NOTICE:  Pages 24749: Changed 178, Empty 0; Tup 5715: Vac 639447, Keep
0, UnUsed 1758908.
Total CPU 2.79s/3.80u sec elapsed 21.53 sec.
NOTICE:  --Relation pg_toast_11914691--
NOTICE:  Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE:  Analyzing health_exception_test
VACUUM

rms=#
rms=#     SELECT relname, relkind, relpages, relpages / 128 AS MB FROM
pg_class WHERE relname LIKE 'health_%';
        relname        | relkind | relpages | mb
-----------------------+---------+----------+-----
health_ex_group       | i       |     9698 |  75
health_exception_test | r       |    24749 | 193

rms=# select * from pg_stat_activity;
  datid   | datname | procpid | usesysid | usename  | current_query
----------+---------+---------+----------+----------+---------------
11914305 | rms     |    2355 |        1 | postgres |
11914305 | rms     |   29985 |        1 | postgres |
11914305 | rms     |    4586 |        1 | postgres |
11914305 | rms     |    1999 |        1 | postgres |
11914305 | rms     |    5290 |        1 | postgres |
11914305 | rms     |   21195 |        1 | postgres |
11914305 | rms     |    5179 |        1 | postgres |
11914305 | rms     |    2755 |        1 | postgres |
11914305 | rms     |    4708 |        1 | postgres |
11914305 | rms     |    2334 |        1 | postgres |
11914305 | rms     |   21196 |        1 | postgres |
11914305 | rms     |    2759 |        1 | postgres |
11914305 | rms     |    2690 |        1 | postgres |
11914305 | rms     |   21203 |        1 | postgres |
11914305 | rms     |   26541 |        1 | postgres |
11914305 | rms     |   21202 |        1 | postgres |
11914305 | rms     |    5280 |        1 | postgres |
11914305 | rms     |   21205 |        1 | postgres |
11914305 | rms     |    5047 |        1 | postgres |
11914305 | rms     |    4864 |        1 | postgres |
11914305 | rms     |    5291 |        1 | postgres |
11914305 | rms     |    5250 |        1 | postgres |
(22 rows)


Correct me if I'm wrong, but this seems to indicate that my table is
growing, vacuum is not reusing space, and there are no stale
transactions lying around.  Now for the record I am still using the
default FSM settings, I guess I need to either up this *a lot* or start
vacuuming every 5 minutes?

Robert Treat