Postgres Pro
Downloads
Home   >   mailing lists

Thread: Config settings for large restore

Config settings for large restore

From
Erik Jones
Date:
Hi,

I'm just wondering what is considered the general wisdom on config
setting for large pg_restore runs.  I know to increase
maintenance_work_mem and turn off autovacuum and stats collection.
Shoule should checkpoint_segments and checkpoint_timeout be
increased?  Would twiddling shared_buffers help?  What about the
bgwriter (I'm thinking this should just follow the checkpoint and
shared_buffers settings)?  I realize that the actual setting will be
hardware dependent, I'm just looking for general rules of thumb for
what to tweak wrt increased restore speed/performance.


Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Re: Config settings for large restore

From
Tomasz Ostrowski
Date:
On Tue, 27 Nov 2007, Erik Jones wrote:

> I'm just wondering what is considered the general wisdom on config setting
> for large pg_restore runs.

I think the first thing you can do is to "fsync=off" temporarily. But
do remember to turn this back on when you're done restoring.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

Re: Config settings for large restore

From
Vivek Khera
Date:
On Nov 27, 2007, at 3:30 PM, Erik Jones wrote:

> I'm just wondering what is considered the general wisdom on config
> setting for large pg_restore runs.  I know to increase
> maintenance_work_mem and turn off autovacuum and stats collection.
> Shoule should checkpoint_segments and checkpoint_timeout be
> increased?  Would twiddling shared_buffers help?  What about

At least with 8.0 testing I did a while back, I found that bumping
checkpoint segments was the biggest benefit.  I use 256 segments as a
matter of course now, even for normal operations.



=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                MailerMailer, LLC     Rockville, MD
http://www.MailerMailer.com/                     +1-301-869-4449 x806

Re: Config settings for large restore

From
Erik Jones
Date:
Thanks, we're at 128 now but I'll see how bumping that up goes.

On Nov 28, 2007, at 9:46 AM, Vivek Khera wrote:

>
> On Nov 27, 2007, at 3:30 PM, Erik Jones wrote:
>
>> I'm just wondering what is considered the general wisdom on config
>> setting for large pg_restore runs.  I know to increase
>> maintenance_work_mem and turn off autovacuum and stats
>> collection.  Shoule should checkpoint_segments and
>> checkpoint_timeout be increased?  Would twiddling shared_buffers
>> help?  What about
>
> At least with 8.0 testing I did a while back, I found that bumping
> checkpoint segments was the biggest benefit.  I use 256 segments as
> a matter of course now, even for normal operations.
>
>
>
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D.                MailerMailer, LLC     Rockville, MD
> http://www.MailerMailer.com/                     +1-301-869-4449 x806
>
>

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com