Re: controlling process priority - Mailing list pgsql-sql

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
negativenumber* for inc.** Useful for slowing down a backend that* is performing a long process so that* other backends
canbe more responsive.* Since negative inc is not allowed,* once backend is slowed, it cannot be* sped up. This should
beused only* for long-running query tasks that* start a backend and then disconnect* afterward. Newly started backends*
getdefault 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 booleanAS '/home/reaster/prog/triggers/benice/benice.so'LANGUAGE 'c';
----------------------------------------------------

Compile something like this (Makefile):

#---------------------------------------------------
benice.so: benice.cgcc -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