X-Original-To: pgsql-novice-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A33C19FB225 for ; Mon, 7 Aug 2006 12:35:12 -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 11530-01 for ; Mon, 7 Aug 2006 12:35:04 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web51310.mail.yahoo.com (web51310.mail.yahoo.com [206.190.38.176]) by postgresql.org (Postfix) with SMTP id 4B5BA9FB25F for ; Mon, 7 Aug 2006 12:35:03 -0300 (ADT) Received: (qmail 25589 invoked by uid 60001); 7 Aug 2006 15:35:02 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=lQa3DeOg8slDTVeKI7ILnLaUMdfxRezxAerBNhmVo+xHD1pSILyxkkk0pxvvtPT8Xh5kRiAIy+4HxKaqVpiHPsHGd3Mu+yJmFchPvOs2UtTVZYUx9ATZxwKYTPyzrVVMq3jqoGTxyT8ETTu24+lr4IFrIZr7j3ninOnKLJOrSns= ; Message-ID: <20060807153502.25587.qmail@web51310.mail.yahoo.com> Received: from [216.199.70.171] by web51310.mail.yahoo.com via HTTP; Mon, 07 Aug 2006 08:35:02 PDT Date: Mon, 7 Aug 2006 08:35:02 -0700 (PDT) From: Saranya Sivakumar Subject: Re: [PERFORM] 7.3.2 pg_restore very slow To: Richard Huxton Cc: pgsqlperform , pgsqlnovice In-Reply-To: <44D70E26.9020804@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-140695556-1154964902=:24751" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.356 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: *** X-Archive-Number: 200608/49 X-Sequence-Number: 17264 --0-140695556-1154964902=:24751 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Richard, Thank you very much for the suggestions. As I said, we are stuck with 7.3.2 version for now. We have a Upgrade Project in place, but this backup is something we have to do immediately (we do not have enough time to test our application with 7.3.15 :( ) The checkpoint segments occur every 1.15 minutes with the default setting. I tried tuning some parameters in the conf file, which took 4.5 hours for the restore. sort_mem = 40960 shared_buffers = 3000 #checkpoint_segments = 3 (default) #fsync = true --I will disable this and try We can afford to have a downtime of only 1 to 1.5 hours. I am going to increase the shared_buffers, sort_mem and disable fysnc as suggested by you, and try the restore process again. I would appreciate any other suggestions/advice in this regard. Thanks, Saranya Richard Huxton wrote: Saranya Sivakumar wrote: > Hi All, > > I am trying to back up a full copy of one of our databases (14G) and > restore it on another server. Both databases run 7.3.2 version. > Though the restore completed successfully, it took 9 hours for the > process to complete. The destination server runs Fedora Core 3 with > 512 MB RAM and has 1 processor. I have also deferred referential > intergrity checks during the restore. I tried to tune some parameters > in the config file, but it still takes 9 hours. Firstly, you should upgrade to the most recent version of 7.3.x (7.3.15) - that's a *lot* of bug-fixes you are missing Then, I would temporarily disable fsync and increase sort_mem and checkpoint_segments. What you're trying to do is make a single process run as fast as possible, so allow it to grab more resources than you normally would. -- Richard Huxton Archonet Ltd --------------------------------- See the all-new, redesigned Yahoo.com. Check it out. --0-140695556-1154964902=:24751 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi Richard,
 
Thank you very much for the suggestions. As I said, we are stuck with 7.3.2 version for now. We have a Upgrade Project in place, but this backup is something we have to do immediately (we do not have enough time to test our application with 7.3.15 :( )
 
The checkpoint segments occur every 1.15 minutes with the default setting.
I tried tuning some parameters in the conf file, which took 4.5 hours for the restore.
 
sort_mem = 40960
shared_buffers = 3000
#checkpoint_segments = 3  (default)
#fsync = true    --I will disable this and try
 
We can afford to have a downtime of only 1 to 1.5 hours.
I am going to increase the shared_buffers, sort_mem and disable fysnc as suggested by you, and try the restore process again.
 
I would appreciate any other suggestions/advice in this regard.
 
Thanks,
Saranya
 


Richard Huxton <dev@archonet.com> wrote:
Saranya Sivakumar wrote:
> Hi All,
>
> I am trying to back up a full copy of one of our databases (14G) and
> restore it on another server. Both databases run 7.3.2 version.
> Though the restore completed successfully, it took 9 hours for the
> process to complete. The destination server runs Fedora Core 3 with
> 512 MB RAM and has 1 processor. I have also deferred referential
> intergrity checks during the restore. I tried to tune some parameters
> in the config file, but it still takes 9 hours.

Firstly, you should upgrade to the most recent version of 7.3.x (7.3.15)
- that's a *lot* of bug-fixes you are missing

Then, I would temporarily disable fsync and increase sort_mem and
checkpoint_segments. What you're trying to do is make a single process
run as fast as possible, so allow it to grab more resources than you
normally would.

--
Richard Huxton
Archonet Ltd

See the all-new, redesigned Yahoo.com. Check it out. --0-140695556-1154964902=:24751--