Re: controlling process priority

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