Re: db growing out of proportion

On Fri, 30 May 2003, Tomas Szepe wrote:

> > [sszabo(at)megazone23(dot)bigpanda(dot)com]
> >
> > > Trouble is, as the rows in the tables get deleted/inserted/updated
> > > (the frequency being a couple thousand rows per minute), the database
> > > is growing out of proportion in size. After about a week, I have
> > > to redump the db by hand so as to get query times back to sensible
> > > figures. A transaction that takes ~50 seconds before the redump will
> > > then complete in under 5 seconds (the corresponding data/base/ dir having
> > > shrunk from ~2 GB to ~0.6GB).
> > >
> > > A nightly VACCUM ANALYZE is no use.
> > >
> > > A VACUUM FULL is no use.
> > >
> > > A VACUUM FULL followed by REINDEX is no use.
> >
> > Is the space being taken up by stats_min, this index, some other object?
>
> relname | relkind | relpages | reltuples
> ---------------------------------+---------+----------+-------------
> stats_hr | r | 61221 | 3.01881e+06
> stats_hr_pkey | i | 26414 | 3.02239e+06
> stats_min_pkey | i | 20849 | 953635
> stats_hr_start | i | 17218 | 3.02142e+06
> stats_min_start | i | 15284 | 949788
> stats_min | r | 10885 | 948792
> authinfo_pkey | i | 1630 | 1342
> authinfo | r | 1004 | 1342
> contract_ips | r | 865 | 565
> contract_ips_pkey | i | 605 | 565
>
> > What does VACUUM FULL VERBOSE stats_min; give you?
>
> Sorry, I can't run a VACUUM FULL at this time.
> We're in production use.

As Tom said, you probably need higher FSM settings, but also, do you have
any long lived transactions (say from some kind of persistent connection
system) that might be preventing vacuum from removing rows?