Re: locking issue on simple selects?

On 23 September 2010 22:55, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Have you turned on checkpoint logging?

Yes ... it seems so:

13:19:13.840 - LOG: checkpoint complete: wrote 3849 buffers (0.2%); 0
transaction log file(s) added, 0 removed, 5 recycled; write=269.551 s,
sync=0.103 s, total=269.953 s
13:19:13.841 - LOG: checkpoint starting: xlog
13:19:33 - the seq scan query towards the affected table started
13:20:31.454 - one of the index lookup queries towards the affected
table was finished
13:20:43.176 - LOG: checkpoint complete: wrote 108199 buffers (6.9%);
0 transaction log file(s) added, 0 removed, 16 recycled; write=11.521
s, sync=77.533 s, total=89.335 s

> You might want to see if
> these are happening at some particular point in the checkpoint
> processing.  If so, look through the archives for posts from Greg
> Smith on how to tune that -- he's worked out a nice methodology to
> iteratively improve your configuration in this regard.

Thank you, I will ... hmm ... I found this blog post:

http://blog.2ndquadrant.com/en/2010/01/measuring-postgresql-checkpoin.html

Of course I'm doing it my own way:

select *,now() as snapshot into tmp_pg_stat_bgwriter from pg_stat_bgwriter ;

create view tmp_delta_pg_stat_bgwriter as
select a.checkpoints_timed-b.checkpoints_timed as
checkpoints_timed,a.checkpoints_req-b.checkpoints_req as
checkpoints_req,a.buffers_checkpoint-b.buffers_checkpoint as
buffers_checkpoint,a.buffers_clean-b.buffers_clean as
buffers_clean,a.maxwritten_clean-b.maxwritten_clean as
maxwritten_clean,a.buffers_backend-b.buffers_backend as
buffers_backend,a.buffers_alloc-b.buffers_alloc as buffers_alloc,
now()-b.snapshot as interval
from pg_stat_bgwriter a ,
(select * from tmp_pg_stat_bgwriter order by snapshot desc limit 1) as b;

Checkpoint timeout is set to 5 minutes. Right now we're having
relatively low activity. I'm not sure how to read the stats below,
but they look OK to me:

select * from tmp_delta_pg_stat_bgwriter ;
checkpoints_timed | checkpoints_req | buffers_checkpoint |
buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc |
interval
-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------+-----------------
3 | 0 | 8277 |
15 | 0 | 185 | 18691 |
00:12:02.988842
(1 row)