Re: Proposal: backend "niceness" / session_priority

On Fri, Jul 31, 2015 at 3:48 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:

> On 7/30/15 10:54 AM, Tom Lane wrote:
>
>> =?ISO-8859-15?Q?Jos=E9_Luis_Tall=F3n?= <jltallon(at)adv-solutions(dot)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] 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] -
http://www.postgresql.org/message-id/CA+U5nMLK2dVcW7ymZ_uBRNQqeNmvDbVyW+OZmUfBKvWBALnARw@mail.gmail.com

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com