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
================================================== ================

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/ ----

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> 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/



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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
================================================== ================

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...5/msg00462.php


Bye, Chris.



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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...5/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
================================================== ================