Thread: Disabling bgwriter on my notebook
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
"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
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
"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
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
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 #
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
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 #
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
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, Pennsylvania19073
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
"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
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, Pennsylvania19073
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.
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, Pennsylvania19073