Re: Postgresql 'eats' all mi data partition - Mailing list pgsql-bugs

> [sszabo@megazone.bigpanda.com]
>
> On Fri, 26 Sep 2003, Tomas Szepe wrote:
>
> > > [sszabo@megazone.bigpanda.com]
> > >
> > > Did you use -f on the vacuumdb?  If not, it did a normal vacuum (which
> > > isn't likely to help) not a full vacuum.
> >
> > There are scenarios where VACUUM FULL is not an option because
> > of its resource-hungriness and plain VACUUM just doesn't seem
> > to help.
> >
> > We have a production database that happens to receive several
> > thousand row updates per minute.  We VACUUM ANALYZE every four
> > hours with max_fsm_pages set to 2100000, and it's no use.
>
> Hmm, what does vacuum verbose say?

(postgres is 7.3.4 on x86 Linux)

INFO:  --Relation pg_catalog.pg_description--
INFO:  Pages 12: Changed 0, Empty 0; Tup 1390: Vac 0, Keep 0, UnUsed 1.
    Total CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  --Relation pg_toast.pg_toast_16416--
INFO:  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.
WARNING:  Skipping "pg_group" --- only table or database owner can VACUUM it
INFO:  --Relation pg_catalog.pg_proc--
INFO:  Pages 58: Changed 0, Empty 0; Tup 1492: Vac 0, Keep 0, UnUsed 165.
    Total CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  --Relation pg_toast.pg_toast_1255--
INFO:  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.
INFO:  --Relation pg_catalog.pg_rewrite--
INFO:  Pages 4: Changed 0, Empty 0; Tup 27: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_16410--
INFO:  Pages 4: Changed 0, Empty 0; Tup 16: Vac 0, Keep 0, UnUsed 1.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_type--
INFO:  Pages 4: Changed 0, Empty 0; Tup 178: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_attribute--
INFO:  Pages 16: Changed 0, Empty 0; Tup 914: Vac 0, Keep 0, UnUsed 4.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_class--
INFO:  Pages 4: Changed 0, Empty 0; Tup 138: Vac 0, Keep 0, UnUsed 44.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_inherits--
INFO:  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.
INFO:  --Relation pg_catalog.pg_index--
INFO:  Pages 3: Changed 0, Empty 0; Tup 69: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_operator--
INFO:  Pages 13: Changed 0, Empty 0; Tup 643: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_opclass--
INFO:  Pages 1: Changed 0, Empty 0; Tup 51: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_am--
INFO:  Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_amop--
INFO:  Pages 1: Changed 0, Empty 0; Tup 180: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_amproc--
INFO:  Pages 1: Changed 0, Empty 0; Tup 57: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_language--
INFO:  Pages 1: Changed 0, Empty 0; Tup 3: Vac 0, Keep 0, UnUsed 3.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_largeobject--
INFO:  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.
INFO:  --Relation pg_catalog.pg_aggregate--
INFO:  Pages 1: Changed 0, Empty 0; Tup 60: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_trigger--
INFO:  Pages 1: Changed 0, Empty 0; Tup 2: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_listener--
INFO:  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.
INFO:  --Relation pg_catalog.pg_cast--
INFO:  Pages 2: Changed 0, Empty 0; Tup 174: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_namespace--
INFO:  Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING:  Skipping "pg_shadow" --- only table or database owner can VACUUM it
INFO:  --Relation pg_catalog.pg_conversion--
INFO:  Pages 2: Changed 0, Empty 0; Tup 114: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_depend--
INFO:  Pages 20: Changed 0, Empty 0; Tup 2834: Vac 0, Keep 0, UnUsed 66.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_catalog.pg_attrdef--
INFO:  Pages 1: Changed 0, Empty 0; Tup 4: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_16384--
INFO:  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.
INFO:  --Relation pg_catalog.pg_constraint--
INFO:  Pages 1: Changed 0, Empty 0; Tup 5: Vac 0, Keep 0, UnUsed 0.
    Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  --Relation pg_toast.pg_toast_16386--
INFO:  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.
WARNING:  Skipping "pg_database" --- only table or database owner can VACUUM it
INFO:  --Relation pg_catalog.pg_statistic--
INFO:  Index pg_statistic_relid_att_index: Pages 4; Tuples 189: Deleted 187.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Removed 187 tuples in 15 pages.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Pages 18: Changed 9, Empty 0; Tup 189: Vac 187, Keep 0, UnUsed 259.
    Total CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  --Relation pg_toast.pg_toast_16408--
INFO:  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.
INFO:  --Relation public.contract_ips--
INFO:  Index contract_ips_pkey: Pages 430; Tuples 743: Deleted 37893.
    CPU 0.03s/0.13u sec elapsed 0.16 sec.
INFO:  Removed 37893 tuples in 609 pages.
    CPU 0.01s/0.07u sec elapsed 0.07 sec.
INFO:  Pages 1113: Changed 24, Empty 0; Tup 743: Vac 37893, Keep 0, UnUsed 36763.
    Total CPU 0.08s/0.20u sec elapsed 0.28 sec.
INFO:  Truncated 1113 --> 110 pages.
    CPU 0.05s/0.00u sec elapsed 0.41 sec.
INFO:  --Relation pg_toast.pg_toast_50107070--
INFO:  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.
INFO:  --Relation public.authinfo--
INFO:  Index authinfo_pkey: Pages 733; Tuples 930: Deleted 47430.
    CPU 0.04s/0.18u sec elapsed 0.44 sec.
INFO:  Removed 47430 tuples in 717 pages.
    CPU 0.02s/0.07u sec elapsed 0.09 sec.
INFO:  Pages 1380: Changed 29, Empty 0; Tup 930: Vac 47430, Keep 0, UnUsed 45290.
    Total CPU 0.10s/0.27u sec elapsed 0.60 sec.
INFO:  --Relation public.stats_min--
INFO:  Index stats_min_start: Pages 34445; Tuples 1985464: Deleted 404651.
    CPU 1.19s/2.41u sec elapsed 17.86 sec.
INFO:  Index stats_min_pkey: Pages 76501; Tuples 1986938: Deleted 404651.
    CPU 3.98s/5.47u sec elapsed 217.07 sec.
INFO:  Removed 404651 tuples in 6118 pages.
    CPU 0.83s/0.77u sec elapsed 13.52 sec.
INFO:  Pages 25295: Changed 4615, Empty 0; Tup 1985464: Vac 404651, Keep 0, UnUsed 468220.
    Total CPU 7.19s/8.78u sec elapsed 252.67 sec.
INFO:  --Relation public.stats_hr--
INFO:  Index stats_hr_start: Pages 57654; Tuples 10811294: Deleted 348991.
    CPU 3.09s/5.27u sec elapsed 63.67 sec.
INFO:  Index stats_hr_pkey: Pages 78301; Tuples 10814527: Deleted 348991.
    CPU 5.11s/6.39u sec elapsed 152.78 sec.
INFO:  Removed 348991 tuples in 8333 pages.
    CPU 1.09s/0.92u sec elapsed 36.46 sec.
INFO:  Pages 217213: Changed 1362, Empty 0; Tup 10810476: Vac 348991, Keep 0, UnUsed 352822.
    Total CPU 17.09s/13.98u sec elapsed 284.52 sec.
INFO:  --Relation public.stats_hr_old--
INFO:  Pages 60984: Changed 0, Empty 0; Tup 3232113: Vac 0, Keep 0, UnUsed 0.
    Total CPU 2.76s/0.45u sec elapsed 13.90 sec.
VACUUM

> One other thing is to find where the space is going.  Some of that might
> be ending up in indexes which (unfortunately) on 7.3 and earlier aren't
> going to get cleaned up by vacuum and will instead need a reindex.

That's very likely happening in our case I'm afraid.

Hmm, you seem to suggest that we might expect a change in this regard
as 7.4 ships.  Is that right?

Thanks for your interest in this problem,
--
Tomas Szepe <szepe@pinerecords.com>