pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed Some Suggestions
3+ messages / 2 participants
[nested] [flat]
* Need Some Suggestions
@ 2005-10-06 21:29 Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Lane Van Ingen @ 2005-10-06 21:29 UTC (permalink / raw)
To: pgsql-performance
I have an application that is prone to sudden, unscheduled high bursts of
activity, and
I am finding that the application design permits me to detect the activity
bursts within
an existing function. The bursts only affect 3 tables, but degradation
becomes apparent
after 2,000 updates, and significant after 8,000 updates.
I already know that a plain vacuum (without full, analyze, or free options)
solves my
problem. Since vacuum is classified in the documentation as an SQL command,
I tried to
call it using a trigger function on one the tables (they all have roughly
the same insert
/ update rate). However, I just found out that vacuum cannot be called by a
function.
Vacuums done by a scheduler at 3AM in the morning are adequate to handle my
non-peak
needs otherwise.
autovacuum sounds like it would do the trick, but I am on a WINDOWS 2003
environment, but
I have Googled up messages that it still has various problems (in Windows)
which won't be
resolved until 8.1 is out. But I have a problem NOW, and the application is
deployed
around the world.
QUESTION:
Is there anyway anyone knows of to permit me to execute an operating
system program
(even vacuumdb) or possibly to add a C function to the library which would
allow me to
do this (I am not a C programmer, but have access to some persons who are)?
Very important to me for performance reasons.
Does anybody have some suggestions on the best path for me to take?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need Some Suggestions
@ 2005-10-07 07:52 Richard Huxton <dev@archonet.com>
parent: Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2005-10-07 07:52 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; +Cc: pgsql-performance
Lane Van Ingen wrote:
> I have an application that is prone to sudden, unscheduled high bursts of
> activity, and
> I am finding that the application design permits me to detect the activity
> bursts within
> an existing function. The bursts only affect 3 tables, but degradation
> becomes apparent
> after 2,000 updates, and significant after 8,000 updates.
Hmm - assuming your free-space settings are large enough, it might be
adequate to just run a vacuum on the 3 tables every 5 minutes or so. It
sounds like these are quite small tables with a lot of activity, so if
there's not much for vacuum to do it won't place too much load on your
system.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Need Some Suggestions
@ 2005-10-07 13:37 Lane Van Ingen <lvaningen@esncc.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Lane Van Ingen @ 2005-10-07 13:37 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
You are correct, in that these tables are not large (50,000 records), but
their effect on performance is noticeable. Plain VACUUM (no freeze, full,
etc)
does the trick well, but I am unable to figure a way to call the 'plain
vanilla
version' of VACUUM via a PostgreSQL trigger function (does not allow it).
Using the Windows scheduler (schtask, somewhat like Unix cron) is an option,
but not a good one, as it takes too much out of the platform to run. My
client
does not use strong platforms, so I have to be concerned about that. VACUUM
is
a minimum impact on performance when running. I believe it would be much
better
to be able to call VACUUM out of a function, the same way in which other SQL
commands are used.
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Friday, October 07, 2005 3:53 AM
To: Lane Van Ingen
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Need Some Suggestions
Lane Van Ingen wrote:
> I have an application that is prone to sudden, unscheduled high bursts of
> activity, and I am finding that the application design permits me to
detect
> the activity bursts within an existing function. The bursts only affect 3
> tables, but degradation becomes apparent after 2,000 updates, and quite
> significant after 8,000 updates.
Hmm - assuming your free-space settings are large enough, it might be
adequate to just run a vacuum on the 3 tables every 5 minutes or so. It
sounds like these are quite small tables with a lot of activity, so if
there's not much for vacuum to do it won't place too much load on your
system.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-07 13:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-06 21:29 Need Some Suggestions Lane Van Ingen <lvaningen@esncc.com>
2005-10-07 07:52 ` Richard Huxton <dev@archonet.com>
2005-10-07 13:37 ` Lane Van Ingen <lvaningen@esncc.com>
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