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 1581A9FA5C8 for ; Wed, 14 Jun 2006 14:04:44 -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 39235-08 for ; Wed, 14 Jun 2006 14:04:33 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id 197A49F9FE3 for ; Wed, 14 Jun 2006 14:04:33 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so417330ugf for ; Wed, 14 Jun 2006 10:04:31 -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=guOtrmjtBq55rLt5jwZUx4DFlxBLva8UQ6NERqtqKNzqLw2kuH4aVaIkoVgeCI/i60pGzfa7jA+05kG9OZhOQKnoJ32Af4QdlJtOz/KN6yXKRQjqtgtu6HZ+vK58VU3vlDV/YwewlIF7Vy2PYyXiy9OFch9oF21eN1497alfxzQ= Received: by 10.66.250.17 with SMTP id x17mr857052ugh; Wed, 14 Jun 2006 10:04:31 -0700 (PDT) Received: by 10.66.216.11 with HTTP; Wed, 14 Jun 2006 10:04:31 -0700 (PDT) Message-ID: Date: Wed, 14 Jun 2006 13:04:31 -0400 From: "John Vincent" To: "Scott Marlowe" Subject: Re: Performance of pg_dump on PGSQL 8.0 Cc: "PGSQL Performance" In-Reply-To: <1150303450.26538.9.camel@state.g2switchworks.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7780_8892426.1150304671808" References: <44902165.7020502@lusis.org> <1150303450.26538.9.camel@state.g2switchworks.com> X-Google-Sender-Auth: 5c8f9fa2b394e391 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/261 X-Sequence-Number: 19618 ------=_Part_7780_8892426.1150304671808 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/14/06, Scott Marlowe wrote: > > On Wed, 2006-06-14 at 09:47, John E. Vincent wrote: > > -- this is the third time I've tried sending this and I never saw it get > > through to the list. Sorry if multiple copies show up. > > > > Hi all, > > BUNCHES SNIPPED > > > work_mem = 1048576 ( I know this is high but you should see some of our > > sorts and aggregates) > > Ummm. That's REALLY high. You might want to consider lowering the > global value here, and then crank it up on a case by case basis, like > during nighttime report generation. Just one or two queries could > theoretically run your machine out of memory right now. Just put a "set > work_mem=1000000" in your script before the big query runs. I know it is but that's what we need for some of our queries. Our ETL tool (informatica) and BI tool (actuate) won't let us set those things as part of our jobs. We need it for those purposes. We have some really nasty queries that will be fixed in our new server. E.G. we have a table called loan_account_agg_fact that has 200+ million rows and it contains every possible combination of late status for a customer account (i.e. 1 day late, 2 day late, 3 day late) so it gets inserted for new customers but updated for existing records as part of our warehouse load. Part of the new layout is combining late ranges so instead of number of days we have a range of days (i.e. 1-15,16-30....). Even with work_mem that large, the load of that loan_account_agg_fact table creates over 3GB of temp tables! > That's exactly what we do. We just do a normal backup, and have a > script that gzips anything in the backup directory that doesn't end in > .gz... If you've got space to burn, as you say, then use it at least a > few days to see how it affects backup speeds. > > Seeing as how you're CPU bound, most likely the problem is just the > compressed backup. > I'm starting to think the same thing. I'll see how this COPY I'm doing of the single largest table does right now and make some judgement based on that. -- John E. Vincent ------=_Part_7780_8892426.1150304671808 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 6/14/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Wed, 2006-06-14 at 09:47, John E. Vincent wrote:
> -- this is the third time I've tried sending this and I never saw it get
> through to the list. Sorry if multiple copies show up.
>
> Hi all,

BUNCHES SNIPPED

> work_mem = 1048576 ( I know this is high but you should see some of our
> sorts and aggregates)

Ummm.  That's REALLY high.  You might want to consider lowering the
global value here, and then crank it up on a case by case basis, like
during nighttime report generation.  Just one or two queries could
theoretically run your machine out of memory right now.  Just put a "set
work_mem=1000000" in your script before the big query runs.


I know it is but that's what we need for some of our queries. Our ETL tool (informatica) and BI tool (actuate) won't let us set those things as part of our jobs. We need it for those purposes. We have some really nasty queries that will be fixed in our new server.

E.G. we have a table called loan_account_agg_fact that has 200+ million rows and it contains every possible combination of late status for a customer account (i.e. 1 day late, 2 day late, 3 day late) so it gets inserted for new customers but updated for existing records as part of our warehouse load. Part of the new layout is combining late ranges so instead of number of days we have a range of days ( i.e. 1-15,16-30....). Even with work_mem that large, the load of that loan_account_agg_fact table creates over 3GB of temp tables!


That's exactly what we do.  We just do a normal backup, and have a
script that gzips anything in the backup directory that doesn't end in
.gz...  If you've got space to burn, as you say, then use it at least a
few days to see how it affects backup speeds.

Seeing as how you're CPU bound, most likely the problem is just the
compressed backup.

I'm starting to think the same thing. I'll see how this COPY I'm doing of the single largest table does right now and make some judgement based on that.

--
John E. Vincent
------=_Part_7780_8892426.1150304671808--