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 [EMAIL PROTECTED]
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
