From brianf@consistentstate.com Tue Feb  28 22:57:13 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 2FC0B9552D7
	for <pgsql-admin@postgresql.org>; Tue, 28 Feb 2012 18:57:13 -0400 (AST)
Received: from mail-pz0-f46.google.com ([209.85.210.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <brianf@consistentstate.com>) id 1S2Vyr-00071A-0D
	for pgsql-admin@postgresql.org; Tue, 28 Feb 2012 22:57:13 +0000
Received: by daed14 with SMTP id d14so2868627dae.19
	for <pgsql-admin@postgresql.org>; Tue, 28 Feb 2012 14:56:59 -0800 (PST)
Received-SPF: pass (google.com: domain of brianf@consistentstate.com
	designates 10.68.225.194 as permitted sender)
	client-ip=10.68.225.194;
Authentication-Results: mr.google.com; spf=pass (google.com: domain of
	brianf@consistentstate.com designates 10.68.225.194 as
	permitted sender) smtp.mail=brianf@consistentstate.com
Received: from mr.google.com ([10.68.225.194])
	by 10.68.225.194 with SMTP id rm2mr59834613pbc.95.1330469819808
	(num_hops = 1); Tue, 28 Feb 2012 14:56:59 -0800 (PST)
Received: by 10.68.225.194 with SMTP id rm2mr50346888pbc.95.1330469819762;
	Tue, 28 Feb 2012 14:56:59 -0800 (PST)
Received: from sidon.consistentstate.com
	(173-164-51-9-colorado.hfc.comcastbusiness.net. [173.164.51.9])
	by mx.google.com with ESMTPS id g3sm16809619pbt.41.2012.02.28.14.56.58
	(version=SSLv3 cipher=OTHER); Tue, 28 Feb 2012 14:56:59 -0800 (PST)
Message-ID: <4F4D5AE0.9020204@consistentstate.com>
Date: Tue, 28 Feb 2012 15:53:20 -0700
From: Brian Fehrle <brianf@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.26) Gecko/20120129 Lightning/1.0b2 Thunderbird/3.1.18
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Hot standby having high requested checkpoints?
Content-Type: multipart/alternative;
	boundary="------------030602080107000105090900"
X-Gm-Message-State: 
 ALoCoQmkrwVNy3Yuhymu7OCvGgiRjnM7BN+/xgI9IWwgdVIw6+En9NKrW8AyCXnQLQKgQY0NmMXJ
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201202/122
X-Sequence-Number: 39182

This is a multi-part message in MIME format.
--------------030602080107000105090900
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all,

I have two PostgreSQL 9.0.5 clusters, one is a master and the other is a 
hot standby via streaming replication. I'm monitoring some stats on each 
and I'm noticing something very odd. On the master, I get between 2 and 
4 requested checkpoints per hour, but on the hot standby I'm seeing 
between 200 and 300 requested checkpoints per hour.

Both boxes are the same specs, and have the same following config 
parameters:
              name             | setting
------------------------------+---------
  bgwriter_delay               | 200
  bgwriter_lru_maxpages        | 350
  bgwriter_lru_multiplier      | 2
  checkpoint_completion_target | 0.5
  checkpoint_segments          | 256
  checkpoint_timeout           | 1800


Is there any reason why I would be seeing this type of behavior on the 
hot standby? Is it the standard type of behavior on a hot standby that 
I'm only now noticing?

Thanks in advance,
- Brian F

