Updates - Mailing list pgsql-general

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@megazone23.bigpanda.com>
To: "Micah Anderson" <micah@colltech.com>
Cc: <pgsql-general@postgresql.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@bigpanda.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