Reposted, I did not see the mail come through...
(Lists working ok?)

Hi everybody,

I have started testing PostgreSQL on windows. Now that I have managed to
import a dump of one of our production databases to postgres on my notebook,
there is one issue that came up immediatly after running VACUUM FULL:

pgwriter is keeping my disks busy and disturbs me :-(. The more I like
pgwriter for a production system, the less I like it on my desktop during
developement.

So I thought I would just be able to disable this, but that seems not the
case.

#bgwriter_delay = 200  # 10-5000 milliseconds
#bgwriter_percent = 1  # 1-100% of dirty buffers
#bgwriter_maxpages = 100 # 1-1000 buffers max at once

bgwriter_percent can't be set to 0, neither can bgwriter_maxpages be, nor
can I set bgwriter_delay to something like 15 or 30 minutes...

For sympathy or pity for developers who are using postgres on a laptop or
desktop system, I would suggest that this is classified as a "convenience
bug" and fixed in a reasonable way.

Best Regards,
Michael Paesold


Fri, 17 Sep 2004 21:46:05 +0200
"Michael Paesold" <mpaesold@gmx.at>
Disabling bgwriter on my notebook
"Michael Paesold" <mpaesold@gmx.at> writes:
> pgwriter is keeping my disks busy and disturbs me :-(. The more I like
> pgwriter for a production system, the less I like it on my desktop during
> developement.
> So I thought I would just be able to disable this, but that seems not the
> case.

You can't turn off the bgwriter process entirely anymore, because we
rely on it for checkpoints.  However I suppose there's no solid reason
not to allow bgwriter_delay to be set as high as checkpoint_timeout,
which would accomplish your goal of not spinning up the laptop disk very
often.

This reminds me of another issue I'd been meaning to bring up, which is
whether the default bgwriter settings are reasonable:

> #bgwriter_delay = 200  # 10-5000 milliseconds
> #bgwriter_percent = 1  # 1-100% of dirty buffers
> #bgwriter_maxpages = 100 # 1-1000 buffers max at once

ISTM that writing only 1% of dirty buffers per cycle isn't going to go
very far in terms of keeping the freelist clean.  I was wondering if the
default on that shouldn't be higher.  If 1% *is* a reasonable setting,
then don't we need finer granularity --- maybe the setting should be in
tenths of a percent?  Having the default right up against the end of the
possible range doesn't sound right.

Jan, you're probably the only one who's done any serious testing of
alternatives for this --- what do you think?

			regards, tom lane

Fri, 17 Sep 2004 16:14:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disabling bgwriter on my notebook 
Tom Lane wrote:
> You can't turn off the bgwriter process entirely anymore, because we
> rely on it for checkpoints.  However I suppose there's no solid reason
> not to allow bgwriter_delay to be set as high as checkpoint_timeout,
> which would accomplish your goal of not spinning up the laptop disk very
> often.

Sounds reasonable. Please change it that way.

> This reminds me of another issue I'd been meaning to bring up, which is
> whether the default bgwriter settings are reasonable:
>
> > #bgwriter_delay = 200  # 10-5000 milliseconds
> > #bgwriter_percent = 1  # 1-100% of dirty buffers
> > #bgwriter_maxpages = 100 # 1-1000 buffers max at once

> ISTM that writing only 1% of dirty buffers per cycle isn't going to go
> very far in terms of keeping the freelist clean.  I was wondering if the
> default on that shouldn't be higher.  If 1% *is* a reasonable setting,
> then don't we need finer granularity --- maybe the setting should be in
> tenths of a percent?  Having the default right up against the end of the
> possible range doesn't sound right.

#checkpoint_timeout = 300 # range 30-3600, in seconds

First I thought this setting could be put in relation to the checkpoint
timeout directly, but it can't. It really depends on the "creation rate" of
dirty buffers.

Different approach: what bgwriter should do (if I am correct) is flush
enough dirty buffers between checkpoints so that there are only the least
recently touched ones left for syncing to disk.
(Anyway, how does the bgwriter select which dirty buffers to sync?)

So for testing it would be nice to know:
* how many dirty buffers are left for syncing during checkpoint?
* how many dirty buffers are synced between checkpoints
* how many unique disk pages are those; or how many disk pages have been
flushed more than once during that time

Is there currently debug output in the code that could tell me answers to
those questions? If so, I could try to some testing.

> Jan, you're probably the only one who's done any serious testing of
> alternatives for this --- what do you think?

Best Regards,
Michael Paesold


Fri, 17 Sep 2004 23:25:23 +0200
"Michael Paesold" <mpaesold@gmx.at>
Re: Disabling bgwriter on my notebook 
"Michael Paesold" <mpaesold@gmx.at> writes:
> Is there currently debug output in the code that could tell me answers to
> those questions?

There is some debug output available from the ARC code,
but I dunno if its output is actually useful ;-).  Try

http://developer.postgresql.org/docs/postgres/runtime-config.html#GUC-DEBUG-SHARED-BUFFERS

			regards, tom lane

Fri, 17 Sep 2004 17:33:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disabling bgwriter on my notebook 
Tom Lane wrote:

> There is some debug output available from the ARC code,
> but I dunno if its output is actually useful ;-).  Try
>
>
http://developer.postgresql.org/docs/postgres/runtime-config.html#GUC-DEBUG-SHARED-BUFFERS