--------------030602080107000105090900
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body text="#000000" bgcolor="#ffffff">
    Hi all,<br>
    <br>
    I have two PostgreSQL 9.0.5 clusters, one is a master and the other
    is a hot standby via streaming replication. I'm monitoring some
    stats on each and I'm noticing something very odd. On the master, I
    get between 2 and 4 requested checkpoints per hour, but on the hot
    standby I'm seeing between 200 and 300 requested checkpoints per
    hour. <br>
    <br>
    Both boxes are the same specs, and have the same following config
    parameters:<br>
    <font face="monospace">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | setting<br>
      ------------------------------+---------<br>
      &nbsp;bgwriter_delay&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 200<br>
      &nbsp;bgwriter_lru_maxpages&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 350<br>
      &nbsp;bgwriter_lru_multiplier&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 2<br>
      &nbsp;checkpoint_completion_target | 0.5<br>
      &nbsp;checkpoint_segments&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 256<br>
      &nbsp;checkpoint_timeout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 1800</font><br>
    <br>
    <br>
    Is there any reason why I would be seeing this type of behavior on
    the hot standby? Is it the standard type of behavior on a hot
    standby that I'm only now noticing?<br>
    <br>
    Thanks in advance,<br>
    - Brian F<br>
  </body>
</html>

--------------030602080107000105090900--



From masao.fujii@gmail.com Wed Feb  29 04:22:47 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 2FE101A3BCAC
	for <pgsql-admin@postgresql.org>; Wed, 29 Feb 2012 00:22:47 -0400 (AST)
