473,700 Members | 2,553 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 6099
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_arra yref("select
procpid from pg_stat_activit y 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.or g> 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 counterproducti ve 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 "counterproduct ive" 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.or g> 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 counterproducti ve 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.or g> 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 counterproducti ve 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 "counterproduct ive" 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*****@bigfoo t.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*****@bigfoo t.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*****@bigfoo t.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2240
by: Adam i Agnieszka Gasiorowski FNORD | last post by:
I need help width formulating the most effective (in terms of processing time) SQL query to count all the "new" documents in the repository, where "new" is defined as "from 00:00:01 up to 23:59:59 today". My current query does not give me satisfactory results, it creates a visible delay in rendering of the main page of one of the departments (Drugs) :8https://hyperreal.info > site, see for yourself, notice the delay
1
5029
by: Norm | last post by:
I have a program that is cpu intensive so I want to change it's priority to BelowNormal. The code functions without error, the VS Debugger shows the change but when I then go to "Windows Task Manager" and look at my process it still has a "Base Pri" of "Normal". What thing am I missing? C# Code snippet:
5
4561
by: scott | last post by:
Hello, I'm trying to find the most optimal way to perform a tricky query. I'm hoping this is some sort of standard problem that has been solved before, but I'm not finding anything too useful so far. I have a solution that works (using subqueries), but is pretty slow. Assume I have two tables: ItemID int (Primary Key) ItemSourceID int ItemUniversalKey uniqueidentifier
6
1268
by: pointBoarder | last post by:
Thanks in advance to all who read this. I've got 3 tables which were created from a txt file dumped from some old system. Header ID -- autonumber, primary OrderNum -- field I want Line
4
4453
by: active | last post by:
It appears to me that if I change the Control's client size the controls size does not change to agree with the new size. Does it work like the VB6 ScaleWidth and ScaleHeight? That is after I change the Clientsize does the display stay the same but the number of units required to reference a given point changes? Anyway, it appears to me that If I want to change to size of the control so that the client size is some value, I need to...
18
44653
by: A.M | last post by:
Hi, Is there any way to call a WSS web service method by using browser and see the XML result in browser as well? I have been told that there is query string syntax for calling webservices.
8
3165
by: p | last post by:
I'm trying to put a query into VBA code and its giving me difficulties. I would like to put the following query: SELECT tbl_Workload.Priority, tbl_Workload.Number AS Num, tbl_Workload.Name, tbl_Workload.Comment AS Comment, tbl_Workload.EmpID, tbl_Workload.TaskID, tbl_Jobs.ID FROM tbl_Workload LEFT JOIN tbl_Jobs ON tbl_Workload.Number = tbl_Jobs.Number WHERE (((tbl_Workload.EmpID)=..)) ORDER BY tbl_Workload.Priority, tbl_Workload.Number;
1
1349
by: wmpatricio | last post by:
I have a problem on how I the query be stated in this situation: Area Manager 1 Priority 9 Area Manager 2 Priority 9 Branch Manager 1 under Area Mgr1 Priority 8 Branch Manager 2 under Area Mgr1 Priority 8 Branch Manager 3 under Area Mgr2 Priority 8 Branch Manager 4 under Area Mgr2 Priority 8
2
5336
by: =?Utf-8?B?bXVyYWRqYW1lcw==?= | last post by:
Yes, sorry I tried to make it clear in the original question that I want to get the user token of the service - ie. the account the service is running under. I know services don't have user tokens - I suspect users do have tokens, hence "user token", otherwise we might call them "service tokens" or somesuch :-) If you read the question again, you migh see that the fundamental question is, *assuming there is no other approach*: The...
0
8713
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8639
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9203
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8957
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7795
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6556
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5897
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4396
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2379
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.