pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedControlling 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>
2006-07-31 09:01 ` Re: Controlling CPU Usage in PostgreSQL Thomas Pundt <mlists@rp-online.de>
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 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 ` Re: Controlling CPU Usage in PostgreSQL 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-07-31 08:06 Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-07-31 09:01 ` Re: Controlling CPU Usage in PostgreSQL Thomas Pundt <mlists@rp-online.de>
@ 2006-08-01 18:59 ` Aaron Bono <postgresql@aranya.com>
2006-08-01 19:22 ` Re: Controlling CPU Usage in PostgreSQL Scott Marlowe <smarlowe@g2switchworks.com>
2006-08-01 19:43 ` Re: Controlling CPU Usage in PostgreSQL Joshua D. Drake <jd@commandprompt.com>
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-07-31 08:06 Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-07-31 09:01 ` Re: Controlling CPU Usage in PostgreSQL Thomas Pundt <mlists@rp-online.de>
2006-08-01 18:59 ` Re: Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
@ 2006-08-01 19:22 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-08-01 19:52 ` Re: Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-08-01 21:56 ` Re: Controlling CPU Usage in PostgreSQL Chris Mair <list@1006.org>
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-07-31 08:06 Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-07-31 09:01 ` Re: Controlling CPU Usage in PostgreSQL Thomas Pundt <mlists@rp-online.de>
2006-08-01 18:59 ` Re: Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-08-01 19:22 ` Re: Controlling CPU Usage in PostgreSQL Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-08-01 19:52 ` Aaron Bono <postgresql@aranya.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-07-31 08:06 Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-07-31 09:01 ` Re: Controlling CPU Usage in PostgreSQL Thomas Pundt <mlists@rp-online.de>
2006-08-01 18:59 ` Re: Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-08-01 19:22 ` Re: Controlling CPU Usage in PostgreSQL Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-08-01 21:56 ` Chris Mair <list@1006.org>
2006-08-02 17:43 ` Re: Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.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-07-31 08:06 Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-07-31 09:01 ` Re: Controlling CPU Usage in PostgreSQL Thomas Pundt <mlists@rp-online.de>
2006-08-01 18:59 ` Re: Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-08-01 19:22 ` Re: Controlling CPU Usage in PostgreSQL Scott Marlowe <smarlowe@g2switchworks.com>
2006-08-01 21:56 ` Re: Controlling CPU Usage in PostgreSQL Chris Mair <list@1006.org>
@ 2006-08-02 17:43 ` Aaron Bono <postgresql@aranya.com>
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
* Re: Controlling CPU Usage in PostgreSQL
2006-07-31 08:06 Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
2006-07-31 09:01 ` Re: Controlling CPU Usage in PostgreSQL Thomas Pundt <mlists@rp-online.de>
2006-08-01 18:59 ` Re: Controlling CPU Usage in PostgreSQL Aaron Bono <postgresql@aranya.com>
@ 2006-08-01 19:43 ` Joshua D. Drake <jd@commandprompt.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
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