Received: from mail-iy0-f174.google.com ([209.85.210.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <masao.fujii@gmail.com>) id 1S2b3u-0003ng-4f
	for pgsql-admin@postgresql.org; Wed, 29 Feb 2012 04:22:46 +0000
Received: by iagj37 with SMTP id j37so2138808iag.19
	for <pgsql-admin@postgresql.org>; Tue, 28 Feb 2012 20:22:33 -0800 (PST)
Received-SPF: pass (google.com: domain of masao.fujii@gmail.com designates
	10.42.18.195 as permitted sender) client-ip=10.42.18.195;
Authentication-Results: mr.google.com;
	spf=pass (google.com: domain of masao.fujii@gmail.com
	designates 10.42.18.195 as permitted sender)
	smtp.mail=masao.fujii@gmail.com;
	dkim=pass header.i=masao.fujii@gmail.com
Received: from mr.google.com ([10.42.18.195])
	by 10.42.18.195 with SMTP id y3mr19555091ica.13.1330489353667 (num_hops
	= 1); Tue, 28 Feb 2012 20:22:33 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=VbCD1DDYUGB+NorSYekjgPiG/WGQooNoWW2RHOSkuAo=;
	b=lYbmwfUchmj/1rjkYvnP5XEicJH2aP7s5sKAGCox36WupYkpaqo5xC4s7vbzawT168
	B3jUlaNVriAicHqgwNe91y7krR9LtwhvExG7Jr9HmhJ8K0vHGfnJ9v3G3Oy41V1ENkBv
	7X1mbuEu7Fk3EoYGpYgGrEZsWE34dg6eJ6McY=
MIME-Version: 1.0
Received: by 10.42.18.195 with SMTP id y3mr15813979ica.13.1330489353628; Tue,
	28 Feb 2012 20:22:33 -0800 (PST)
Received: by 10.50.93.68 with HTTP; Tue, 28 Feb 2012 20:22:33 -0800 (PST)
In-Reply-To: <4F4D5AE0.9020204@consistentstate.com>
References: <4F4D5AE0.9020204@consistentstate.com>
Date: Wed, 29 Feb 2012 13:22:33 +0900
Message-ID: 
 <CAHGQGwF+g6c+eU1M9Lq-PfeEz8V525p1utyBUs8wj2xh7k=_Rw@mail.gmail.com>
Subject: Re: Hot standby having high requested checkpoints?
From: Fujii Masao <masao.fujii@gmail.com>
To: Brian Fehrle <brianf@consistentstate.com>
Cc: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201202/123
X-Sequence-Number: 39183

On Wed, Feb 29, 2012 at 7:53 AM, Brian Fehrle
<brianf@consistentstate.com> wrote:
> Hi all,
>
> I have two PostgreSQL 9.0.5 clusters, one is a master and the other is a hot
> standby via streaming replication. I'm monitoring some stats on each and I'm
> noticing something very odd. On the master, I get between 2 and 4 requested
> checkpoints per hour, but on the hot standby I'm seeing between 200 and 300
> requested checkpoints per hour.

How did you get that information? Enable log_checkpoints?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center



From brianf@consistentstate.com Wed Feb  29 15:18:40 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 1A4681A3BCAE
	for <pgsql-admin@postgresql.org>; Wed, 29 Feb 2012 11:18:40 -0400 (AST)
Received: from mail-pz0-f46.google.com ([209.85.210.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <brianf@consistentstate.com>) id 1S2lId-00060N-Vg
	for pgsql-admin@postgresql.org; Wed, 29 Feb 2012 15:18:40 +0000
Received: by dajr28 with SMTP id r28so622766daj.33
	for <pgsql-admin@postgresql.org>; Wed, 29 Feb 2012 07:18:27 -0800 (PST)
Received-SPF: pass (google.com: domain of brianf@consistentstate.com
	designates 10.68.211.202 as permitted sender)
	client-ip=10.68.211.202;
Authentication-Results: mr.google.com; spf=pass (google.com: domain of
	brianf@consistentstate.com designates 10.68.211.202 as
	permitted sender) smtp.mail=brianf@consistentstate.com
Received: from mr.google.com ([10.68.211.202])
	by 10.68.211.202 with SMTP id ne10mr7551883pbc.164.1330528707015
	(num_hops = 1); Wed, 29 Feb 2012 07:18:27 -0800 (PST)
Received: by 10.68.211.202 with SMTP id ne10mr6422534pbc.164.1330528706960;
	Wed, 29 Feb 2012 07:18:26 -0800 (PST)
Received: from sidon.consistentstate.com
	(173-164-51-9-colorado.hfc.comcastbusiness.net. [173.164.51.9])
	by mx.google.com with ESMTPS id p9sm18918033pbb.9.2012.02.29.07.18.25
	(version=SSLv3 cipher=OTHER); Wed, 29 Feb 2012 07:18:25 -0800 (PST)
Message-ID: <4F4E40E5.9040006@consistentstate.com>
Date: Wed, 29 Feb 2012 08:14:45 -0700
From: Brian Fehrle <brianf@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.26) Gecko/20120129 Lightning/1.0b2 Thunderbird/3.1.18
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Hot standby having high requested checkpoints?
References: <4F4D5AE0.9020204@consistentstate.com>
	<CAHGQGwF+g6c+eU1M9Lq-PfeEz8V525p1utyBUs8wj2xh7k=_Rw@mail.gmail.com>
In-Reply-To: 
 <CAHGQGwF+g6c+eU1M9Lq-PfeEz8V525p1utyBUs8wj2xh7k=_Rw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Gm-Message-State: 
 ALoCoQkqN6u5+h7PezRfDukyte/1e+BtPloCIVa4fytdNTnOYD9DeVSn44RBCn4x1g8gDEONqyF+
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201202/125
X-Sequence-Number: 39185

I am taking periodic snapshots from the system view 'pg_stat_bgwriter' 
from both the master and the hot standby.

- Brian F

On 02/28/2012 09:22 PM, Fujii Masao wrote:
> On Wed, Feb 29, 2012 at 7:53 AM, Brian Fehrle
> <brianf@consistentstate.com>  wrote:
>> Hi all,
>>
>> I have two PostgreSQL 9.0.5 clusters, one is a master and the other is a hot
>> standby via streaming replication. I'm monitoring some stats on each and I'm
>> noticing something very odd. On the master, I get between 2 and 4 requested
>> checkpoints per hour, but on the hot standby I'm seeing between 200 and 300
>> requested checkpoints per hour.
> How did you get that information? Enable log_checkpoints?
>
> Regards,
>




From masao.fujii@gmail.com Thu Mar  1 02:18:13 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id E8ED284B067
	for <pgsql-admin@postgresql.org>; Wed, 29 Feb 2012 22:18:13 -0400 (AST)
Received: from mail-iy0-f174.google.com ([209.85.210.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <masao.fujii@gmail.com>) id 1S2vau-0001Ho-Eq
	for pgsql-admin@postgresql.org; Thu, 01 Mar 2012 02:18:13 +0000
Received: by iagz16 with SMTP id z16so166945iag.19
	for <pgsql-admin@postgresql.org>; Wed, 29 Feb 2012 18:17:59 -0800 (PST)
Received-SPF: pass (google.com: domain of masao.fujii@gmail.com designates
	10.50.236.5 as permitted sender) client-ip=10.50.236.5;
Authentication-Results: mr.google.com;
	spf=pass (google.com: domain of masao.fujii@gmail.com
	designates 10.50.236.5 as permitted sender)
	smtp.mail=masao.fujii@gmail.com;
	dkim=pass header.i=masao.fujii@gmail.com
Received: from mr.google.com ([10.50.236.5])
	by 10.50.236.5 with SMTP id uq5mr23246985igc.13.1330568279794 (num_hops
	= 1); Wed, 29 Feb 2012 18:17:59 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=9n6iDvVVexMnL+XlaB+33uht5ctlEpMyQm76IV7xcbs=;
	b=GAkyy4fmde3Im0lvDcBrSqxxaN4VrDxNmDlCFR7FyaBjDv1LdYIBc3eNsnksPUFn4H
	5gQlLdvGgjjVtkoRwVVTmUMj3150RVVPQ9R2SNPz+bqRYPfUt0iB9OKV/sCNKL34pfWE
	TYRrveAcQOLzEqRzphoQx3yIBBJ3qIV1NYxyY=
MIME-Version: 1.0
Received: by 10.50.236.5 with SMTP id uq5mr19019040igc.13.1330568279757; Wed,
	29 Feb 2012 18:17:59 -0800 (PST)
Received: by 10.50.93.68 with HTTP; Wed, 29 Feb 2012 18:17:59 -0800 (PST)
In-Reply-To: <4F4E40E5.9040006@consistentstate.com>
References: <4F4D5AE0.9020204@consistentstate.com>
	<CAHGQGwF+g6c+eU1M9Lq-PfeEz8V525p1utyBUs8wj2xh7k=_Rw@mail.gmail.com>
	<4F4E40E5.9040006@consistentstate.com>
Date: Thu, 1 Mar 2012 11:17:59 +0900
Message-ID: 
 <CAHGQGwFQ-4CFX_0APMSDjFse+c8vGs_-SVbF04ydXcid+0-tJQ@mail.gmail.com>
Subject: Re: Hot standby having high requested checkpoints?
From: Fujii Masao <masao.fujii@gmail.com>
To: Brian Fehrle <brianf@consistentstate.com>
Cc: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201202/128
X-Sequence-Number: 39188

On Thu, Mar 1, 2012 at 12:14 AM, Brian Fehrle
<brianf@consistentstate.com> wrote:
> I am taking periodic snapshots from the system view 'pg_stat_bgwriter' from
> both the master and the hot standby.

You mean that you observed that pg_stat_bgwriter.checkpoints_timed in
the standby was larger than that in the master? I ran pgbench on streaming
replication and checked pg_stat_bgwriter, but I could not reproduce the
problem you encountered. pg_stat_bgwriter.checkpoints_timed in the
standby was equal to or one more than that in the master. Could you tell me
the self-contained test case?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center



From simon@2ndQuadrant.com Thu Mar  1 08:41:19 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id A6A081014388
	for <pgsql-admin@postgresql.org>; Thu,  1 Mar 2012 04:41:19 -0400 (AST)
Received: from outmail149084.authsmtp.net ([62.13.149.84])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <simon@2ndQuadrant.com>) id 1S31Zb-0003yo-Uj
	for pgsql-admin@postgresql.org; Thu, 01 Mar 2012 08:41:18 +0000
Received: from mail-c194.authsmtp.com (mail-c194.authsmtp.com [62.13.128.121])
	by punt8.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id q218f2OD091795
	for <pgsql-admin@postgresql.org>; Thu, 1 Mar 2012 08:41:02 GMT
Received: from mail-tul01m020-f174.google.com (mail-tul01m020-f174.google.com
	[209.85.214.174]) (authenticated bits=0)
	by mail.authsmtp.com (8.14.2/8.14.2) with ESMTP id q218ev9q046922
	(version=TLSv1/SSLv3 cipher=RC4-SHA bits=128 verify=FAIL)
	for <pgsql-admin@postgresql.org>; Thu, 1 Mar 2012 08:40:58 GMT
Received: by obbuo6 with SMTP id uo6so90722obb.19
	for <pgsql-admin@postgresql.org>; Thu, 01 Mar 2012 00:40:57 -0800 (PST)
Received-SPF: pass (google.com: domain of simon@2ndQuadrant.com designates
	10.60.22.169 as permitted sender) client-ip=10.60.22.169;
Authentication-Results: mr.google.com;
	spf=pass (google.com: domain of simon@2ndQuadrant.com
	designates 10.60.22.169 as permitted sender)
	smtp.mail=simon@2ndQuadrant.com
Received: from mr.google.com ([10.60.22.169])
	by 10.60.22.169 with SMTP id e9mr1537675oef.24.1330591257207 (num_hops
	= 1); Thu, 01 Mar 2012 00:40:57 -0800 (PST)
MIME-Version: 1.0
Received: by 10.60.22.169 with SMTP id e9mr1328077oef.24.1330590901972; Thu,
	01 Mar 2012 00:35:01 -0800 (PST)
Received: by 10.60.63.170 with HTTP; Thu, 1 Mar 2012 00:35:01 -0800 (PST)
In-Reply-To: <4F4D5AE0.9020204@consistentstate.com>
References: <4F4D5AE0.9020204@consistentstate.com>
Date: Thu, 1 Mar 2012 08:35:01 +0000
Message-ID: 
 <CA+U5nMKcy_wkzJdpwsjyaa76ucyw0FkDhdXnMCypbAwPF0JSEQ@mail.gmail.com>
Subject: Re: Hot standby having high requested checkpoints?
From: Simon Riggs <simon@2ndQuadrant.com>
To: Brian Fehrle <brianf@consistentstate.com>
Cc: pgsql-admin@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Server-Quench: 44a65ef1-637a-11e1-80b9-0022640b883e
X-AuthReport-Spam: If SPAM / abuse - report it at:
	http://www.authsmtp.com/abuse
X-AuthRoute: OCdxZQATClZOTQEd DAteCiN5VAwpPBRK HVkIKg5MOFUSTAAU
	O0pfL1x7KFoHWV5D ViVKB0oLEgo/WWJq FlsTcgddY0heXw1g
	VEhDR1ocFgVhHxQE GBwfVgdzcAFZfXZ3 ekcsH3JaVBE8fAd9
	RkhdF29IYWVjYC4b VBFeclYCcR5MfRgU d1F+AXIQaWVVb2dg
	R1VpYWxpN2UPcylD eAxfZWE7bWMqOAt0 ZxceVS4zFEsJTTR7
	LxU4K1gTVEYQPUV6 K1Y4RF8DLxIeQgZe EkpOGChYIUUaEEIA
X-Authentic-SMTP: 61633235383639.1015:706
X-AuthFastPath: 0 (Was 255)
X-AuthVirus-Status: No virus detected - but ensure you scan with your own
	anti-virus system.
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201203/2
X-Sequence-Number: 39190

On Tue, Feb 28, 2012 at 10:53 PM, Brian Fehrle
<brianf@consistentstate.com> wrote:

> I have two PostgreSQL 9.0.5 clusters, one is a master and the other is a =
hot
> standby via streaming replication. I'm monitoring some stats on each and =
I'm
> noticing something very odd. On the master, I get between 2 and 4 request=
ed
> checkpoints per hour, but on the hot standby I'm seeing between 200 and 3=
00
> requested checkpoints per hour.

We don't do a restartpoint on the standby unless we see a checkpoint
record, so that result should be impossible.

So I'm guessing you're reading the stats wrong?

--=20
=A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/=
/www.2ndQuadrant.com/
=A0PostgreSQL Development, 24x7 Support, Training & Services



From brianf@consistentstate.com Thu Mar  1 23:42:13 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id D16A5101438F
	for <pgsql-admin@postgresql.org>; Thu,  1 Mar 2012 19:42:13 -0400 (AST)
Received: from mail-pw0-f46.google.com ([209.85.160.46])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <brianf@consistentstate.com>) id 1S3FdT-000698-ED
	for pgsql-admin@postgresql.org; Thu, 01 Mar 2012 23:42:12 +0000
Received: by pbcup15 with SMTP id up15so1449946pbc.19
	for <pgsql-admin@postgresql.org>; Thu, 01 Mar 2012 15:41:58 -0800 (PST)
Received-SPF: pass (google.com: domain of brianf@consistentstate.com
	designates 10.68.216.4 as permitted sender)
	client-ip=10.68.216.4;
Authentication-Results: mr.google.com; spf=pass (google.com: domain of
	brianf@consistentstate.com designates 10.68.216.4 as permitted
	sender) smtp.mail=brianf@consistentstate.com
Received: from mr.google.com ([10.68.216.4])
	by 10.68.216.4 with SMTP id om4mr8666022pbc.19.1330645318355 (num_hops
	= 1); Thu, 01 Mar 2012 15:41:58 -0800 (PST)
Received: by 10.68.216.4 with SMTP id om4mr7157451pbc.19.1330645318313;
	Thu, 01 Mar 2012 15:41:58 -0800 (PST)
Received: from sidon.consistentstate.com
	(74-93-229-125-Colorado.hfc.comcastbusiness.net. [74.93.229.125])
	by mx.google.com with ESMTPS id 2sm3275987pbw.57.2012.03.01.15.41.56
	(version=SSLv3 cipher=OTHER); Thu, 01 Mar 2012 15:41:57 -0800 (PST)
