Re: Change query priority

Hi!

How to change query priority dynamically?

I have a web site which uses PostgreSQL as a host database and a background
program which uses this database too.

I want to change priority for database queries of this background program to
"low" and change priority for web site database queries to "high".

It is possible?

Best regards,

Eugeny

On Sun, 10 Oct 2004 19:12:51 +0400, Eugeny Balakhonov <c0ff75(at)mail(dot)ru> wrote:
> I have a web site which uses PostgreSQL as a host database and a background
> program which uses this database too.
>
> I want to change priority for database queries of this background program to
> "low" and change priority for web site database queries to "high".
>
> It is possible?

I think you could create a daemon/cron job which would get process ids of
servers and renice them, like for instance (assuming perl script):

while (1) {
my @pids = map { $_->[0] } @{$dbh->selectall_arrayref("select
procpid from pg_stat_activity where usename = 'apache'")};
system("renice -10 @pids");
sleep 300;
}

Or something like this. SIlly idea but might do the trick.

On Sun, Oct 10, 2004 at 07:12:51PM +0400, Eugeny Balakhonov wrote:
>
> I want to change priority for database queries of this background program to
> "low" and change priority for web site database queries to "high".

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.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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.

If this were an easy problem to solve we'd have offered a solution long
ago ...

regards, tom lane

On Sun, Oct 10, 2004 at 12:29:32PM -0400, 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.
>
> 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.

I wondered if that might be the case; thanks for pointing out the
reasons, which I now recall having read about before. I withdraw
the suggestion.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:
> I feel that renice a backend will not kill your system.

It won't kill the system, but it probably won't accomplish what you
hoped for, either.

regards, tom lane

Tom Lane wrote:
> Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:
>
>>I feel that renice a backend will not kill your system.
>
>
> It won't kill the system, but it probably won't accomplish what you
> hoped for, either.
>

That's true but right now renice a backend is the only way to procede
in order to *try* to "slow down" some queries

Regards
Gaetano Mendola

Thats fine, but you do understand that nice (linux) will have *no*
effect on I/O?

For any non-trivial table (that can't be held entirely in memory),
re-nice will almost certainly have no effect.

-Barry

In article <416C46C2(dot)1070704(at)bigfoot(dot)com>, Gaetano Mendola wrote:
> Tom Lane wrote:
> > Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:
> >
> >>I feel that renice a backend will not kill your system.
> >
> >
> > It won't kill the system, but it probably won't accomplish what you
> > hoped for, either.
> >
>
> That's true but right now renice a backend is the only way to procede
> in order to *try* to "slow down" some queries
>
>
>
> Regards
> Gaetano Mendola
>
>
>

Barry S wrote:
> Thats fine, but you do understand that nice (linux) will have *no*
> effect on I/O?

I do.

> For any non-trivial table (that can't be held entirely in memory),
> re-nice will almost certainly have no effect.

That's my feeling too, but at least is a try.

Regards
Gaetano Mendola