Re: Change query priority

Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>
>>I don't know how effective this would be, but you could wrap the
>>system call setpriority() in a user-defined function if your
>>platform supports it. This would set the "nice" value of the
>>backend process, which might serve as a crude prioritization
>>mechanism.
>
>
> Every couple of months someone comes along and says "why don't you
> provide a way to renice a backend" ... but in point of fact it's
> somewhere between useless and counterproductive for most query loads.
> The "useless" part comes in because nice only affects CPU priority not
> I/O priority, but I/O load is the thing that counts for most database
> applications. The "counterproductive" part comes in because of an
> effect called priority inversion. The niced-down process may be holding
> a lock that is wanted by some higher-priority process --- but the kernel
> scheduler knows nothing of that, and will leave the niced-down process
> at the bottom of the queue, and thus the high-priority process is
> effectively stuck at the bottom too.

Without change priority doesn't means we are immune to a "priority inversion",
for example the way semaphore are implemented in Linux doesn't prevent you
to be bitten, at least IIRC the Linux kernel doesn't trace chain locks...
however I'd ve worried about "priority inversion" if I have hard deadline,
have "hard deadline" and "database" in the same sentence is like put
"windows" and "security" in the same sentence too...

I feel that renice a backend will not kill your system.

Regards
Gaetano Mendola