Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id B3BF91D9D767 for ; Wed, 9 Nov 2011 12:06:55 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 46055-05 for ; Wed, 9 Nov 2011 16:06:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.10]) by mail.postgresql.org (Postfix) with ESMTP id 523271D9D766 for ; Wed, 9 Nov 2011 12:06:49 -0400 (AST) Received: from [192.168.1.3] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis) id 0M51gc-1R0TiT3Euk-00z7z6; Wed, 09 Nov 2011 17:06:48 +0100 Message-ID: <4EBAA516.2070307@2ndQuadrant.com> Date: Wed, 09 Nov 2011 11:06:46 -0500 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20110818 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: WAL partition filling up after high WAL activity References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------060200000403060308000401" X-Provags-ID: V02:K0:TAOLljhbqx4AYcyJ0cWlah7uDsPe906HlI+oo+25qOn 8bV6wtCVo32YQTXqruux5+AOTgkxb/4lFyWxQ1MzZFx+//dycQ m5LDJEuvX6B8PBvtsWK6x/2t8bGXEroE2wX422xeJnwftpn+Av sidM011vfylPq6d2WMUjKvpEu5bitH7hxb0u+ke5gSLJnT0itd UuinhgiBImcpazpdCk9ZLQbpzbDGgiIbYb/gZ7LNstbT7horQ6 U6gb/41MZ6rc7oyp0HFjX9gkCJkAVduIDubNbaxVkvqOkHkPHH YTCSyoC5Uy6Uy4i55c3zXl08uiYFM7TSTB0LSpbZspEewFLkIq srnWXh/xnvby7v7KF3AE= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201111/124 X-Sequence-Number: 45635 This is a multi-part message in MIME format. --------------060200000403060308000401 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 11/07/2011 05:18 PM, Richard Yen wrote: > My biggest question is: we know from the docs that there should be no > more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 > files. For us, that would mean no more than 48 files, which equates > to 384MB--far lower than the 9.7GB partition size. **Why would WAL > use up so much disk space?** > That's only true if things are operating normally. There are at least two ways this can fail to be a proper upper limit on space used: 1) You are archiving to a second system, and the archiving isn't keeping up. Things that haven't been archived can't be re-used, so more disk space is used. 2) Disk I/O is slow, and the checkpoint writes take a significant period of time. The internal scheduling assumes each individual write will happen without too much delay. That assumption can easily be untrue on a busy system. The worst I've seen now are checkpoints that take 6 hours to sync, where the time is supposed to be a few seconds. Disk space in that case was a giant multiple of checkpoint_segments. (The source of that problem is very much improved in PostgreSQL 9.1) The info needed to figure out which category you're in would appear after tuning log_checkpoints on in the postgresql.conf ; you only need to reload the server config after that, doesn't require a restart. I would guess you have realy long sync times there. As for what to do about it, checkpoint_segments=16 is a low setting. You might as well set it to a large number, say 128, and let checkpoints get driven by time instead. The existing limit isn't working effectively anyway, and having more segments lets the checkpoint spreading code work more evenly. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us --------------060200000403060308000401 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 11/07/2011 05:18 PM, Richard Yen wrote:
My biggest question is: we know from the docs that there should be no more than (2 + checkpoint_completion_target) * checkpoint_segments + 1 files.  For us, that would mean no more than 48 files, which equates to 384MB--far lower than the 9.7GB partition size.  **Why would WAL use up so much disk space?**


That's only true if things are operating normally.  There are at least two ways this can fail to be a proper upper limit on space used:

1) You are archiving to a second system, and the archiving isn't keeping up.  Things that haven't been archived can't be re-used, so more disk space is used.

2) Disk I/O is slow, and the checkpoint writes take a significant period of time.  The internal scheduling assumes each individual write will happen without too much delay.  That assumption can easily be untrue on a busy system.  The worst I've seen now are checkpoints that take 6 hours to sync, where the time is supposed to be a few seconds.  Disk space in that case was a giant multiple of checkpoint_segments.  (The source of that problem is very much improved in PostgreSQL 9.1)

The info needed to figure out which category you're in would appear after tuning log_checkpoints on in the postgresql.conf ; you only need to reload the server config after that, doesn't require a restart.  I would guess you have realy long sync times there.

As for what to do about it, checkpoint_segments=16 is a low setting.  You might as well set it to a large number, say 128, and let checkpoints get driven by time instead.  The existing limit isn't working effectively anyway, and having more segments lets the checkpoint spreading code work more evenly.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
--------------060200000403060308000401--