Received: from comptechnews.com (bdsl.66.12.175.115.gte.net [66.12.175.115]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBKDUdm96796 for ; Thu, 20 Dec 2001 08:30:40 -0500 (EST) (envelope-from reaster@comptechnews.com) Received: from localhost (localhost [[UNIX: localhost]]) by comptechnews.com (8.11.4/8.11.4) id fBKDOgM15049; Thu, 20 Dec 2001 08:24:42 -0500 Message-Id: <200112201324.fBKDOgM15049@comptechnews.com> Content-Type: text/plain; charset="iso-8859-1" From: "Robert B. Easter" To: "Peter T. Brown" Subject: Re: controlling process priority Date: Thu, 20 Dec 2001 08:24:41 -0500 X-Mailer: KMail [version 1.3.1] References: <007301c18902$39df9b20$7d00000a@PETER> In-Reply-To: <007301c18902$39df9b20$7d00000a@PETER> Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200112/215 X-Sequence-Number: 6037 You might be interested in a little C function I made (benice.c): //-------------------------------------------------- #include #include #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