Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0C91A636F71 for ; Fri, 26 Feb 2010 03:04:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 31041-04 for ; Fri, 26 Feb 2010 07:04:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pw0-f46.google.com (mail-pw0-f46.google.com [209.85.160.46]) by mail.postgresql.org (Postfix) with ESMTP id D9126632733 for ; Fri, 26 Feb 2010 03:04:15 -0400 (AST) Received: by pwj8 with SMTP id 8so6282696pwj.19 for ; Thu, 25 Feb 2010 23:04:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=OM4bHuo4doxG+FHmmVR+B9Lz7U84kCDfdM37HjwmPYY=; b=bVp7kFkltbUDHhSlCnns+hszMuR+Qi8LdjGBKMydwH28IZ+LtbOwXrgvpNmwnQJIwQ LATCTQ3LNqHjOCfJyBpcFHg+MpmbFaYcwgUzbY/SLwXo2qjRlJkuAXCzHEJ/DVgpFhqJ FDXMKVrusOILV+nf+crCf8of8EUEGOzGhpseg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=GlnYBJ9p4WqPnkHh/NiS5IM9H1Qxs4buNZzNnp0B5SM0dGLKo3uBTuM1zy2PA/HfZF o/qnwJMTGMdMrsZIK+Nc3m7K3ycwekPunGiG3+2KMJD7SRMjg+klRoORZ2fi5DfP5own nSfX4sAsVPF6M1urG/xyoLH3KfnAuhIH8JtKw= MIME-Version: 1.0 Received: by 10.141.107.9 with SMTP id j9mr336680rvm.200.1267167854307; Thu, 25 Feb 2010 23:04:14 -0800 (PST) In-Reply-To: <8a547c841002252301g49d7f183ue0909929de09d20@mail.gmail.com> References: <8a547c841002252212t382cfeb4pde3e668a9d3f2f10@mail.gmail.com> <1267166541.2094.52.camel@hp-laptop2.gunduz.org> <8a547c841002252301g49d7f183ue0909929de09d20@mail.gmail.com> Date: Thu, 25 Feb 2010 23:04:13 -0800 Message-ID: <8a547c841002252304v9da8589w719e4a3aa0e6d8e0@mail.gmail.com> Subject: Re: bgwriter, checkpoints, curious (seeing delays) From: Tory M Blue To: =?ISO-8859-1?Q?Devrim_G=DCND=DCZ?= Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.682 tagged_above=-10 required=5 tests=AWL=-0.497, BAYES_40=-0.185 X-Spam-Level: X-Archive-Number: 201002/369 X-Sequence-Number: 37790 2010/2/25 Tory M Blue : > 2010/2/25 Devrim G=DCND=DCZ : >> On Thu, 2010-02-25 at 22:12 -0800, Tory M Blue wrote: >>> shared_buffers =3D 1500MB >> >> Some people tend to increase this to 2.2GB(32-bit) or 4-6 GB (64 bit), >> if needed. Please note that more shared_buffers will lead to more >> pressure on bgwriter, but it also has lots of benefits, too. >> >>> work_mem =3D 100MB >> >> This is too much. Since you have 300 connections, you will probably swap >> because of this setting, since each connection may use this much >> work_mem. The rule of the thumb is to set this to a lower general value >> (say, 1-2 MB), and set it per-query when needed. >> >>> checkpoint_segments =3D 100 >>> checkpoint_warning =3D 3600s >> >> What about checkpoint_timeout? Please note that even if >> checkpoint_segments =3D 100, if timeout value is low (say 5 mins), >> PostgreSQL will probably checkpoint every checkpoint_timeout minutes >> (unless PostgreSQL creates $checkpoint_segments xlogs before >> checkpoint_timeout value). Depending on your workload, this may not be >> intended, and it may cause spikes -- which will lead to the issues you >> complain. >> >> I'll stop here, and suggest you read this: >> >> http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm >> >> for details about this subject. As noted there, if you are running 8.3+, >> pg_stat_bgwriter will help you to tune checkpoint & bgwriter settings. >> >> -HTH. > > Checkpoint_timeout is the default and that looks like 5 mins (300 > seconds). And is obviously why I have such a discrepancy between time > reached and requested. > > Thank you sir, that's actually the page that I've spent much of my > time on this eve :) =A0I'll continue to read and check my configuration > settings. > > Tory Also since I set the log on today I have some log information regarding the checkpoints 2010-02-25 22:08:13 PST LOG: checkpoint starting: time 2010-02-25 22:10:41 PST LOG: checkpoint complete: wrote 44503 buffers (23.2%); 0 transaction log file(s) added, 0 removed, 20 recycled; write=3D148.539 s, sync=3D0.000 s, total=3D148.540 s 2010-02-25 22:13:13 PST LOG: checkpoint starting: time 2010-02-25 22:15:37 PST LOG: checkpoint complete: wrote 38091 buffers (19.8%); 0 transaction log file(s) added, 0 removed, 20 recycled; write=3D144.713 s, sync=3D0.000 s, total=3D144.714 s 2010-02-25 22:18:13 PST LOG: checkpoint starting: time 2010-02-25 22:20:42 PST LOG: checkpoint complete: wrote 38613 buffers (20.1%); 0 transaction log file(s) added, 0 removed, 19 recycled; write=3D149.870 s, sync=3D0.000 s, total=3D149.871 s 2010-02-25 22:23:13 PST LOG: checkpoint starting: time 2010-02-25 22:25:42 PST LOG: checkpoint complete: wrote 39009 buffers (20.3%); 0 transaction log file(s) added, 0 removed, 19 recycled; write=3D149.876 s, sync=3D0.000 s, total=3D149.877 s 2010-02-25 22:28:13 PST LOG: checkpoint starting: time 2010-02-25 22:30:43 PST LOG: checkpoint complete: wrote 30847 buffers (16.1%); 0 transaction log file(s) added, 0 removed, 19 recycled; write=3D150.000 s, sync=3D0.000 s, total=3D150.001 s 2010-02-25 22:33:13 PST LOG: checkpoint starting: time 2010-02-25 22:35:43 PST LOG: checkpoint complete: wrote 11917 buffers (6.2%); 0 transaction log file(s) added, 0 removed, 14 recycled; write=3D150.064 s, sync=3D0.000 s, total=3D150.065 s 2010-02-25 22:38:13 PST LOG: checkpoint starting: time 2010-02-25 22:40:43 PST LOG: checkpoint complete: wrote 10869 buffers (5.7%); 0 transaction log file(s) added, 0 removed, 5 recycled; write=3D149.995 s, sync=3D0.000 s, total=3D149.996 s 2010-02-25 22:43:13 PST LOG: checkpoint starting: time 2010-02-25 22:45:41 PST LOG: checkpoint complete: wrote 31424 buffers (16.4%); 0 transaction log file(s) added, 0 removed, 4 recycled; write=3D148.597 s, sync=3D0.000 s, total=3D148.598 s 2010-02-25 22:48:13 PST LOG: checkpoint starting: time 2010-02-25 22:50:42 PST LOG: checkpoint complete: wrote 33895 buffers (17.7%); 0 transaction log file(s) added, 0 removed, 17 recycled; write=3D149.825 s, sync=3D0.000 s, total=3D149.826 s 2010-02-25 22:53:13 PST LOG: checkpoint starting: time 2010-02-25 22:53:17 PST postgres postgres [local] LOG: unexpected EOF on client connection 2010-02-25 22:55:43 PST LOG: checkpoint complete: wrote 34155 buffers (17.8%); 0 transaction log file(s) added, 0 removed, 15 recycled; write=3D150.045 s, sync=3D0.000 s, total=3D150.046 s 2010-02-25 22:58:13 PST LOG: checkpoint starting: time 2010-02-25 23:00:41 PST LOG: checkpoint complete: wrote 33873 buffers (17.6%); 0 transaction log file(s) added, 0 removed, 15 recycled; write=3D148.223 s, sync=3D0.000 s, total=3D148.224 s 2010-02-25 23:03:13 PST LOG: checkpoint starting: time