From: Heikki Linnakangas <heikki@enterprisedb.com>
To: ITAGAKI Takahiro <itagaki.takahiro@oss.ntt.co.jp>
Cc: pgsql-patches@postgresql.org
Subject: Re: Automatic adjustment of bgwriter_lru_maxpages
Date: Thu, 12 Apr 2007 11:57:17 +0100
Message-ID: <461E108D.6080709@enterprisedb.com> (raw)
In-Reply-To: <20070305131046.C4A6.ITAGAKI.TAKAHIRO@oss.ntt.co.jp>
References: <20070227141958.638D.ITAGAKI.TAKAHIRO@oss.ntt.co.jp>
<20070227170613.GK29041@nasby.net>
<20070305131046.C4A6.ITAGAKI.TAKAHIRO@oss.ntt.co.jp>
ITAGAKI Takahiro wrote:
> "Jim C. Nasby" <jim@nasby.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, itagaki.takahiro@oss.ntt.co.jp, pgsql-patches@postgresql.org
Subject: Re: Automatic adjustment of bgwriter_lru_maxpages
In-Reply-To: <461E108D.6080709@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox