X-Original-To: pgsql-novice-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id DDC2B9FB35C for ; Mon, 7 Aug 2006 13:08:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 06700-03 for ; Mon, 7 Aug 2006 16:08:31 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web51309.mail.yahoo.com (web51309.mail.yahoo.com [206.190.38.175]) by postgresql.org (Postfix) with SMTP id 8D2BF9FB35B for ; Mon, 7 Aug 2006 13:08:31 -0300 (ADT) Received: (qmail 21243 invoked by uid 60001); 7 Aug 2006 16:08:30 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=IhGTEs0E87bIZ/ffmDmbzsY2iOLSej3UpZoB1KiW9SHn6k8HN6vUppbuUzGTC8iqdweqyIfP5C1cAxH1itr4gHwlezJS7CGkiMc9jytXH6vQbnnUXlHRrMj13JIru891jvfnTpuf9lq67EMe7L1oFmDiBJjyo7bpjFNLk1qCz0w= ; Message-ID: <20060807160830.21241.qmail@web51309.mail.yahoo.com> Received: from [216.199.70.171] by web51309.mail.yahoo.com via HTTP; Mon, 07 Aug 2006 09:08:30 PDT Date: Mon, 7 Aug 2006 09:08:30 -0700 (PDT) From: Saranya Sivakumar Subject: Re: [PERFORM] 7.3.2 pg_restore very slow To: pgsqlperform , pgsqlnovice In-Reply-To: <44D70E26.9020804@archonet.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-412715950-1154966910=:20903" 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/52 X-Sequence-Number: 17267 --0-412715950-1154966910=:20903 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi All, I tried to set shared_buffers= 10000, turned off fsync and reload the config file. But I got the following error: IpcMemoryCreate: shmget(key=5432001, size=85450752, 03600) failed: Invalid argument This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 85450752 bytes), reduce PostgreSQL's shared_buffers parameter (currently 10000) and/or its max_connections parameter (currently 128). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The total RAM available on this machine is 512MB. I am not sure how to set these parameters SHMMAX and SHMMIN. Any help/advice would be greatly appreciated. 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 --------------------------------- Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail Beta. --0-412715950-1154966910=:20903 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi All,
 
I tried to set shared_buffers= 10000, turned off fsync and reload the config file.
But I got the following error:
 
IpcMemoryCreate: shmget(key=5432001, size=85450752, 03600) failed: Invalid argument
This error usually means that PostgreSQL's request for a shared memory
segment exceeded your kernel's SHMMAX parameter.  You can either
reduce the request size or reconfigure the kernel with larger SHMMAX.
To reduce the request size (currently 85450752 bytes), reduce
PostgreSQL's shared_buffers parameter (currently 10000) and/or
its max_connections parameter (currently 128).
If the request size is already small, it's possible that it is less than
your kernel's SHMMIN parameter, in which case raising the request size or
reconfiguring SHMMIN is called for.
The total RAM available on this machine is 512MB.
 
I am not sure how to set these parameters SHMMAX and SHMMIN.
Any help/advice would be greatly appreciated.
 
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

Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail Beta. --0-412715950-1154966910=:20903--