Thread: cpu throttling
We have a complicated stored procedure that we run frequently. It pegs one of our postmaster processes at 100% CPU utilization for a few hours. This has the unfortunate side effect of causing increased latency for our other queries. We are currently planning a fix, but because of the complicated nature of this procedure it is going to take some time to implement. I've noticed that if I renice the process that is running the query, the other postmaster processes are able to respond to our other queries in a timely fashion. My question: Is there a way I can decrease the priority of a specific query, or determine the PID of the process it is running in? I'd like to throw together a quick shell script if at all possible, as right now I have to monitor the process manually and we'll have fixed the problem long before we have the chance to implement proper database clustering. Bryan
On Thursday 02 August 2007 09:02, "Bryan Murphy" <bryan.murphy@gmail.com> wrote: > My question: Is there a way I can decrease the priority of a specific > query, or determine the PID of the process it is running in? I'd like > to throw together a quick shell script if at all possible, as right > now I have to monitor the process manually and we'll have fixed the > problem long before we have the chance to implement proper database > clustering. select procpid from pg_stat_activity where current_query like '%stored_proc%' and current_query not like '%pg_stat_activity%'; requires stats_command_string to be enabled I'm surprised your operating system doesn't automatically lower the priority of the process, though .. -- "Remember when computers were frustrating because they did exactly what you told them to? That actually seems sort of quaint now." --J.D. Baldwin
It's a 4 processor Intel xeon machine with more than enough ram. The entire database can fit in memory, and while the CPU is pegged, nothing is chewing up I/O bandwidth, and nothing is getting swapped out of RAM. I'm running Debian stable with only a few tweaks to the kernel's memory settings. As far as I'm aware, I have not changed anything that would impact scheduling. Other queries do respond, but it's more like every couple of seconds one query which normally takes 300ms might take 8000ms. Nothing terrible, but enough that our users will notice. Bryam On 8/2/07, Alan Hodgson <ahodgson@simkin.ca> wrote: > On Thursday 02 August 2007 09:02, "Bryan Murphy" <bryan.murphy@gmail.com> > wrote: > > My question: Is there a way I can decrease the priority of a specific > > query, or determine the PID of the process it is running in? I'd like > > to throw together a quick shell script if at all possible, as right > > now I have to monitor the process manually and we'll have fixed the > > problem long before we have the chance to implement proper database > > clustering. > > select procpid from pg_stat_activity where current_query > like '%stored_proc%' and current_query not like '%pg_stat_activity%'; > > requires stats_command_string to be enabled > > I'm surprised your operating system doesn't automatically lower the priority > of the process, though .. > > -- > "Remember when computers were frustrating because they did exactly what > you told them to? That actually seems sort of quaint now." --J.D. Baldwin > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On Thu, Aug 02, 2007 at 09:14:37AM -0700, Alan Hodgson wrote: > On Thursday 02 August 2007 09:02, "Bryan Murphy" <bryan.murphy@gmail.com> > wrote: > > My question: Is there a way I can decrease the priority of a specific > > query, or determine the PID of the process it is running in? I'd like > > to throw together a quick shell script if at all possible, as right > > now I have to monitor the process manually and we'll have fixed the > > problem long before we have the chance to implement proper database > > clustering. > > select procpid from pg_stat_activity where current_query > like '%stored_proc%' and current_query not like '%pg_stat_activity%'; > > requires stats_command_string to be enabled > > I'm surprised your operating system doesn't automatically lower the priority > of the process, though .. The OS will only lower it to a certain extent. Also, make sure you understand the concept of priority inversion before going into production with this solution. -- Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)