Thread: Proposal: backend "niceness" / session_priority

Hackers,<br /><br />     I have found myself needing to run some maintenance routines (VACUUM, REINDEX, REFRESH
MATERIALIZEDVIEW mostly) at a lower priority so as not to disturb concurrent *highly transactional* connections. This
issueis also noted within the TODO[0] list in the Wiki .<br /><br /> * There was some discussion on 2007 [1] regarding
"<aclass="external text" href="http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php"
rel="nofollow">Prioritiesfor users or queries?</a>"<br /><br />     Since PostgreSQL lacks the resource management
capabilitiesof the "Big Ones" ( Resource Groups - Red, WorkLoad Manager - Blue ) or the Resource Governor in MS SQL
Server,we can try and approximate the requested behaviour by reducing the CPU priority ("nice") of the backend in
question.Please note that we would be using scheduler priority to try and modulate I/O, though I'm aware of the
limitationsof this mechanism.<br /><br />     Using renice(1) from outside is not only cumbersome and error prone but
verymuch unuseable for the use cases I am contemplating.<br /><br /><br /> * Moveover, as seen in the "Priorities" wiki
page[2], there exists an extension providing a set_backend_priority() function, to be called
"set_backend_priority(pg_backend_pid(),20)". <br /> This approach is, sadly, not portable to non-POSIX operating
systems(e.g. Windows), and IMO quite too convoluted to use and tied to actual implementation details.<br /><br /><br
/><br/> * I have been playing with some code which uses a GUC for this purpose, though only define/support three
differentpriorities would make sense for the final implementation IMO: NORMAL, LOW_PRIORITY, IDLE<br />     Checked
platformcompatibility too: this behaviour can be implemented on Windows, too. For everything else, there's nice (2)<br
/><br/><br /><br /> However, there is a relatively minor catch here which is the reason behind this e-mail: user
interface<br/><br /> - Inventing a new "command" seems overkill to me. Plus, I don't know what we could model it on ---
giventhat the real solution for this problem would be a fully featured "priority manager" ---<br /><br /> - I have been
playingwith a GUC that ignores being reset --- so as to comply with nice's specification when not running as a
privilegeduser --- but I reckon that this behaviour might be surprising at best:<br />     SET session_priority TO
'low';   -- Ok, low priority<br />     VACUUM FREEZE my_test_table;<br />     RESET session_priority;        -- Nope,
stilllow prio. Emit notice?<br /><br />     The way to reset the priority would be to RECONNECT. And this is my main
painpoint.... though it does fullfill the need.<br /><br /><br />     However, this approach does fullfill my needs and
---itseems--- the OP's needs: be able to run a maintenance task at a low priority (i.e. disturbing other concurrent
queriesas little as possible). Expected use case:  cronjob running " psql -c 'SET session_priority TO low; REINDEX
blablaCONCURRENTLY; VACUUM foobar;'"<br /><br /><br /> All suggestions welcome. <br /><br /> I'll be wrapping a
more-or-less-donepatch on monday if somebody wants to take a look and criticize on actual code (I won't be working on
thistomorrow) unless somebody points me at a better solution<br /><br /><br /> Thanks,<br /><br />     / J.L.<br /><br
/><br/><br /> [0] <a class="moz-txt-link-freetext"
href="https://wiki.postgresql.org/wiki/Todo">https://wiki.postgresql.org/wiki/Todo</a>- Miscellaneous performance<br />
[1]<a class="moz-txt-link-freetext"
href="http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php">http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php</a><br
/>[2] <a class="moz-txt-link-freetext"
href="https://wiki.postgresql.org/wiki/Priorities">https://wiki.postgresql.org/wiki/Priorities</a><br/><br /> [3] <a
class="moz-txt-link-freetext"
href="http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm">http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm</a><br
/>[4] <a class="moz-txt-link-freetext"
href="http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.doc/com.ibm.db2.luw.doc-gentopic6.html">http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.doc/com.ibm.db2.luw.doc-gentopic6.html</a><br
/>[5] <a class="moz-txt-link-freetext"
href="https://msdn.microsoft.com/en-us/library/bb933866.aspx">https://msdn.microsoft.com/en-us/library/bb933866.aspx</a><br
/>

José Luis Tallón <jltallon@adv-solutions.net> writes:
>      Since PostgreSQL lacks the resource management capabilities of the 
> "Big Ones" ( Resource Groups - Red, WorkLoad Manager - Blue ) or the 
> Resource Governor in MS SQL Server, we can try and approximate the 
> requested behaviour by reducing the CPU priority ("nice") of the backend 
> in question. Please note that we would be using scheduler priority to 
> try and modulate I/O, though I'm aware of the limitations of this mechanism.

This has been proposed before, and rejected before, and I'm not seeing
anything particularly new here.  Without a credible mechanism for
throttling I/O, "nice" alone does not seem very promising.
        regards, tom lane

On 7/30/15 10:54 AM, Tom Lane wrote:
> José Luis Tallón <jltallon@adv-solutions.net> writes:
>>       Since PostgreSQL lacks the resource management capabilities of the
>> "Big Ones" ( Resource Groups - Red, WorkLoad Manager - Blue ) or the
>> Resource Governor in MS SQL Server, we can try and approximate the
>> requested behaviour by reducing the CPU priority ("nice") of the backend
>> in question. Please note that we would be using scheduler priority to
>> try and modulate I/O, though I'm aware of the limitations of this mechanism.
>
> This has been proposed before, and rejected before, and I'm not seeing
> anything particularly new here.  Without a credible mechanism for
> throttling I/O, "nice" alone does not seem very promising.

Some OSes respect nice when it comes to IO scheduling, so it might still 
be useful. What I'm worried about is priority inversion[1].

What might be useful would be to add a set of GUCs similar to 
vacuum_cost_* that operated at the shared buffer level. Dunno where 
you'd put the sleep though (presumably all the functions where you'd put 
the accounting are too low-level to sleep in).

[1] https://en.wikipedia.org/wiki/Priority_inversion
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 07/31/2015 12:18 AM, Jim Nasby wrote:
>> This has been proposed before, and rejected before, and I'm not seeing
>> anything particularly new here.  Without a credible mechanism for
>> throttling I/O, "nice" alone does not seem very promising.
>
> Some OSes respect nice when it comes to IO scheduling, so it might still
> be useful.

Wouldn't the bgwriter remove a lot of the usefulness?

Andreas