From: Robert B. Easter <reaster@comptechnews.com>
To: Peter T. Brown <peter@memeticsystems.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: controlling process priority
Date: Thu, 20 Dec 2001 08:24:41 -0500
Message-ID: <200112201324.fBKDOgM15049@comptechnews.com> (raw)
In-Reply-To: <007301c18902$39df9b20$7d00000a@PETER>
References: <007301c18902$39df9b20$7d00000a@PETER>
You might be interested in a little C function I made (benice.c):
//--------------------------------------------------
#include <unistd.h>
#include <errno.h>
#include "postgres.h"
#include "fmgr.h"
/**
* benice - increment nice priority of backend
*
* boolean benice( integer inc );
*
* The nice priority is incremented by inc:
* -20 is highest priority (won't dec lower),
* 19 is lowest priority (won't inc higher)
* Only root can specify a negative number
* for inc.
*
* Useful for slowing down a backend that
* is performing a long process so that
* other backends can be more responsive.
* Since negative inc is not allowed,
* once backend is slowed, it cannot be
* sped up. This should be used only
* for long-running query tasks that
* start a backend and then disconnect
* afterward. Newly started backends
* get default nice (0) again.
*
* Returns:
* true on success, false on error
*/
PG_FUNCTION_INFO_V1(benice);
Datum
benice(PG_FUNCTION_ARGS) {
int32 arg = PG_GETARG_INT32(0);
if( nice((int)arg) ) {
elog(NOTICE, "benice: %s", strerror(errno) );
PG_RETURN_BOOL(false); // error
}
PG_RETURN_BOOL(true); // success
}
//--------------------------------------------------
Load it something like this (benice.sql):
----------------------------------------------------
DROP FUNCTION benice(integer);
CREATE FUNCTION benice(integer)
RETURNS boolean
AS '/home/reaster/prog/triggers/benice/benice.so'
LANGUAGE 'c';
----------------------------------------------------
Compile something like this (Makefile):
#---------------------------------------------------
benice.so: benice.c
gcc -shared -I/usr/local/pgsql/include
-I/usr/src/postgresql-7.1.3/src/include $^ -o $@
clean:
rm -f *.so
#---------------------------------------------------
If you do "SELECT benice(10);", you should be able to verify that it works by
looking at the NICE column for the backend using "top" (on Linux).
Bob
On Wednesday 19 December 2001 09:58 pm, Peter T. Brown wrote:
> Hi--
>
> I've got a complicated SQL select/insert which inserts up to 1 million
> records when run and can take up to 20 minutes to complete. Is there a way
> to control the priority of this select such that when Postgres is busy with
> other (higher priority) tasks it gets delayed??
>
> My problem is that when dealing with lots of traffic, if one of these
> complex queries gets run, all the query traffic is slowed considerably thus
> hurting the performance of my application.
>
>
>
> Thanks,
>
> Peter T. Brown
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reaster@comptechnews.com, peter@memeticsystems.com
Subject: Re: controlling process priority
In-Reply-To: <200112201324.fBKDOgM15049@comptechnews.com>
* 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