Updates

Su if you update a row, you are still keeping a copy of the old row? And to
get rid of that is to use vacuum?

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: "Micah Anderson" <micah(at)colltech(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, October 03, 2000 1:10 PM
Subject: Re: [GENERAL] Memory bloating

>
> Are you using a fair number of updates? Postgres uses a
> non-overwriting storage manager, so updates effectively create
> a new row and mark the old one as no longer valid (a great
> simplification of reality, but...)
>
> A useful thing would be an example of the queries that are giving
> problems along with their explain output and any indexes that are on the
> table in question.
>
> Stephan Szabo
> sszabo(at)bigpanda(dot)com
>
> On Tue, 3 Oct 2000, Micah Anderson wrote:
>
> > Hello,
> >
> > We are using postgresql exclusively for our database backend for a
> > high-traffic site, sustaining 3-4 pages per second, in many cases
> > bursting well over that. At least half of those accesses are pgsql
> > SELECT, we rarely, if at all, use DELETE. It seems worst on tables with
> > more than about 1000 rows or 1000 hits an hour, or both.
> >
> > Recently our load has been threatening us, fluxating from between 5 and
> > 50 (yes, we've even been forced to reboot the machine it was so bad).
The
> > load has been isolated to postgres, and we can't seem to tame it. If we
> > cannot find a way to make postgres behave we will be forced to move to
> > mysql, we don't want to, but it has been discussed since we can't seem
to
> > isolate this problem. This is a last ditch effort to find out what can
be
> > done before we dump postgresql.
> >
> > The bloat is essentially the raw database file filling up VERY rapidly,
> > like up to 300 megs of bloat over 8 hours, when we vacuum it drops down
> > to 2 megs and the CPU is reduced considerably. Vacuum VERBOSE says
things
> > like 3000 pages of data, 45000 pages freed. The VACUUM VERBOSE I include
> > below is particularly striking (but not the best one I've seen), it
> > reduced the 14 meg database filesize down to 3 megs, 2136 pages to 356
> > that's a factor of 7!!
> >
> > Does this have anything to do with how we use vartext? Should we switch
> > to fixed sized text fields?
> >
> > Is it that 3-4 hits/sec is too much for postgresql to handle?
> >
> > btw - using 7.0.2
> >
> > Following is our tables and an output of a VACUUM VERBOSE:
> >
> > List of relations
> > Name | Type | Owner
> > -----------+----------+----------
> > groupid | sequence | postgres
> > groups | table | postgres
> > webcast | table | postgres
> > webcastid | sequence | postgres
> > weblink | table | postgres
> > weblinkid | sequence | postgres
> > (6 rows)
> >
> > active_prague=# \d webcast
> > Table "webcast"
> > Attribute | Type | Modifier
> > --------------+--------------+----------
> > id | integer | not null
> > heading | char(90) |
> > author | char(45) |
> > date_entered | char(45) |
> > article | text |
> > contact | varchar(80) |
> > link | varchar(160) |
> > address | varchar(160) |
> > phone | varchar(80) |
> > parent_id | integer |
> > mime_type | char(50) |
> > summary | text |
> > numcomment | smallint |
> > arttype | char(50) |
> > html_file | char(160) |
> > created | timestamp |
> > modified | timestamp |
> > linked_file | char(160) |
> > mirrored | boolean |
> > display | boolean |
> >
> >
> > The following Vacuum reduced the 14 meg database filesize down to 3
> > megs, that is 2136 pages to 356 that's a factor of 7!!
> >
> > NOTICE: --Relation webcast--
> > NOTICE: Pages 2136: Changed 0, reaped 1781, Empty 0, New 0; Tup 1913:
> > Vac 9561, Keep/VTL 0/0, Crash 0, UnUsed 1433, MinLen 726, MaxLen 6858;
> > Re-using: Free/Avail. Space 14514420/14514420; EndEmpty/Avail. Pages
> > 0/1781. CPU 0.14s/0.08u sec.
> > NOTICE: Rel webcast: Pages: 2136 --> 356; Tuple(s) moved: 1913. CPU
> > 0.29s/0.05u sec.
> > VACUUM