Skip site navigation (1) Skip section navigation (2)

get/set priority of PostgreSQL backends

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: get/set priority of PostgreSQL backends
Date: 2012-04-07 17:06:28
Message-ID: CAK3UJRHMc+jdi8OMfkwO5Lom79j99uQeerW8bJCVFBLDTr4M7w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi all,

I noticed a note on the 'Priorities' wiki page[1], which talked about
the need for having "a C-language function 'nice_backend(prio)' that
renices the calling backend to "prio".', and suggests posting a link
to this list. Well, here you go:
  http://pgxn.org/dist/prioritize/

The API is a tiny bit different than what was suggested on the wiki;
the wiki suggested "nice_backend()" and "nice_backend_super()",
whereas I just consolidated those into set_backend_priority(), with
permissions checks similar to pg_cancel_backend(). There is also
get_backend_priority(), which should play nicely with the former
function, and perhaps enable scripted queries to automatically bump
priorities based on pg_stat_activity. See the doc[3] for more details.

The wiki says nice_backend_super() might be able to "renice any
backend pid and set any priority, but is usable only by the [database]
superuser", hinting that it would be feasible to lower a backend's
priority value (i.e. increase the scheduling priority). Unfortunately
this is not possible on at least OS X and Linux, where one must be
root to lower priority values. I haven't checked whether this module
works on Windows, would appreciate if someone could give it a shot
there.

I can update the 'Priorities' wiki page in a bit.

Josh

[1] http://wiki.postgresql.org/wiki/Priorities
[3] https://github.com/schmiddy/pg_prioritize/blob/master/doc/prioritize.md

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2012-04-07 18:05:04
Subject: Re: get/set priority of PostgreSQL backends
Previous:From: Kim HansenDate: 2012-04-06 22:09:36
Subject: Re: Planner selects slow "Bitmap Heap Scan" when "Index Scan" is faster

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group