Thread: get/set priority of PostgreSQL backends

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

On Sat, Apr 7, 2012 at 11:06 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> 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 thought you were limited to only settings above 0 and your own
processes in linux.

On Sat, Apr 7, 2012 at 11:05 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Sat, Apr 7, 2012 at 11:06 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
>> 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 thought you were limited to only settings above 0 and your own
> processes in linux.

For non-root users, you may always only *increase* the priority values
of your processes, and the default priority value is 0. So yes as
non-root, you're effectively limited to positive and increasing values
for setpriority(), and of course you may only alter process priorities
running under the same user. I think that's what I was saying above,
though maybe I wasn't so clear.

For example, if you try to lower your own backend's priority with this
function, you'll get a warning like this:

test=# SELECT set_backend_priority(pg_backend_pid(), -1);
WARNING:  Not possible to lower a process's priority (currently 0)
 set_backend_priority
----------------------
 f
(1 row)


Josh