X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0F9E49FB1C3 for ; Tue, 1 Aug 2006 16:52:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 34199-02 for ; Tue, 1 Aug 2006 16:52:33 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.198]) by postgresql.org (Postfix) with ESMTP id DD7E99FB1BC for ; Tue, 1 Aug 2006 16:52:33 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id t13so602473wxc for ; Tue, 01 Aug 2006 12:52:33 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=N7s5r7mmKBqO4/Dn9JfcfGljVBoX6ECmyuMpvXMHp6gh7TVcdvZploNvdYstGicBV+i9ILHEAyPurezjTKGokBL9wLwoQjNCNuijAk8NQqg6kE3cdWwmmSIuUv+6Dh1BKJDF2dcDVrYV15UlL1dZcW7bdAQtyr4NeQ0rYoVLc/o= Received: by 10.78.160.2 with SMTP id i2mr2811hue; Tue, 01 Aug 2006 12:52:32 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Tue, 1 Aug 2006 12:52:32 -0700 (PDT) Message-ID: Date: Tue, 1 Aug 2006 14:52:32 -0500 From: "Aaron Bono" To: "Scott Marlowe" Subject: Re: Controlling CPU Usage in PostgreSQL Cc: "Thomas Pundt" , pgsql-admin@postgresql.org In-Reply-To: <1154460151.7882.7.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_74771_17508623.1154461952084" References: <200607311101.36629.mlists@rp-online.de> <1154460151.7882.7.camel@state.g2switchworks.com> X-Google-Sender-Auth: 163bc95c43ec3453 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/17 X-Sequence-Number: 22561 ------=_Part_74771_17508623.1154461952084 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/1/06, Scott Marlowe wrote: > > On Tue, 2006-08-01 at 13:59, Aaron Bono wrote: > > On 7/31/06, Thomas Pundt 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 ================================================================== ------=_Part_74771_17508623.1154461952084 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
================================================================== ------=_Part_74771_17508623.1154461952084--