"debug_shared_buffers (integer)

Number of seconds between ARC reports. If set greater than zero, emit ARC
statistics to the log every so many seconds. Zero (the default) disables
reporting."

It seems it doesn't really do, what it says. Output is only sent during
query execution, no output is sent when no query is active, so it's really
hard to say what happens to buffers during idle time.

Aside from that I don't believe that the output can answer questions about
the efficiency of bgwriter...

DEBUG:  ARC T1target=  194 B1len=  779 T1len=  180 T2len=  820 B2len=  208
DEBUG:  ARC total   =  99% B1hit=  18% T1hit=   6% T2hit=  75% B2hit=   0%
DEBUG:  ARC clean buffers at LRU       T1=     180 T2=     820

Anyone?

Regarding current default settings - at this page:
http://developer.postgresql.org/~wieck/vacuum_cost/

Jan, you used different settings for tests here. Can you explain your point
of view?

bgwriter_delay = 50     (now default 200)
bgwriter_percent = 2    (now default 1)
bgwriter_maxpages = 200 (now default 100)


Regards,
Michael Paesold


Sat, 18 Sep 2004 14:38:54 +0200
"Michael Paesold" <mpaesold@gmx.at>
Re: Disabling bgwriter on my notebook 
On 9/18/2004 8:38 AM, Michael Paesold wrote:
> Tom Lane wrote:
> 
>> There is some debug output available from the ARC code,
>> but I dunno if its output is actually useful ;-).  Try
>>
>>
> http://developer.postgresql.org/docs/postgres/runtime-config.html#GUC-DEBUG-SHARED-BUFFERS
> 
> "debug_shared_buffers (integer)
> 
> Number of seconds between ARC reports. If set greater than zero, emit ARC
> statistics to the log every so many seconds. Zero (the default) disables
> reporting."
> 
> It seems it doesn't really do, what it says. Output is only sent during
> query execution, no output is sent when no query is active, so it's really
> hard to say what happens to buffers during idle time.

Right, it only emits those log lines when there is actually query 
activity because the output is generated by a backend and not by the 
background writer itself. Maybe we should change that.

> 
> Aside from that I don't believe that the output can answer questions about
> the efficiency of bgwriter...
> 
> DEBUG:  ARC T1target=  194 B1len=  779 T1len=  180 T2len=  820 B2len=  208
> DEBUG:  ARC total   =  99% B1hit=  18% T1hit=   6% T2hit=  75% B2hit=   0%
> DEBUG:  ARC clean buffers at LRU       T1=     180 T2=     820

Look at the last line about clean buffers at LRU. This shows that you 
currently don't have ANY dirty buffers, as the number of clean buffers 
in T1 and T2 is identical to the two queue lengths.

The bgwriter always flushes the oldest dirty buffers, and every buffer 
touched (hit or faulted in). The output above doesn't tell you how many 
buffers are really dirty. But if the system is under load, that is 
pretty much the same as the distance between those numbers.

