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

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?
>>
>
>