pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Controlling CPU Usage in PostgreSQL
8+ messages / 5 participants
[nested] [flat]

* Controlling CPU Usage in PostgreSQL
@ 2006-07-31 08:06  Aaron Bono <postgresql@aranya.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Aaron Bono @ 2006-07-31 08:06 UTC (permalink / raw)
  To: pgsql-admin

I have a nightly process that runs in production to refresh reports.  This
process runs fine but takes a significant portion of the CPU for an hour or
so.  I was wanting to limit this particular process so it does not consume
the full CPU while running (unless nothing else needs the CPU cycles
anyway).

Is there a way I can tell PostgreSQL to give session a low priority so even
if it does take the full CPU, it only does so if it would otherwise be
idle?  This process runs a series of functions so if there is a command I
can put at the beginning of each function, that would be ideal.  I would
rather do it after logging into PostgreSQL and not by limiting the process
at the OS level though that may be an option if there is nothing better.

Of course, if there are other ways to approach this, I am quite open to
alternatives.

Thanks,
Aaron

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Controlling CPU Usage in PostgreSQL
@ 2006-07-31 09:01  Thomas Pundt <mlists@rp-online.de>
  parent: Aaron Bono <postgresql@aranya.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Thomas Pundt @ 2006-07-31 09:01 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org, "Aaron Bono" <postgresql@aranya.com>

On Monday 31 July 2006 10:06, Aaron Bono wrote:
| Is there a way I can tell PostgreSQL to give session a low priority so even
| if it does take the full CPU, it only does so if it would otherwise be
| idle?

the "nice" command might do what you want...

Ciao,
Thomas

-- 
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Controlling CPU Usage in PostgreSQL
@ 2006-08-01 18:59  Aaron Bono <postgresql@aranya.com>
  parent: Thomas Pundt <mlists@rp-online.de>
  0 siblings, 2 replies; 8+ messages in thread

From: Aaron Bono @ 2006-08-01 18:59 UTC (permalink / raw)
  To: Thomas Pundt <thomas.pundt@rp-online.de>; +Cc: pgsql-admin

On 7/31/06, Thomas Pundt <mlists@rp-online.de> wrote:
>
> On Monday 31 July 2006 10:06, Aaron Bono wrote:
> | Is there a way I can tell PostgreSQL to give session a low priority so
> even
> | if it does take the full CPU, it only does so if it would otherwise be
> | idle?
>
> the "nice" command might do what you want...



OK, so I tried:

su - postgres -c "nice -n 19 psql my_db"

The problem is, the psql command has a nice value of 19 but the PostgreSQL
server process that psql has connected to is running with a nice value of
0.  My assumption is that, if I then run my functions, psql will get low
priority but it is the server process that is running with normal priority
that will still use up all the CPU.

Bottom line, I am skeptical if this will really achieve my goal - to have
the functions run with low priority.

Is there a way to tell PostgreSQL to change the nice value of a particular
connection?  I guess I could use renice but that means a lot of shell
scripting to determine what PID to change - I am not a shell script expert
and would rather not pursue that option.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Controlling CPU Usage in PostgreSQL
@ 2006-08-01 19:22  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: Aaron Bono <postgresql@aranya.com>
  1 sibling, 2 replies; 8+ messages in thread

From: Scott Marlowe @ 2006-08-01 19:22 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: Thomas Pundt <thomas.pundt@rp-online.de>; pgsql-admin

On Tue, 2006-08-01 at 13:59, Aaron Bono wrote:
> On 7/31/06, Thomas Pundt <mlists@rp-online.de> wrote:
>         On Monday 31 July 2006 10:06, Aaron Bono wrote:
>         | Is there a way I can tell PostgreSQL to give session a low
>         priority so even
>         | if it does take the full CPU, it only does so if it would
>         otherwise be
>         | idle?
>         
>         the "nice" command might do what you want...
> 
>  
> OK, so I tried:
> 
> su - postgres -c "nice -n 19 psql my_db"
> 
> The problem is, the psql command has a nice value of 19 but the
> PostgreSQL server process that psql has connected to is running with a
> nice value of 0.  My assumption is that, if I then run my functions,
> psql will get low priority but it is the server process that is
> running with normal priority that will still use up all the CPU. 
> 
> Bottom line, I am skeptical if this will really achieve my goal - to
> have the functions run with low priority.
> 
> Is there a way to tell PostgreSQL to change the nice value of a
> particular connection?  I guess I could use renice but that means a
> lot of shell scripting to determine what PID to change - I am not a
> shell script expert and would rather not pursue that option. 

