Proposal: backend "niceness" / session_priority
Hackers,
I have found myself needing to run some maintenance routines
(VACUUM, REINDEX, REFRESH MATERIALIZED VIEW mostly) at a lower priority
so as not to disturb concurrent *highly transactional* connections. This
issue is also noted within the TODO[0]https://wiki.postgresql.org/wiki/Todo - Miscellaneous performance list in the Wiki .
* There was some discussion on 2007 [1]http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php regarding "Priorities for users
or queries?
<http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php>"
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.
Using renice(1) from outside is not only cumbersome and error prone
but very much unuseable for the use cases I am contemplating.
* Moveover, as seen in the "Priorities" wiki page [2]https://wiki.postgresql.org/wiki/Priorities, there exists an
extension providing a set_backend_priority() function, to be called
"set_backend_priority(pg_backend_pid(), 20)".
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.
* I have been playing with some code which uses a GUC for this purpose,
though only define/support three different priorities would make sense
for the final implementation IMO: NORMAL, LOW_PRIORITY, IDLE
Checked platform compatibility too: this behaviour can be
implemented on Windows, too. For everything else, there's nice (2)
However, there is a relatively minor catch here which is the reason
behind this e-mail: user interface
- Inventing a new "command" seems overkill to me. Plus, I don't know
what we could model it on --- given that the real solution for this
problem would be a fully featured "priority manager" ---
- I have been playing with a GUC that ignores being reset --- so as to
comply with nice's specification when not running as a privileged user
--- but I reckon that this behaviour might be surprising at best:
SET session_priority TO 'low'; -- Ok, low priority
VACUUM FREEZE my_test_table;
RESET session_priority; -- Nope, still low prio. Emit notice? The way to reset the priority would be to RECONNECT. And this is my
main pain point.... though it does fullfill the need.
However, this approach does fullfill my needs and ---it seems---
the OP's needs: be able to run a maintenance task at a low priority
(i.e. disturbing other concurrent queries as little as possible).
Expected use case: cronjob running " psql -c 'SET session_priority TO
low; REINDEX blabla CONCURRENTLY; VACUUM foobar;'"
All suggestions welcome.
I'll be wrapping a more-or-less-done patch on monday if somebody wants
to take a look and criticize on actual code (I won't be working on this
tomorrow) unless somebody points me at a better solution
Thanks,
/ J.L.
[0]: https://wiki.postgresql.org/wiki/Todo - Miscellaneous performance
[1]: http://archives.postgresql.org/pgsql-general/2007-02/msg00493.php
[2]: https://wiki.postgresql.org/wiki/Priorities
[3]: http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm
[4]: 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
[5]: https://msdn.microsoft.com/en-us/library/bb933866.aspx
=?ISO-8859-15?Q?Jos=E9_Luis_Tall=F3n?= <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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 7/30/15 10:54 AM, Tom Lane wrote:
=?ISO-8859-15?Q?Jos=E9_Luis_Tall=F3n?= <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]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.
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
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jul 31, 2015 at 3:48 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 7/30/15 10:54 AM, Tom Lane wrote:
=?ISO-8859-15?Q?Jos=E9_Luis_Tall=F3n?= <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).
I think for I/O throttling mainly we need two different kind of
I/O limiting, one is for data/index pages and other for WAL.
It seems to me that we already have some form of throttling for
checkpoint (via checkpoint_completion_target) and similarly for
bgwriter and Vacuum, however we have nothing for WAL writing
or writes done by backends. For WAL, Simon already proposed
some rate limiting mechanism [1]/messages/by-id/CA+U5nMLK2dVcW7ymZ_uBRNQqeNmvDbVyW+OZmUfBKvWBALnARw@mail.gmail.com and for backend writes we can
have check for sleep after every n buffer evictions by backends
where backend needs to write the buffer.
[1]: /messages/by-id/CA+U5nMLK2dVcW7ymZ_uBRNQqeNmvDbVyW+OZmUfBKvWBALnARw@mail.gmail.com
/messages/by-id/CA+U5nMLK2dVcW7ymZ_uBRNQqeNmvDbVyW+OZmUfBKvWBALnARw@mail.gmail.com
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com