pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedClearing rows periodically
4+ messages / 4 participants
[nested] [flat]
* Clearing rows periodically
@ 2003-07-18 06:55 Martin Foster <martin@ethereal-realms.org>
2003-07-18 07:42 ` Re: Clearing rows periodically Paul Thomas <paul@tmsl.demon.co.uk>
2003-07-18 11:34 ` Re: Clearing rows periodically Andrew Sullivan <andrew@libertyrms.info>
2003-07-18 13:31 ` Re: Clearing rows periodically Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 4+ messages in thread
From: Martin Foster @ 2003-07-18 06:55 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clearing rows periodically
2003-07-18 06:55 Clearing rows periodically Martin Foster <martin@ethereal-realms.org>
@ 2003-07-18 07:42 ` Paul Thomas <paul@tmsl.demon.co.uk>
2 siblings, 0 replies; 4+ messages in thread
From: Paul Thomas @ 2003-07-18 07:42 UTC (permalink / raw)
To: Martin Foster <martin@ethereal-realms.org>; +Cc: pgsql-performance
On 18/07/2003 07:55 Martin Foster wrote:
> [snip]
> 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?
ISTR that there are setting in postgresql.conf which affect how many
tables/rows vacuum can reclaim. The docs say that the default setting of
max_fsm_pages is 10000. Maybe this should be increased for your situation?
HTH
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clearing rows periodically
2003-07-18 06:55 Clearing rows periodically Martin Foster <martin@ethereal-realms.org>
@ 2003-07-18 11:34 ` Andrew Sullivan <andrew@libertyrms.info>
2 siblings, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2003-07-18 11:34 UTC (permalink / raw)
To: pgsql-performance
On Fri, Jul 18, 2003 at 12:55:12AM -0600, Martin Foster wrote:
> 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.
> 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.
During your "clearing period", why not do the deletes in batches, and
VACUUM the table periodically. That will allow you to reclaim the
space gradually, and ensure that you don't end up with a big "bald
spot". But you probably want to increase your FSM settings. See the
docs.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clearing rows periodically
2003-07-18 06:55 Clearing rows periodically Martin Foster <martin@ethereal-realms.org>
@ 2003-07-18 13:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-07-18 13:31 UTC (permalink / raw)
To: Martin Foster <martin@ethereal-realms.org>; +Cc: pgsql-performance
Martin Foster <martin@ethereal-realms.org> writes:
> 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?
Given that the hourly purge seems to work well for you, I'd suggest
trying it on both tables.
Non-FULL vacuum is intended to be run *frequently*, say as often as
you've updated or deleted 10% to 50% of the rows in a table. Delaying
it until you've had multiple complete turnovers of the table contents
will cost you.
> As there were too many rows purged for vacuum to
> accurately keep track of?
Only possible if you don't have the FSM parameters set high enough.
Infrequent vacuuming means you need more FSM space, btw.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-07-18 13:31 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-18 06:55 Clearing rows periodically Martin Foster <martin@ethereal-realms.org>
2003-07-18 07:42 ` Paul Thomas <paul@tmsl.demon.co.uk>
2003-07-18 11:34 ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-18 13:31 ` Tom Lane <tgl@sss.pgh.pa.us>
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