Re: Postgresql 'eats' all mi data partition

> [sszabo(at)megazone(dot)bigpanda(dot)com]
>
> On Fri, 26 Sep 2003, Tomas Szepe wrote:
>
> > > [sszabo(at)megazone(dot)bigpanda(dot)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(at)pinerecords(dot)com>