Re: Automatic adjustment of bgwriter_lru_maxpages

ITAGAKI Takahiro wrote:
> "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.

The general approach looks good to me. I'm queuing some benchmarks to
see how effective it is with a fairly constant workload.

This change in bgwriter.c looks fishy:

*************** BackgroundWriterMain(void)
*** 484,491 ****
*
* We absorb pending requests after each short sleep.
*/
! if ((bgwriter_all_percent > 0.0 && bgwriter_all_maxpages > 0) ||
! (bgwriter_lru_percent > 0.0 && bgwriter_lru_maxpages > 0))
udelay = BgWriterDelay * 1000L;
else if (XLogArchiveTimeout > 0)
udelay = 1000000L; /* One second */
--- 484,490 ----
*
* We absorb pending requests after each short sleep.
*/
! if (bgwriter_all_percent > 0.0 && bgwriter_all_maxpages > 0)
udelay = BgWriterDelay * 1000L;
else if (XLogArchiveTimeout > 0)
udelay = 1000000L; /* One second */

Doesn't that mean that bgwriter only runs every 1 or 10 seconds,
regardless of bgwriter_delay, if bgwriter_all_* parameters are not set?

The algorithm used to update bgwriter_lru_maxpages needs some thought.
Currently, it's decreased by one when less clean pages were required by
backends than expected, and increased otherwise. Exponential smoothing
or something similar seems like the natural choice to me.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com