Обсуждение: Controlling CPU Usage in PostgreSQL

Поиск
Список
Период
Сортировка

Controlling CPU Usage in PostgreSQL

От
"Aaron Bono"
Дата:
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
==================================================================

Re: Controlling CPU Usage in PostgreSQL

От
Thomas Pundt
Дата:
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/ ----

Re: Controlling CPU Usage in PostgreSQL

От
"Aaron Bono"
Дата:
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
==================================================================

Re: Controlling CPU Usage in PostgreSQL

От
Scott Marlowe
Дата:
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.

Re: Controlling CPU Usage in PostgreSQL

От
"Joshua D. Drake"
Дата:
> 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/



Re: Controlling CPU Usage in PostgreSQL

От
"Aaron Bono"
Дата:
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
==================================================================

Re: Controlling CPU Usage in PostgreSQL

От
Chris Mair
Дата:
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.



Re: Controlling CPU Usage in PostgreSQL

От
"Aaron Bono"
Дата:
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
==================================================================