Re: Memory bloating

Are you saving usage and navigation statistics via PostgreSQL? Updates to
summary records like a page counter cause a new record to be written and the
old one to be flagged for deletion/cleanup as I understand it. I think the
solution is to vacuum more frequently on certain tables or write a second
teir application that would aggregate the update requests and write them
less frequently...

- k
----- Original Message -----
From: "Micah Anderson" <micah(at)colltech(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, October 03, 2000 11:55 AM
Subject: [GENERAL] Memory bloating

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

--
Micah Anderson
Collective Technologies
www.colltech.com

"To be and not to do is not to be at all"