> 
> Anyone?
> 
> Regarding current default settings - at this page:
> http://developer.postgresql.org/~wieck/vacuum_cost/
> 
> Jan, you used different settings for tests here. Can you explain your point
> of view?
> 
> bgwriter_delay = 50     (now default 200)
> bgwriter_percent = 2    (now default 1)
> bgwriter_maxpages = 200 (now default 100)

Just what I was having the best TPC-C results with.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Sun, 19 Sep 2004 21:46:20 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Disabling bgwriter on my notebook
Jan Wieck" <JanWieck@Yahoo.com>
> > Aside from that I don't believe that the output can answer questions
about
> > the efficiency of bgwriter...
> >
> > DEBUG:  ARC T1target=  194 B1len=  779 T1len=  180 T2len=  820 B2len=
208
> > DEBUG:  ARC total   =  99% B1hit=  18% T1hit=   6% T2hit=  75% B2hit=
0%
> > DEBUG:  ARC clean buffers at LRU       T1=     180 T2=     820
>
> Look at the last line about clean buffers at LRU. This shows that you
> currently don't have ANY dirty buffers, as the number of clean buffers
> in T1 and T2 is identical to the two queue lengths.

Ah, now suddenly the output seems much clearer. Thanks! :-)

> The bgwriter always flushes the oldest dirty buffers, and every buffer
> touched (hit or faulted in). The output above doesn't tell you how many
> buffers are really dirty. But if the system is under load, that is
> pretty much the same as the distance between those numbers.

That would be nice, since analysing ARC/bgwriter using the logs would be
much easier, if it really wrote those in constant intervals independent of
backend activity.

> > bgwriter_delay = 50     (now default 200)
> > bgwriter_percent = 2    (now default 1)
> > bgwriter_maxpages = 200 (now default 100)
>
> Just what I was having the best TPC-C results with.

And how were the default values in chosen? Educated guesses?

Best Regards,
Michael Paesold


Mon, 20 Sep 2004 08:02:07 +0200
"Michael Paesold" <mpaesold@gmx.at>
Re: Disabling bgwriter on my notebook
On 9/20/2004 2:02 AM, Michael Paesold wrote:

>> The bgwriter always flushes the oldest dirty buffers, and every buffer
>> touched (hit or faulted in). The output above doesn't tell you how many
>> buffers are really dirty. But if the system is under load, that is
>> pretty much the same as the distance between those numbers.

Hmmm, I meant to say that the touched buffers are always put at the 
other end of the queue. Jetlag must have swallowed that.

> 
> That would be nice, since analysing ARC/bgwriter using the logs would be
> much easier, if it really wrote those in constant intervals independent of
> backend activity.
> 
>> > bgwriter_delay = 50     (now default 200)
>> > bgwriter_percent = 2    (now default 1)
>> > bgwriter_maxpages = 200 (now default 100)
>>
>> Just what I was having the best TPC-C results with.
> 
> And how were the default values in chosen? Educated guesses?

I am not 100% sure how those came to pass. But certainly not under the 
assumption that the default PG install is for a busy server with a 
medium to high update rate.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Mon, 20 Sep 2004 15:11:40 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Disabling bgwriter on my notebook
Jan Wieck <JanWieck@Yahoo.com> writes:
>>> bgwriter_delay = 50     (now default 200)
>>> bgwriter_percent = 2    (now default 1)
>>> bgwriter_maxpages = 200 (now default 100)

> Just what I was having the best TPC-C results with.

I'm a bit hesitant to reduce the default bgwriter_delay, since AFAICS
that will result in a direct increase in the CPU overhead incurred.
(It'd be nice if the bgwriter didn't have to scan through *all* the
buffers on every cycle ...)

If we keep the delay at 200 then the 50/2 numbers would be roughly
equivalent to 200/8, that is flush 8% every 200ms.  I'm inclined to
propose 5% as the default bgwriter_percent --- what do you think?

We need not change the maxpages default since it wouldn't matter until
you had upwards of 2000 dirty buffers, which to me would indicate that
you had better be raising the other parameters anyway (and you could
never get there at all with the default shared_buffers setting).

			regards, tom lane

Mon, 20 Sep 2004 15:25:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disabling bgwriter on my notebook 

What about the original request to turn off the bgwriter?  Would setting
the delay to a very high value do that?

---------------------------------------------------------------------------

Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >>> bgwriter_delay = 50     (now default 200)
> >>> bgwriter_percent = 2    (now default 1)
> >>> bgwriter_maxpages = 200 (now default 100)
> 
> > Just what I was having the best TPC-C results with.
> 
> I'm a bit hesitant to reduce the default bgwriter_delay, since AFAICS
> that will result in a direct increase in the CPU overhead incurred.
> (It'd be nice if the bgwriter didn't have to scan through *all* the
> buffers on every cycle ...)
> 
> If we keep the delay at 200 then the 50/2 numbers would be roughly
> equivalent to 200/8, that is flush 8% every 200ms.  I'm inclined to
> propose 5% as the default bgwriter_percent --- what do you think?
> 
> We need not change the maxpages default since it wouldn't matter until
> you had upwards of 2000 dirty buffers, which to me would indicate that
> you had better be raising the other parameters anyway (and you could
> never get there at all with the default shared_buffers setting).
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 20 Sep 2004 18:28:12 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Disabling bgwriter on my notebook
Bruce Momjian wrote:

> What about the original request to turn off the bgwriter?  Would setting
> the delay to a very high value do that?

Setting the delay to the same value as checkpoint_timeout would effectivly
disable bgwriter between checkpoints. This is what I wanted.
The problem is that this is currently not possible, since bgwriter_delay has
an upper limit of 5 seconds. Tom said this could be fixed.

Perhaps add as open item?

Best Regards,
Michael Paesold


Tue, 21 Sep 2004 11:04:41 +0200
"Michael Paesold" <mpaesold@gmx.at>
Re: Disabling bgwriter on my notebook
"Michael Paesold" <mpaesold@gmx.at> writes:
> Setting the delay to the same value as checkpoint_timeout would effectivly
> disable bgwriter between checkpoints. This is what I wanted.
> The problem is that this is currently not possible, since bgwriter_delay has
> an upper limit of 5 seconds. Tom said this could be fixed.

> Perhaps add as open item?

The open item in my mind is adjustments to *all* the bgwriter
parameters.

			regards, tom lane

Tue, 21 Sep 2004 09:47:05 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Disabling bgwriter on my notebook 

Added to open items list:

	* adjust bgwriter defaults, allow disabling


---------------------------------------------------------------------------

Tom Lane wrote:
> "Michael Paesold" <mpaesold@gmx.at> writes:
> > Setting the delay to the same value as checkpoint_timeout would effectivly
> > disable bgwriter between checkpoints. This is what I wanted.
> > The problem is that this is currently not possible, since bgwriter_delay has
> > an upper limit of 5 seconds. Tom said this could be fixed.
> 
> > Perhaps add as open item?
> 
> The open item in my mind is adjustments to *all* the bgwriter
> parameters.
> 
> 			regards, tom lane
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 21 Sep 2004 10:22:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Disabling bgwriter on my notebook
On Tue, Sep 21, 2004 at 10:22:57AM -0400, Bruce Momjian wrote:

> Added to open items list:
> 
> 	* adjust bgwriter defaults, allow disabling

Now that the oid2name patch has been applied, you can remove the
oid2name item from the open items list.

Additionaly, there's a patch in the patch queue to close the "xid in log
line prefix" item.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Dios hizo a Adï¿½n, pero fue Eva quien lo hizo hombre.


Tue, 21 Sep 2004 11:15:55 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Open items (was Re: Disabling bgwriter on my notebook)
Alvaro Herrera wrote:
> On Tue, Sep 21, 2004 at 10:22:57AM -0400, Bruce Momjian wrote:
> 
> > Added to open items list:
> > 
> > 	* adjust bgwriter defaults, allow disabling
> 
> Now that the oid2name patch has been applied, you can remove the
> oid2name item from the open items list.

Done.

> Additionaly, there's a patch in the patch queue to close the "xid in log
> line prefix" item.

Yep.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 21 Sep 2004 19:42:19 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Open items (was Re: Disabling bgwriter on my notebook)