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

Would more regular vacuum help. I think a vaccum every hour may do
the job. perhaps with an analyse every day. (I presume the statistics
don't change too much)
While I don't surgest doing a vacuum more than twice an hour as
this would slow down the system with little gain more than once a day may
improve the speed and space usage.
Just an idea.

Peter