Message-ID: <4F500868.8060603@consistentstate.com>
Date: Thu, 01 Mar 2012 16:38:16 -0700
From: Brian Fehrle <brianf@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.26) Gecko/20120129 Lightning/1.0b2 Thunderbird/3.1.18
MIME-Version: 1.0
To: Simon Riggs <simon@2ndQuadrant.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Hot standby having high requested checkpoints?
References: <4F4D5AE0.9020204@consistentstate.com>
	<CA+U5nMKcy_wkzJdpwsjyaa76ucyw0FkDhdXnMCypbAwPF0JSEQ@mail.gmail.com>
In-Reply-To: 
 <CA+U5nMKcy_wkzJdpwsjyaa76ucyw0FkDhdXnMCypbAwPF0JSEQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Gm-Message-State: 
 ALoCoQmeMHUfVlgEkP3W68DEVw8tscLYOo+ePkddc0fSCszt+IyTXx8BKBr13S6Jmh67lFkCXvCZ
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201203/5
X-Sequence-Number: 39193

I just now ran the following query a few times after each other on the 
hot standby:

select now(), * from pg_stat_bgwriter;

Here are the results:

               now              | checkpoints_timed | checkpoints_req | 
buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend 
| buffers_alloc
-------------------------------+-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------
  2012-03-01 23:24:49.099194+00 |             11546 |          145300 
