From: | Saranya Sivakumar <sarlavk(at)yahoo(dot)com> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsqlperform <pgsql-performance(at)postgresql(dot)org>, pgsqlnovice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: [PERFORM] 7.3.2 pg_restore very slow |
Date: | 2006-08-07 15:35:02 |
Message-ID: | 20060807153502.25587.qmail@web51310.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice pgsql-performance |
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(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
---------------------------------
See the all-new, redesigned Yahoo.com. Check it out.
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2006-08-07 15:48:11 | Re: Did I wipe off my database -- please help - urgent |
Previous Message | Srinivas Iyyer | 2006-08-07 15:19:00 | Did I wipe off my database -- please help - urgent |
From | Date | Subject | |
---|---|---|---|
Next Message | Donald C. Sumbry ][ | 2006-08-07 15:51:09 | Re: High Context-Switches on Linux 8.1.4 Server |
Previous Message | Arjen van der Meijden | 2006-08-07 13:45:08 | Re: PostgreSQL scalability on Sun UltraSparc T1 |