database file size bloat

From: Matthew Arnison <maffew(at)physics(dot)usyd(dot)edu(dot)au>
To: pgsql-general(at)postgresql(dot)org
Cc: Rabble-Rouser <rabble(at)protest(dot)net>, Manse Jacobi <jacobi(at)freespeech(dot)org>
Subject: database file size bloat
Date: 2000-04-13 16:40:09
Message-ID: Pine.LNX.4.21.0004140232450.27788-100000@poota.cat.org.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi folks,

we are using a fairly simple database, with stuff being retrieved and
updated and inserted via php over the web. we have code in there to check
that queries sent to psql are always less than 8000 bytes.

three times now this week (on two different servers) the raw database on
disk has ballooned in size, from about 10 megs to 50 megs in two cases,
and from about 10 megs to 250 megs in another case.

a VACUUM VERBOSE ANALYZE; cleans it back down to the proper size, but
meanwhile all the queries take half a minute, instead of less than a
second. and our load average skyrockets.

here is a dump from VACUUM:

NOTICE: --Relation webcast--
NOTICE: Pages 5568: Changed 0, Reapped 5553, Empty 0, New 0; Tup 93: Vac
35065, Keep/VTL 0/0, Crash 0, UnUsed 29, MinLen 766, MaxLen
4782; Re-using: Free/Avail. Space 45303704/45303704; EndEmpty/Avail. Pages
0/5553. Elapsed 1/0 sec.
NOTICE: Rel webcast: Pages: 5568 --> 15; Tuple(s) moved: 93. Elapsed
415/0 sec.

we are running postgresql-6.5.2-1, redhat 6.0, pii-350, 384 megs RAM.

i thought of upgrading, but couldn't see anything in the 6.5.3 changelogs
that would help. checked through the mail archives, couldn't find anything
on a quick search, couldn't see anything in the FAQ.

i am wondering if we can prevent this bloat from happening. any help
appreciated! this server could get very busy over the weekend, and we want
it to stay up under the load. :)

cheers,
matthew.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Lockhart 2000-04-13 16:43:01 [Fwd: [HACKERS] Porting reports (cont'd)]
Previous Message Bruce Momjian 2000-04-13 16:22:58 Re: Changes from 6.5 to 7.0?