This is one of those things that only seems like a good idea at the
time.

While nice-ing the whole of a postgresql server is doable, nice-ing
individual connections is no advisable.

You can reach a bad state caused by "priority inversion".  Search the
lists (not just admin) for that phrase and you should find out what I
mean.  This should probably be a FAQ, as it shows up every few months.



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Controlling CPU Usage in PostgreSQL
@ 2006-08-01 19:43  Joshua D. Drake <jd@commandprompt.com>
  parent: Aaron Bono <postgresql@aranya.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Joshua D. Drake @ 2006-08-01 19:43 UTC (permalink / raw)
  To: Aaron Bono <postgresql@aranya.com>; +Cc: Thomas Pundt <thomas.pundt@rp-online.de>; pgsql-admin


> Bottom line, I am skeptical if this will really achieve my goal - to 
> have the functions run with low priority.
> 
> Is there a way to tell PostgreSQL to change the nice value of a 
> particular connection?  I guess I could use renice but that means a lot 
> of shell scripting to determine what PID to change - I am not a shell 
> script expert and would rather not pursue that option.

You can't really do what you want, and I am skeptical that it would be a 
good idea anyway. Honestly it sounds like you have an implementation 
problem.

Joshua D. Drake

-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Controlling CPU Usage in PostgreSQL
@ 2006-08-01 19:52  Aaron Bono <postgresql@aranya.com>
  parent: Scott Marlowe <smarlowe@g2switchworks.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Aaron Bono @ 2006-08-01 19:52 UTC (permalink / raw)
  To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Thomas Pundt <thomas.pundt@rp-online.de>; pgsql-admin

On 8/1/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
>
> On Tue, 2006-08-01 at 13:59, Aaron Bono wrote:
> > On 7/31/06, Thomas Pundt <mlists@rp-online.de> wrote:
> >         On Monday 31 July 2006 10:06, Aaron Bono wrote:
> >         | Is there a way I can tell PostgreSQL to give session a low
> >         priority so even
> >         | if it does take the full CPU, it only does so if it would
> >         otherwise be
> >         | idle?
> >
> >         the "nice" command might do what you want...
> >
> >
> > OK, so I tried:
> >
> > su - postgres -c "nice -n 19 psql my_db"
> >
> > The problem is, the psql command has a nice value of 19 but the
> > PostgreSQL server process that psql has connected to is running with a
> > nice value of 0.  My assumption is that, if I then run my functions,
> > psql will get low priority but it is the server process that is
> > running with normal priority that will still use up all the CPU.
> >
> > Bottom line, I am skeptical if this will really achieve my goal - to
> > have the functions run with low priority.
> >
> > Is there a way to tell PostgreSQL to change the nice value of a
> > particular connection?  I guess I could use renice but that means a
> > lot of shell scripting to determine what PID to change - I am not a
> > shell script expert and would rather not pursue that option.
>
> This is one of those things that only seems like a good idea at the
> time.
>
> While nice-ing the whole of a postgresql server is doable, nice-ing
> individual connections is no advisable.
>
> You can reach a bad state caused by "priority inversion".  Search the
> lists (not just admin) for that phrase and you should find out what I
> mean.  This should probably be a FAQ, as it shows up every few months.
>

I am guessing my best bet is to wait until performance is becomming an issue
(or just before) and then create a data warehouse on a separate server.
That would be much easier to implement and would isolate the reporting from
production.

Thanks for the input.

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Controlling CPU Usage in PostgreSQL
@ 2006-08-01 21:56  Chris Mair <list@1006.org>
  parent: Scott Marlowe <smarlowe@g2switchworks.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Chris Mair @ 2006-08-01 21:56 UTC (permalink / raw)
  To: pgsql-admin

