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 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

On Sun, Oct 10, 2004 at 07:12:51PM +0400, Eugeny Balakhonov wrote:
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

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
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

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

Tom Lane wrote:
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:
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

Tom Lane wrote:
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.1070704 (AT) bigfoot (DOT) com>, Gaetano Mendola wrote:

Barry S wrote:
I do.

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



Regards
Gaetano Mendola