Re: [PERFORM] 7.3.2 pg_restore very slow

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(at)archonet(dot)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.