Re: controlling process priority

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

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

"Robert B. Easter" <reaster(at)comptechnews(dot)com> writes:
> [ C function to nice down the backend ]

This sort of thing has been suggested before, but I've always wondered
whether it isn't counterproductive. The trouble is priority inversion:
any time the niced backend is holding a lock on some shared
datastructure, it will be blocking the allegedly-higher-priority other
backends.

regards, tom lane

I had an idea last night that addresses the process priority and the locking
issue: use LIMIT with an offset in a long running thread to programmatically
throttle the backend processing.

This works only because I am storing all the results to my queries as
pointers in a temporary table (then using those values as the basis for
subsequent queries).

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, December 20, 2001 7:10 AM
To: Robert B. Easter
Cc: Peter T. Brown; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] controlling process priority

"Robert B. Easter" <reaster(at)comptechnews(dot)com> writes:
> [ C function to nice down the backend ]

This sort of thing has been suggested before, but I've always wondered
whether it isn't counterproductive. The trouble is priority inversion:
any time the niced backend is holding a lock on some shared
datastructure, it will be blocking the allegedly-higher-priority other
backends.

regards, tom lane