pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcpu throttling
4+ messages / 3 participants
[nested] [flat]
* cpu throttling
@ 2007-08-02 16:02 Bryan Murphy <bryan.murphy@gmail.com>
2007-08-02 16:14 ` Re: cpu throttling Alan Hodgson <ahodgson@simkin.ca>
0 siblings, 1 reply; 4+ messages in thread
From: Bryan Murphy @ 2007-08-02 16:02 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cpu throttling
2007-08-02 16:02 cpu throttling Bryan Murphy <bryan.murphy@gmail.com>
@ 2007-08-02 16:14 ` Alan Hodgson <ahodgson@simkin.ca>
2007-08-02 18:09 ` Re: cpu throttling Bryan Murphy <bryan.murphy@gmail.com>
2007-08-03 22:17 ` Re: cpu throttling Decibel! <decibel@decibel.org>
0 siblings, 2 replies; 4+ messages in thread
From: Alan Hodgson @ 2007-08-02 16:14 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cpu throttling
2007-08-02 16:02 cpu throttling Bryan Murphy <bryan.murphy@gmail.com>
2007-08-02 16:14 ` Re: cpu throttling Alan Hodgson <ahodgson@simkin.ca>
@ 2007-08-02 18:09 ` Bryan Murphy <bryan.murphy@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Bryan Murphy @ 2007-08-02 18:09 UTC (permalink / raw)
To: Alan Hodgson <ahodgson@simkin.ca>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: cpu throttling
2007-08-02 16:02 cpu throttling Bryan Murphy <bryan.murphy@gmail.com>
2007-08-02 16:14 ` Re: cpu throttling Alan Hodgson <ahodgson@simkin.ca>
@ 2007-08-03 22:17 ` Decibel! <decibel@decibel.org>
1 sibling, 0 replies; 4+ messages in thread
From: Decibel! @ 2007-08-03 22:17 UTC (permalink / raw)
To: Alan Hodgson <ahodgson@simkin.ca>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-03 22:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-02 16:02 cpu throttling Bryan Murphy <bryan.murphy@gmail.com>
2007-08-02 16:14 ` Alan Hodgson <ahodgson@simkin.ca>
2007-08-02 18:09 ` Bryan Murphy <bryan.murphy@gmail.com>
2007-08-03 22:17 ` Decibel! <decibel@decibel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox