From: Martin Foster <martin@ethereal-realms.org>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Clearing rows periodically
Date: Fri, 18 Jul 2003 00:55:12 -0600
Message-ID: <3F1799D0.1010009@ethereal-realms.org> (raw)
In-Reply-To: <3F17E2A2.28685.45CC782@localhost>
References: <3F13E18A.21241.4B86CB0@localhost>
<3F17E2A2.28685.45CC782@localhost>
I have two tables in the database which are used almost every time
someone makes use of the webpages themselves. The first, is some sort
of database side parameter list which stores parameters from session to
session. While the other, is a table that handles the posting activity
of all the rooms and chatters.
The first is required in all authentication with the system and when
entries are missing you are challenged by the system to prove your
identity. This table is based on a randomized order, as in the unique
number changes pseudo randomly and this table sees a reduction in
entries every hour on the hour as to keep it's information fresh and
manageable.
The other table follows a sequential order and carries more columns of
information. However, this table clears it's entry nightly and with
current settings will delete roughly a days traffic sitting at 50K rows
of information.
The difference is as follows: Without making the use of vacuum every
hour the parameter table performs very well, showing no loss in service
or degradation. Since people authenticate more then post, it is safe
to assume that it removes more rows daily then the posting table.
The posting table often drags the system down in performance when a day
has been skipped, which includes the use of VACUUM ANALYZE EXPLAIN.
This seems to be an indication that the process of a daily delete is
actually a very wise step to take, even if the information itself is not
needed for very long.
A VACUUM FULL will correct the issue, but put the site out of commission
for roughly 20 minutes as the drive crunches the information.
My question is, should the purging of rows be done more often then once
a day for both tables. Is this why performance seems to take a hit
specifically? As there were too many rows purged for vacuum to
accurately keep track of?
Martin Foster
Creator/Designer Ethereal Realms
martin@ethereal-realms.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: martin@ethereal-realms.org
Subject: Re: Clearing rows periodically
In-Reply-To: <3F1799D0.1010009@ethereal-realms.org>
References: <3F13E18A.21241.4B86CB0@localhost> <3F17E2A2.28685.45CC782@localhost> <3F1799D0.1010009@ethereal-realms.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox