Re: [PERFORM] 7.3.2 pg_restore very slow
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 7.3.2 pg_restore very slow
Hi Richard,Thank you very muchfor 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 tryWe 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, andtry the restore process again. I would appreciate any other suggestions/advice in this regard.Thanks, SaranyaRichard 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 missingThen, 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 HuxtonArchonet Ltd See the all-new, redesigned Yahoo.com. Check it out.
Re: [PERFORM] 7.3.2 pg_restore very slow
Hi All,I tried to set shared_buffers= 1, turned off fsyncand 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 memorysegment exceeded your kernel's SHMMAX parameter. You can eitherreduce the request size or reconfigure the kernel with larger SHMMAX.To reduce the request size (currently 85450752 bytes), reducePostgreSQL's shared_buffers parameter (currently 1) and/orits max_connections parameter (currently 128). If the request size is already small, it's possible that it is less thanyour kernel's SHMMIN parameter, in which case raising the request size orreconfiguring 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 missingThen, 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 HuxtonArchonet Ltd Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail Beta.
