X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8F9E99FA5C8 for ; Wed, 14 Jun 2006 17:55:14 -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 69233-08 for ; Wed, 14 Jun 2006 17:55:03 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id B16539FA5FB for ; Wed, 14 Jun 2006 17:55:03 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so533166ugf for ; Wed, 14 Jun 2006 13:55:01 -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=pLQ6szmCAh/8hvz3CslKbhPVp9vie7Bz6JR2MI1eHq4UJ72jXhllCmjDGpsRflDxrOQDwts9NDiwxcolhZufRqyny+jgDFbg5XDgn+XRFJZKSLg6kvgcsYjZBkQThq3HBlyLnssvcvnJSlghj5cSorl1TghMQrqIfiPP2t3Ivls= Received: by 10.67.100.17 with SMTP id c17mr1041081ugm; Wed, 14 Jun 2006 13:55:00 -0700 (PDT) Received: by 10.66.216.11 with HTTP; Wed, 14 Jun 2006 13:55:00 -0700 (PDT) Message-ID: Date: Wed, 14 Jun 2006 16:55:00 -0400 From: "John Vincent" To: "Scott Marlowe" Subject: Re: Performance of pg_dump on PGSQL 8.0 Cc: "PGSQL Performance" In-Reply-To: <1150315897.26538.33.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_12166_5466557.1150318500847" References: <44902165.7020502@lusis.org> <1150303450.26538.9.camel@state.g2switchworks.com> <1150315897.26538.33.camel@state.g2switchworks.com> X-Google-Sender-Auth: 39232c34f3430f1e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/271 X-Sequence-Number: 19628 ------=_Part_12166_5466557.1150318500847 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/14/06, Scott Marlowe wrote: > > > Description of "Queries gone wild" redacted. hehe. > > Yeah, I've seen those kinds of queries before too. you might be able to > limit your exposure by using alter user: > > alter user userwhoneedslotsofworkmem set work_mem=1000000; Is this applicable on 8.0? We were actually LOOKING for a governor of some sort for these queries. And something that is not explicitly stated, is that allocated up front or is that just a ceiling? and then only that user will have that big of a default. You could even > make it so that only queries that need that much log in as that user, > and all other queries log in as other folks. Just a thought. I just > get REAL nervous seeing a production machine with a work_mem set that > high. Which is actually how it's configured. We have a dedicated user connecting from Actuate. The reports developers use thier own logins when developing new reports. Only when they get published do they convert to the Actuate user. -- John E. Vincent lusis.org@gmail.com ------=_Part_12166_5466557.1150318500847 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 6/14/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:

Description of "Queries gone wild" redacted.  hehe.

Yeah, I've seen those kinds of queries before too.  you might be able to
limit your exposure by using alter user:

alter user userwhoneedslotsofworkmem set work_mem=1000000;

Is this applicable on  8.0? We were actually LOOKING for a governor of some sort for these queries.  And something that is not explicitly stated, is that allocated up front or is that just a ceiling?

and then only that user will have that big of a default.  You could even
make it so that only queries that need that much log in as that user,
and all other queries log in as other folks.  Just a thought.  I just
get REAL nervous seeing a production machine with a work_mem set that
high.

Which is actually how it's configured. We have a dedicated user connecting from  Actuate. The reports developers use thier own logins when developing new reports. Only when they get published do they convert to the Actuate user.




--
John E. Vincent
lusis.org@gmail.com ------=_Part_12166_5466557.1150318500847--