On Thu, Jun 08, 2006 at 09:13:33AM -0700, Joshua D. Drake wrote: > Tom Lane wrote: > >"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > >>I was recently involved in a project where we had to decrease the > >>checkpoint_timeout . The problem was, that the database was performing > >>so many transactions that if we waiting for 5 minutes, checkpoint would > >>take entirely too long. > > > >Seems like the correct fix for that is to make the bgwriter more > >aggressive. Narrowing the checkpoint spacing is a pretty horrid answer > >because of the resulting increase in full-page-image WAL traffic. > > Well we did that as well. Here are the basic symptons: > > During normal processing which contained about 250 connections > everything was fine. A checkpoint would start and connections would > start piling up, sometimes breaking 1000. > > We narrowed that down to users having to wait longer for query execution > so instead of just reusing connections new connections had to be > initiated because the existing connections were busy. > > We tried many different parameters, and bgwriter did significantly help > but the only "solution" was to make checkpoints happen at a much more > aggressive time frame. > > Modify bgwriters settings and the checkpoint actually increased our > velocity by about 70% by the time we were done. Bgwriter was definitely > the largest chunk of that although other parameters combined outweighed > it (effective_cache, shared_buffers etc...).
Did you try increasing the checkpoint interval, in the hopes that it would allow the bgwritter enough extra time to get everything pushed out? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
