Automatic adjustment of bgwriter_lru_maxpages (was: Dead Space Map version 2)

"Jim C. Nasby" <jim(at)nasby(dot)net> wrote:

> > > Perhaps it would be better to have the bgwriter take a look at how many
> > > dead tuples (or how much space the dead tuples account for) when it
> > > writes a page out and adjust the DSM at that time.
> >
> > Yeah, I feel it is worth optimizable, too. One question is, how we treat
> > dirty pages written by backends not by bgwriter? If we want to add some
> > works in bgwriter, do we also need to make bgwriter to write almost of
> > dirty pages?
>
> IMO yes, we want the bgwriter to be the only process that's normally
> writing pages out. How close we are to that, I don't know...

I'm working on making the bgwriter to write almost of dirty pages. This is
the proposal for it using automatic adjustment of bgwriter_lru_maxpages.

The bgwriter_lru_maxpages value will be adjusted to the equal number of calls
of StrategyGetBuffer() per cycle with some safety margins (x2 at present).
The counter are incremented per call and reset to zero at StrategySyncStart().

This patch alone is not so useful except for hiding hardly tunable parameters
from users. However, it would be a first step of allow bgwriters to do some
works before writing dirty buffers.

- [DSM] Pick out pages worth vaccuming and register them into DSM.
- [HOT] Do a per page vacuum for HOT updated tuples. (Is it worth doing?)
- [TODO Item] Shrink expired COLD updated tuples to just their headers.
- Set commit hint bits to reduce subsequent writes of blocks.
http://archives.postgresql.org/pgsql-hackers/2007-01/msg01363.php

I tested the attached patch on pgbench -s5 (80MB) with shared_buffers=32MB.
I got an expected result as below. Over 75% of buffers are written by
bgwriter. In addition , automatic adjusted bgwriter_lru_maxpages values
were much higher than the default value (5). It shows that the most suitable
values greatly depends on workloads.

benchmark | throughput | cpu-usage | by-bgwriter | bgwriter_lru_maxpages
------------+------------+-----------+-------------+-----------------------
default | 300tps | 100% | 77.5% | 120 pages/cycle
with sleep | 150tps | 50% | 98.6% | 70 pages/cycle

I hope that this patch will be a first step of the intelligent bgwriter.
Comments welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center