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

bgwriter statistics

From: Jim Nasby <jnasby(at)pervasive(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: bgwriter statistics
Date: 2006-06-02 21:26:06
Message-ID: A06F0A3D-DA32-41A2-81CE-DBB21D7FB2EC@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Now that we've got a nice amount of tuneability in the bgwriter, it  
would be nice if we had as much insight into how it's actually doing.  
I'd like to propose that the following info be added to the stats  
framework to assist in tuning it:

bgwriter_rounds - number of rounds that have run
bgwriter_lru_percent_scanned - total pages of the LRU end of the  
buffer pool scanned on each round
bgwriter_lru_pages_written - total LRU pages written
bgwriter_all_percent_scanned - total pages scanned for all of the  
buffer pool
bgwriter_all_pages_written - total pages written for all the buffer pool

To clarify: the 'all' statistics should correspond to  
bgwriter_all_percent and bgwriter_all_maxpages GUC's

Unfortunately, the above information doesn't tell you why (on  
average) the bgwriter is stopping each of it's scans (ie: did it hit  
the scan percentage limit, or did it hit the pages written limit). I  
think the next two would provide insight into that:

bgwriter_lru_scan_limit_hit - number of rounds where  
bgwriter_lru_percent was hit
bgwriter_all_scan_limit_hit - ditto for bgwriter_all_percent

Finally, the real reason for bgwriter's existence is to prevent the  
need to write many pages out during a checkpoint, so to monitor that:

checkpoint_timeouts - number of times we've hit checkpoint_timeout
checkpoint_timeout_pages - number of pages written during timeout  
checkpoints

checkpoint_segment_overflow, checkpoint_segment_pages - same thing,  
but for checkpoints that were forced because we ran out of WAL files

I suppose for completeness sake we should add stats_bgwriter and  
stats_checkpoint GUC's, though I don't see any issue with just  
leaving these turned on unless there's some folks out there running  
very low bgwriter_delay settings. Also, I'm wondering if it would be  
useful to have a way to reset just these statistics. If you're tuning  
things, you'll want to be able to see what effect your changes are  
having, which is a bit difficult without reseting the counters unless  
you've got them feeding into MRTG or something. But reseting all the  
counters would be rather bad if you're using autovacuum.

Comments?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



Responses

pgsql-hackers by date

Next:From: Tino WildenhainDate: 2006-06-02 21:29:48
Subject: Re: COPY (query) TO file
Previous:From: Mark WoodwardDate: 2006-06-02 21:23:25
Subject: Re: COPY (query) TO file

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