Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M43037=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Xlpw5-0000xC-BC
 for pgsql-admin@arkaria.postgresql.org; Wed, 05 Nov 2014 02:03:01 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M43037=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1Xlpw4-0005Qe-E0
 for pgsql-admin@arkaria.postgresql.org; Wed, 05 Nov 2014 02:03:00 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1Xlpw2-0005QV-3x
 for pgsql-admin@postgresql.org; Wed, 05 Nov 2014 02:02:58 +0000
Received: from mail-qg0-x22a.google.com ([2607:f8b0:400d:c04::22a])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1Xlpvy-0005vz-2x
 for pgsql-admin@postgresql.org; Wed, 05 Nov 2014 02:02:56 +0000
Received: by mail-qg0-f42.google.com with SMTP id i50so10716332qgf.1
 for <pgsql-admin@postgresql.org>; Tue, 04 Nov 2014 18:02:53 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=references:mime-version:in-reply-to:content-type
 :content-transfer-encoding:message-id:cc:from:subject:date:to;
 bh=mGKaGuwOCkYNQrlEkrQutU58PaO1MZESOM7adtSNkBI=;
 b=EvS61ExNlAGT2mPIOiDFX7wR49WRa/uOczm8YUo1WBSlTDQU8F+wgvlbKRVz0yk7gl
 gmCdu4J3gMI7HjsfdIU7HoLm6dMGvlSnxmG+rdupgZZ34CJWpCpOfEDCTXNT4JWXi0Tq
 qyEcTg5zUhVxcKGI1j4Cu39Fyn/HOCAmVefkrvm5h4uxFqZk6Cr+0R3rvDvd807hl0vW
 ytVezXwgLuYbkNc2ubx9DkEhptrWvlN+2Cox3J5e6+nk9ibo3PlzeHbRvXrGeBWm1VLm
 PFqfg9rm/DR151eI8ikdoGcVKOnyTnDsJMr607fD0cl+03N3XYmCVOxHd41+CaKLkHR/
 rJVQ==
X-Received: by 10.224.23.9 with SMTP id p9mr14686026qab.92.1415152972983;
 Tue, 04 Nov 2014 18:02:52 -0800 (PST)
Received: from [192.168.254.3] (h77.89.102.166.static.ip.windstream.net.
 [166.102.89.77])
 by mx.google.com with ESMTPSA id g2sm1984365qaf.15.2014.11.04.18.02.51
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Tue, 04 Nov 2014 18:02:51 -0800 (PST)
References: <F592F2DDBBFE4EBD82C9B785F14B4BE5@mia>
Mime-Version: 1.0 (1.0)
In-Reply-To: <F592F2DDBBFE4EBD82C9B785F14B4BE5@mia>
Content-Type: multipart/alternative;
 boundary=Apple-Mail-340D1C29-A37F-4A9A-BD92-2A56CBCBCDDF
Content-Transfer-Encoding: 7bit
Message-Id: <75596940-87AC-4ECD-9E1A-3D4D37D4F54E@gmail.com>
Cc: "<pgsql-admin@postgresql.org>" <pgsql-admin@postgresql.org>
X-Mailer: iPad Mail (11D257)
From: jayknowsunix@gmail.com
Subject: Re: xlog-cleanup problem
Date: Tue, 4 Nov 2014 21:02:52 -0500
To: Tom Fischer <tom@timia.de>
X-Pg-Spam-Score: -2.7 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--Apple-Mail-340D1C29-A37F-4A9A-BD92-2A56CBCBCDDF
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable

Tom,

When you say the script is consumating the WAL files, what is it actually do=
ing? Here at Verizon, we just invoke an hourly pg_basebackup via a cron job.=
 Cron invokes a python script that copies the tar.gz file into part of our c=
loud when the backup is finished. If your script is using some other pg mech=
anism for backing up, most of those are just slow anyway.
--
Jay

Sent from my iPad

> On Nov 4, 2014, at 7:13 PM, "Tom Fischer" <tom@timia.de> wrote:
>=20
> Hello,
> =20
> we have some problems with the xlog-directory (size 5 GB). When we take a b=
ackup from one of our databases (about 80 GB data) and use an own script cal=
led by recovery.conf it begins to start the consumation of the wal-files fet=
ched from our nas at a rate of about 2 per second.
> =20
> Postgres just will not clean up the xlog-directory in time, so it runs ful=
l and next essential wal-file file could not be copied.
> So my script stops for 5 minutes and sometimes the xlog is cleaned up a bi=
t (from 100% to 84%) and the game starts again, there also has been some kin=
d of lock and nothing happened until I restarted the cluster. On a restart t=
he directory is cleaned up and about one half of the 5 GB will become availa=
ble.
> =20
> Checkpoint_segments are set to 64, wal-file-size is 16 MB, so at about 300=
 wal-files the whole game is over even if there should never be more then 3 *=
 64 + xxx, what should be under 200.
> Why do we get a lot more then 300 wal-files and what can we do against thi=
s, even 10 GB xlog-size is not enough to keep up some kind of balance.
> =20
> With friendly regards
> Tom Fischer

--Apple-Mail-340D1C29-A37F-4A9A-BD92-2A56CBCBCDDF
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: 7bit

<html><head><meta http-equiv="content-type" content="text/html; charset=utf-8"></head><body dir="auto"><div>Tom,</div><div><br></div><div>When you say the script is consumating the WAL files, what is it actually doing? Here at Verizon, we just invoke an hourly pg_basebackup via a cron job. Cron invokes a python script that copies the tar.gz file into part of our cloud when the backup is finished. If your script is using some other pg mechanism for backing up, most of those are just slow anyway.</div><div>--</div><div>Jay<br><br>Sent from my iPad</div><div><br>On Nov 4, 2014, at 7:13 PM, "Tom Fischer" &lt;<a href="mailto:tom@timia.de">tom@timia.de</a>&gt; wrote:<br><br></div><blockquote type="cite"><div>

<div dir="ltr">
<div style="FONT-SIZE: 12pt; FONT-FAMILY: 'Calibri'; COLOR: #000000">
<div>Hello,</div>
<div>&nbsp;</div>
<div>we have some problems with the xlog-directory (size 5 GB). When we take a 
backup from one of our databases (about 80 GB data) and use an own script called 
by recovery.conf it begins to start the consumation of the wal-files fetched 
from our nas at a rate of about 2 per second.</div>
<div>&nbsp;</div>
<div>Postgres just will not clean up the xlog-directory in time, so it runs full 
and next essential wal-file file could not be copied.</div>
<div>So my script stops for 5 minutes and sometimes the xlog is cleaned up a bit 
(from 100% to 84%) and the game starts again, there also has been some kind of 
lock and nothing happened until I restarted the cluster. On a restart the 
directory is cleaned up and about one half of the 5 GB will become 
available.</div>
<div>&nbsp;</div>
<div>Checkpoint_segments are set to 64, wal-file-size is 16 MB, so at about 300 
wal-files the whole game is over even if there should never be more then 3 * 64 
+ xxx, what should be under 200.</div>
<div>Why do we get a lot more then 300 wal-files and what can we do against 
this, even 10 GB xlog-size is not enough to keep up some kind of balance.</div>
<div>&nbsp;</div>
<div>With friendly regards </div>
<div>Tom Fischer</div></div></div>
</div></blockquote></body></html>
--Apple-Mail-340D1C29-A37F-4A9A-BD92-2A56CBCBCDDF--