|         1000409459 |      38483026 |           170724 |         
5058186 |     438703950
  2012-03-01 23:24:52.139176+00 |             11546 |          145300 
|         1000409459 |      38483027 |           170724 |         
5058191 |     438703975
  2012-03-01 23:24:59.129171+00 |             11546 |          145302 
|         1000409459 |      38483081 |           170724 |         
5058214 |     438704438
  2012-03-01 23:25:05.957532+00 |             11546 |          145304 
|         1000409459 |      38483118 |           170724 |         
5058230 |     438704689
  2012-03-01 23:25:09.519175+00 |             11546 |          145305 
|         1000409459 |      38483160 |           170724 |         
5058239 |     438704968
  2012-03-01 23:25:37.019194+00 |             11546 |          145308 
|         1000409459 |      38483259 |           170724 |         
5058255 |     438705566
  2012-03-01 23:25:40.659164+00 |             11546 |          145308 
|         1000409459 |      38483268 |           170724 |         
5058257 |     438705639
  2012-03-01 23:25:47.239281+00 |             11546 |          145309 
|         1000409459 |      38483283 |           170724 |         
5058266 |     438705815
  2012-03-01 23:26:23.858716+00 |             11546 |          145312 
|         1000409459 |      38483393 |           170724 |         
5058307 |     438706561
  2012-03-01 23:26:46.467493+00 |             11546 |          145317 
|         1000409670 |      38483524 |           170724 |         
5058354 |     438707619

in two minutes, I saw 17 checkpoints_req, and the number of 
buffers_checkpoint didn't budge till the last few and even then not much.

checkpoint_segments = 256

- Brian F

On 03/01/2012 01:35 AM, Simon Riggs wrote:
> On Tue, Feb 28, 2012 at 10:53 PM, Brian Fehrle
> <brianf@consistentstate.com>  wrote:
>
>> I have two PostgreSQL 9.0.5 clusters, one is a master and the other is a hot
>> standby via streaming replication. I'm monitoring some stats on each and I'm
>> noticing something very odd. On the master, I get between 2 and 4 requested
>> checkpoints per hour, but on the hot standby I'm seeing between 200 and 300
>> requested checkpoints per hour.
> We don't do a restartpoint on the standby unless we see a checkpoint
> record, so that result should be impossible.
>
> So I'm guessing you're reading the stats wrong?
>




From brianf@consistentstate.com Thu Mar  8 22:16:08 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id D775D119C295
	for <pgsql-admin@postgresql.org>; Thu,  8 Mar 2012 18:16:08 -0400 (AST)
Received: from 74-93-229-125-colorado.hfc.comcastbusiness.net ([74.93.229.125]
	helo=mail.consistentstate.com)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <brianf@consistentstate.com>) id 1S5ld1-0002pK-K6
	for pgsql-admin@postgresql.org; Thu, 08 Mar 2012 22:16:08 +0000
Received: from localhost (localhost [127.0.0.1])
	by mail.consistentstate.com (Postfix) with ESMTP id 987E9304A31
	for <pgsql-admin@postgresql.org>; Thu,  8 Mar 2012 15:15:54 -0700 (MST)
X-Virus-Scanned: amavisd-new at consistentstate.com
Received: from mail.consistentstate.com ([127.0.0.1])
	by localhost (mail.consistentstate.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id L9bL6SnDBlmH for <pgsql-admin@postgresql.org>;
	Thu,  8 Mar 2012 15:15:50 -0700 (MST)
Received: from sidon.consistentstate.com (unknown [192.168.1.191])
	by mail.consistentstate.com (Postfix) with ESMTPSA id 10C2A3049AC
	for <pgsql-admin@postgresql.org>; Thu,  8 Mar 2012 15:15:50 -0700 (MST)
Message-ID: <4F592FA1.6060005@consistentstate.com>
Date: Thu, 08 Mar 2012 15:16:01 -0700
From: Brian Fehrle <brianf@consistentstate.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); en-US;
	rv:1.9.2.26) Gecko/20120129 Lightning/1.0b2 Thunderbird/3.1.18
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Hot standby having high requested checkpoints?
References: <4F4D5AE0.9020204@consistentstate.com>
	<CA+U5nMKcy_wkzJdpwsjyaa76ucyw0FkDhdXnMCypbAwPF0JSEQ@mail.gmail.com>
	<4F500868.8060603@consistentstate.com>
