Skip site navigation (1) Skip section navigation (2)

Re: Hot standby having high requested checkpoints?

From: Brian Fehrle <brianf(at)consistentstate(dot)com>
To: Simon Riggs <simon(at)2ndQuadrant(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Hot standby having high requested checkpoints?
Date: 2012-03-01 23:38:16
Message-ID: 4F500868.8060603@consistentstate.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)consistentstate(dot)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?
>


In response to

Responses

pgsql-admin by date

Next:From: Piyush LenkaDate: 2012-03-02 10:19:30
Subject: Restoring selected records from backup file
Previous:From: Khangelani GamaDate: 2012-03-01 10:16:54
Subject: Re: Can't recover data from a .tar.gz file which was backed up using COPY BINARY command

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group