Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id BC7632E021D for ; Thu, 17 Jan 2008 05:16:47 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 74798-03 for ; Thu, 17 Jan 2008 05:16:38 -0400 (AST) Received: from web52302.mail.re2.yahoo.com (web52302.mail.re2.yahoo.com [206.190.48.145]) by postgresql.org (Postfix) with SMTP id 48D182E004A for ; Thu, 17 Jan 2008 05:16:42 -0400 (AST) Received: (qmail 85710 invoked by uid 60001); 17 Jan 2008 09:16:40 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.au; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=uMiwF4DnnLuw6jNDeKgJ13vg8YAdbNH3+T7JCb4Fqlk+zGyuiSfnhUG0H3MxDnQb2x/q795I0LZgfLo5bMYwvYbCNfGe4BiT6iFfW8VEHFosIUYKji8V7HTqIFF+9kQowD5wA6vRrMs2yNDYqCrKRC91ttXtdEjlF3h0zrr9mU0=; X-YMail-OSG: zxVC.WoVM1kHe1tb6DzcxHTSHNAHPHlyy00C.y0zbeyjeIWuEuPgDqGrW8nbG0NINdg5RnVofywnIv50RcROpXymxg_8jE.l0gp8Cn8.STejNGjTZJ6na9o8fw6J1HMk_BQTv6TuiPoPD7g- Received: from [202.156.9.2] by web52302.mail.re2.yahoo.com via HTTP; Thu, 17 Jan 2008 01:16:40 PST X-Mailer: YahooMailRC/818.31 YahooMailWebService/0.7.160 Date: Thu, 17 Jan 2008 01:16:40 -0800 (PST) From: Brian Herlihy Subject: Re: strange pauses To: Postgresql Performance MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Message-ID: <913219.85206.qm@web52302.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/137 X-Sequence-Number: 28376 We had the same situation, and did two things=0A=0A1. Reduce checkpoint ti= meout=0A2. Reduce quantity of data going into database (nice if it's possi= ble!)=0A=0A1 alone wasn't enough to eliminate the delays, but it did make e= ach delay small enough that the user interface was only minimally affected.= Previously, the delays were causing timeouts in the user interface.=0A=0A= Our symptoms were that the queries finishing "at the same time" were appear= ing in clusters every 5 minutes + some seconds, which happens to be the che= ckpoint timeout. Seems a new checkpoint timeout is started only after the = checkpoint is complete, hence 5 minute plus, rather than exactly 5 minutes.= =0A=0ABrian=0A =0A=0A----- Original Message ----=0AFrom: Adrian Moisey =0A=0AHi=0A=0A>> Search this list for references t= o "checkpoints". If you run =0A>> vmstat/iostat for a bit you should see bu= rsts of disk activity at =0A>> those times.=0A> =0A> The most straightforwa= rd way to prove or disprove that the slow=0A queries =0A> line up with chec= kpoints is to set to checkpoint_warning to a high=0A value =0A> (3600 shoul= d work), which should log every checkpoint, and then see=0A if =0A> they sh= ow up at the same time in the logs.=0A=0AYou guys were spot on. During the= se pauses the IO goes up high.=0A=0AI've got the following set:=0Acheckpoin= t_timeout =3D 5min=0Acheckpoint_warning =3D 3600s=0A=0Alog_min_messages =3D= info=0A=0ABut I see nothing in the logs about checkpoints=0A=0A-- =0AAdria= n Moisey=0ASystem Administrator | CareerJunction | Your Future Starts Here.= =0AWeb: www.careerjunction.co.za | Email: adrian@careerjunction.co.za=0APho= ne: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842=0A=0A-= --------------------------(end of=0A broadcast)---------------------------= =0ATIP 1: if posting/reading through Usenet, please send an appropriate=0A = subscribe-nomail command to majordomo@postgresql.org so that=0A your= =0A message can get through to the mailing list cleanly=0A=0A=0A