pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robert B. Easter <reaster@comptechnews.com>
To: Peter T. Brown <peter@memeticsystems.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: controlling process priority
Date: Thu, 20 Dec 2001 08:24:41 -0500
Message-ID: <200112201324.fBKDOgM15049@comptechnews.com> (raw)
In-Reply-To: <007301c18902$39df9b20$7d00000a@PETER>
References: <007301c18902$39df9b20$7d00000a@PETER>

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



view thread (4+ messages)  latest in thread

Message-ID: <200112201324.fBKDOgM15049@comptechnews.com>
Permalink:  ../200112201324.fBKDOgM15049@comptechnews.com/
Also on:    postgresql.org/message-id/200112201324.fBKDOgM15049@comptechnews.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: reaster@comptechnews.com, peter@memeticsystems.com
  Subject: Re: controlling process priority
  In-Reply-To: <200112201324.fBKDOgM15049@comptechnews.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox