Обсуждение: Config settings for large restore
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
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
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
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