By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

Change query priority

P: n/a
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
Share this Question
Share on Google+
9 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.