On Tue, 2006-08-01 at 14:22 -0500, Scott Marlowe wrote:
> On Tue, 2006-08-01 at 13:59, Aaron Bono wrote:
> > On 7/31/06, Thomas Pundt <mlists@rp-online.de> wrote:
> >         On Monday 31 July 2006 10:06, Aaron Bono wrote:
> >         | Is there a way I can tell PostgreSQL to give session a low
> >         priority so even
> >         | if it does take the full CPU, it only does so if it would
> >         otherwise be
> >         | idle?
> >         
> >         the "nice" command might do what you want...
> > 
> >  
> > OK, so I tried:
> > 
> > su - postgres -c "nice -n 19 psql my_db"
> > 
> > The problem is, the psql command has a nice value of 19 but the
> > PostgreSQL server process that psql has connected to is running with a
> > nice value of 0.  My assumption is that, if I then run my functions,
> > psql will get low priority but it is the server process that is
> > running with normal priority that will still use up all the CPU. 
> > 
> > Bottom line, I am skeptical if this will really achieve my goal - to
> > have the functions run with low priority.
> > 
> > Is there a way to tell PostgreSQL to change the nice value of a
> > particular connection?  I guess I could use renice but that means a
> > lot of shell scripting to determine what PID to change - I am not a
> > shell script expert and would rather not pursue that option. 
> 
> This is one of those things that only seems like a good idea at the
> time.
> 
> While nice-ing the whole of a postgresql server is doable, nice-ing
> individual connections is no advisable.
> 
> You can reach a bad state caused by "priority inversion".  Search the
> lists (not just admin) for that phrase and you should find out what I
> mean.  This should probably be a FAQ, as it shows up every few months.

I started going down that same road in May... here's the thread from
then explaining why it's a bad idea after all:
http://archives.postgresql.org/pgsql-performance/2006-05/msg00462.php


Bye, Chris.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Controlling CPU Usage in PostgreSQL
@ 2006-08-02 17:43  Aaron Bono <postgresql@aranya.com>
  parent: Chris Mair <list@1006.org>
  0 siblings, 0 replies; 8+ messages in thread

From: Aaron Bono @ 2006-08-02 17:43 UTC (permalink / raw)
  To: Chris Mair <list@1006.org>; +Cc: pgsql-admin

On 8/1/06, Chris Mair <list@1006.org> wrote:
>
> On Tue, 2006-08-01 at 14:22 -0500, Scott Marlowe wrote:
> > On Tue, 2006-08-01 at 13:59, Aaron Bono wrote:
> > > Is there a way to tell PostgreSQL to change the nice value of a
> > > particular connection?  I guess I could use renice but that means a
> > > lot of shell scripting to determine what PID to change - I am not a
> > > shell script expert and would rather not pursue that option.
> >
> > This is one of those things that only seems like a good idea at the
> > time.
> >
> > While nice-ing the whole of a postgresql server is doable, nice-ing
> > individual connections is no advisable.
> >
> > You can reach a bad state caused by "priority inversion".  Search the
> > lists (not just admin) for that phrase and you should find out what I
> > mean.  This should probably be a FAQ, as it shows up every few months.
>
> I started going down that same road in May... here's the thread from
> then explaining why it's a bad idea after all:
> http://archives.postgresql.org/pgsql-performance/2006-05/msg00462.php



I didn't really have my heart set on doing this, just was wondering if I
could and how.

This has been a good eye opener though.  It will definitely have an impact
on my future design of both the database and multi-threaded applications.

Thanks guys!

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2006-08-02 17:43 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-31 08:06 Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-07-31 09:01 ` Thomas Pundt <mlists@rp-online.de>
2006-08-01 18:59   ` Aaron Bono <postgresql@aranya.com>
2006-08-01 19:22     ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-08-01 19:52       ` Aaron Bono <postgresql@aranya.com>
2006-08-01 21:56       ` Chris Mair <list@1006.org>
2006-08-02 17:43         ` Aaron Bono <postgresql@aranya.com>
2006-08-01 19:43     ` Joshua D. Drake <jd@commandprompt.com>

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