471,073 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

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
Nov 23 '05 #1
9 5840
On Sun, 10 Oct 2004 19:12:51 +0400, Eugeny Balakhonov <c0****@mail.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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2
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/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
Michael Fuhr <mi**@fuhr.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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4
On Sun, Oct 10, 2004 at 12:29:32PM -0400, Tom Lane wrote:
Michael Fuhr <mi**@fuhr.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/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #5
Tom Lane wrote:
Michael Fuhr <mi**@fuhr.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

Nov 23 '05 #6
Gaetano Mendola <me*****@bigfoot.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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #7
Tom Lane wrote:
Gaetano Mendola <me*****@bigfoot.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

Nov 23 '05 #8
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 <41**************@bigfoot.com>, Gaetano Mendola wrote:
Tom Lane wrote:
Gaetano Mendola <me*****@bigfoot.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

Nov 23 '05 #9
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

Nov 23 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Adam i Agnieszka Gasiorowski FNORD | last post: by
5 posts views Thread by scott | last post: by
8 posts views Thread by p | last post: by
2 posts views Thread by =?Utf-8?B?bXVyYWRqYW1lcw==?= | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.