Re: db growing out of proportion - Mailing list pgsql-bugs

From Peter Childs
Subject Re: db growing out of proportion
Date
Msg-id Pine.LNX.4.44.0305301012040.22127-100000@RedDragon.Childs
Whole thread Raw
In response to Re: db growing out of proportion  (Tomas Szepe <szepe@pinerecords.com>)
Responses Re: db growing out of proportion
List pgsql-bugs
On Fri, 30 May 2003, Tomas Szepe wrote:

> > [sszabo@megazone23.bigpanda.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

pgsql-bugs by date:

Previous
From: Tomas Szepe
Date:
Subject: Re: db growing out of proportion
Next
From: Tom Lane
Date:
Subject: Re: db growing out of proportion