Re: Memory bloating - Mailing list pgsql-general

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