In-Reply-To: <4F500868.8060603@consistentstate.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -0.9 (/)
X-Archive-Number: 201203/43
X-Sequence-Number: 39231

Anyone have any thoughts on why this may be happening?

thanks,
- Brian F
On 03/01/2012 04:38 PM, Brian Fehrle wrote:
> I just now ran the following query a few times after each other on the 
> hot standby:
>
> select now(), * from pg_stat_bgwriter;
>
> Here are the results:
>
>               now              | checkpoints_timed | checkpoints_req | 
> buffers_checkpoint | buffers_clean | maxwritten_clean | 
> buffers_backend | buffers_alloc
> -------------------------------+-------------------+-----------------+--------------------+---------------+------------------+-----------------+--------------- 
>
>  2012-03-01 23:24:49.099194+00 |             11546 |          145300 
> |         1000409459 |      38483026 |           170724 |         
> 5058186 |     438703950
>  2012-03-01 23:24:52.139176+00 |             11546 |          145300 
> |         1000409459 |      38483027 |           170724 |         
> 5058191 |     438703975
>  2012-03-01 23:24:59.129171+00 |             11546 |          145302 
> |         1000409459 |      38483081 |           170724 |         
> 5058214 |     438704438
>  2012-03-01 23:25:05.957532+00 |             11546 |          145304 
> |         1000409459 |      38483118 |           170724 |         
> 5058230 |     438704689
>  2012-03-01 23:25:09.519175+00 |             11546 |          145305 
> |         1000409459 |      38483160 |           170724 |         
> 5058239 |     438704968
>  2012-03-01 23:25:37.019194+00 |             11546 |          145308 
> |         1000409459 |      38483259 |           170724 |         
> 5058255 |     438705566
>  2012-03-01 23:25:40.659164+00 |             11546 |          145308 
> |         1000409459 |      38483268 |           170724 |         
> 5058257 |     438705639
>  2012-03-01 23:25:47.239281+00 |             11546 |          145309 
> |         1000409459 |      38483283 |           170724 |         
> 5058266 |     438705815
>  2012-03-01 23:26:23.858716+00 |             11546 |          145312 
> |         1000409459 |      38483393 |           170724 |         
> 5058307 |     438706561
>  2012-03-01 23:26:46.467493+00 |             11546 |          145317 
> |         1000409670 |      38483524 |           170724 |         
> 5058354 |     438707619
>
> in two minutes, I saw 17 checkpoints_req, and the number of 
> buffers_checkpoint didn't budge till the last few and even then not much.
>
> checkpoint_segments = 256
>
> - Brian F
>
> On 03/01/2012 01:35 AM, Simon Riggs wrote:
>> On Tue, Feb 28, 2012 at 10:53 PM, Brian Fehrle
>> <brianf@consistentstate.com>  wrote:
>>
>>> I have two PostgreSQL 9.0.5 clusters, one is a master and the other 
>>> is a hot
>>> standby via streaming replication. I'm monitoring some stats on each 
>>> and I'm
>>> noticing something very odd. On the master, I get between 2 and 4 
>>> requested
>>> checkpoints per hour, but on the hot standby I'm seeing between 200 
>>> and 300
>>> requested checkpoints per hour.
>> We don't do a restartpoint on the standby unless we see a checkpoint
>> record, so that result should be impossible.
>>
>> So I'm guessing you're reading the stats wrong?
>>
>
>