Обсуждение: Controlling CPU Usage in PostgreSQL
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
==================================================================
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/ ----
On 7/31/06, Thomas Pundt <mlists@rp-online.de> wrote:
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
==================================================================
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...
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
==================================================================
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.
> 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/
On 8/1/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
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 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-performance/2006-05/msg00462.php Bye, Chris.
On 8/1/06, Chris Mair <list@1006.org> wrote:
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
==================================================================
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
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
==================================================================