Thread: 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
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

"Robert B. Easter" <reaster@comptechnews.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@sss.pgh.pa.us]
Sent: Thursday, December 20, 2001 7:10 AM
To: Robert B. Easter
Cc: Peter T. Brown; pgsql-sql@postgresql.org
Subject: Re: [SQL] controlling process priority


"Robert B. Easter" <reaster@comptechnews.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