Thread: Postgres Databases growing without much reason
Hello,
we are running Postgres 7.2.1 on a RH 7.2 System.
Currently there is a (imho) rather small Database running on the Server
and there are no other services eating up much of the CPU time. Only
postgres acts pretty strange.
The postgres 'data' directory grows far too fast. 90% of the queries to
postgres are simple selects, and there are about 190 Tables which mostly
are having ONLY 200-3000 rows. The 2 largest tables have 67000 and 132000
rows.
We had heavy performance problems and in the end I found, that the 'data'
directory
went up to 2 GB. This, by far, appeared too much for such a little dbase.
I completely dumped the dbase, dropped and restored it and instead of 2 GB
it was
only using 150 MB then. Now, after having this running for about 2 Weeks
constantly,
the performance is falling down again and the database grew up to 550 MB
again, although
there was not much inserted into the dbase-tables.
What is happening here ? Will upgrading to 7.2.4 or 7.3 help me out of this
dilemma ?
There are no views, rules or triggers put on this dbase so far and the
Apache/PHP combo
isnt using transactions either.
Has anyone else found this behaviour so far ?
Any input appreciated and urgently needed :)
l8r
Andreas Rust - webnova GmbH
rust@webnova.de - www.webnova.de
Tel: +49 (0)234 - 912 96 10
Fax: +49 (0)234 - 912 96 15
+:----------------------------------------------------------:+
Internet Solutions & Creative Design
On Fri, 2003-02-14 at 05:58, Andreas Rust wrote: How often are you running vacuum? Tony Grant > we are running Postgres 7.2.1 on a RH 7.2 System. > Currently there is a (imho) rather small Database running on the Server > and there are no other services eating up much of the CPU time. Only > postgres acts pretty strange. > > The postgres 'data' directory grows far too fast. 90% of the queries to > postgres are simple selects, and there are about 190 Tables which mostly > are having ONLY 200-3000 rows. The 2 largest tables have 67000 and 132000 > rows. > > We had heavy performance problems and in the end I found, that the 'data' > directory > went up to 2 GB. This, by far, appeared too much for such a little dbase. > > I completely dumped the dbase, dropped and restored it and instead of 2 GB > it was > only using 150 MB then. Now, after having this running for about 2 Weeks > constantly, > the performance is falling down again and the database grew up to 550 MB > again, although > there was not much inserted into the dbase-tables. > > What is happening here ? Will upgrading to 7.2.4 or 7.3 help me out of this > dilemma ? > There are no views, rules or triggers put on this dbase so far and the > Apache/PHP combo > isnt using transactions either. > > Has anyone else found this behaviour so far ? > > Any input appreciated and urgently needed :) -- www.tgds.net Library management software toolkit, redhat linux on Sony Vaio C1XD, Dreamweaver MX with Tomcat and PostgreSQL
At 18:38 14.02.03, you wrote:
>On Fri, 2003-02-14 at 05:58, Andreas Rust wrote:
>
>How often are you running vacuum?
Forgot to mention that:
VACUUM ANALYZE doesnt help anything. No matter how often I run it.
Cron is doing it daily.
l8r
Andreas Rust - webnova GmbH
rust@webnova.de - www.webnova.de
Tel: +49 (0)234 - 912 96 10
Fax: +49 (0)234 - 912 96 15
+:----------------------------------------------------------:+
Internet Solutions & Creative Design
On Fri, Feb 14, 2003 at 11:58:00AM +0100, Andreas Rust wrote: > I completely dumped the dbase, dropped and restored it and instead of 2 GB > it was > only using 150 MB then. Now, after having this running for about 2 Weeks > constantly, > the performance is falling down again and the database grew up to 550 MB > again, although > there was not much inserted into the dbase-tables. Can you find which files in the data directory are growing? They're numbered by oid, which you can lookup in pg_class. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
At 14:03 14.02.03, you wrote:
>On Fri, Feb 14, 2003 at 11:58:00AM +0100, Andreas Rust wrote:
> > I completely dumped the dbase, dropped and restored it and instead of 2 GB
> > it was
> > only using 150 MB then. Now, after having this running for about 2 Weeks
> > constantly,
> > the performance is falling down again and the database grew up to 550 MB
> > again, although
> > there was not much inserted into the dbase-tables.
>
>Can you find which files in the data directory are growing? They're numbered
>by oid, which you can lookup in pg_class.
Yes, I can ...
After an advice, I just did "vacuum full" instead of analyze and that
brought the DB
down to some 350 MB again ... no time this week anymore, but will check
into that
middle next week again.
Thx for all your help, ppl :)
l8r
Andreas Rust - webnova GmbH
rust@webnova.de - www.webnova.de
Tel: +49 (0)234 - 912 96 10
Fax: +49 (0)234 - 912 96 15
+:----------------------------------------------------------:+
Internet Solutions & Creative Design
--- Andreas Rust <rust@webnova.de> wrote: > After an advice, I just did "vacuum full" instead of > analyze and that > brought the DB > down to some 350 MB again ... That will shrink the on-disk size of the tables, but not the indexes. There is work going on to fix this for the next version of PostgreSQL, but in the meantime you will need to look at a workaround if index growth becomes a problem for you. Search the archives for references to "index bloat" and "reindex" and you should find plenty of stuff. __________________________________________________ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com
On Fri, 14 Feb 2003, Andreas Rust wrote: > At 14:03 14.02.03, you wrote: > >On Fri, Feb 14, 2003 at 11:58:00AM +0100, Andreas Rust wrote: > > > I completely dumped the dbase, dropped and restored it and instead of 2 GB > > > it was > > > only using 150 MB then. Now, after having this running for about 2 Weeks > > > constantly, > > > the performance is falling down again and the database grew up to 550 MB > > > again, although > > > there was not much inserted into the dbase-tables. > > > >Can you find which files in the data directory are growing? They're numbered > >by oid, which you can lookup in pg_class. > > Yes, I can ... > > After an advice, I just did "vacuum full" instead of analyze and that > brought the DB > down to some 350 MB again ... no time this week anymore, but will check > into that > middle next week again. As a note for when you come back to this, if vacuum full reclaims a bunch of space, but vacuum (without full) ends up with unbounded growth, it's possible that you're exceeding the free space map settings and will need to look at increasing them.
On Fri, 14 Feb 2003, Stephan Szabo wrote: > > On Fri, 14 Feb 2003, Andreas Rust wrote: > > > At 14:03 14.02.03, you wrote: > > >On Fri, Feb 14, 2003 at 11:58:00AM +0100, Andreas Rust wrote: > > > > I completely dumped the dbase, dropped and restored it and instead of 2 GB > > > > it was > > > > only using 150 MB then. Now, after having this running for about 2 Weeks > > > > constantly, > > > > the performance is falling down again and the database grew up to 550 MB > > > > again, although > > > > there was not much inserted into the dbase-tables. > > > > > >Can you find which files in the data directory are growing? They're numbered > > >by oid, which you can lookup in pg_class. > > > > Yes, I can ... > > > > After an advice, I just did "vacuum full" instead of analyze and that > > brought the DB > > down to some 350 MB again ... no time this week anymore, but will check > > into that > > middle next week again. > > As a note for when you come back to this, if vacuum full reclaims a bunch > of space, but vacuum (without full) ends up with unbounded growth, it's > possible that you're exceeding the free space map settings and will need > to look at increasing them. > But the original message said there was very little inserted or updated (at least I think it said updated) and no rules or triggers. I was thinking that there was something going on here that the poster wasn't aware off. Perhaps the app does do updates that don't change the data, for some reason, and so the poster doesn't see any inserts/updates from his reports. -- Nigel J. Andrews