DBM1 asynch write buffer flush

Jim Lewandowski

DBM1 asynch write buffer flush
When DBM1 takes a checkpoint and has to flush buffers, is there any
specific order to buffer flushing? Does DBM1 start at the lowest BP#
and go upwards?

The reason for this is that I would like to put the "least
important" writes in a BP# that is taken care of last by DBM1 such that
the more important writes finish first.

BTW, I had asked previously (months ago) about buffer LRU internals and
specifically wanted to know in what circumstances a thread could be
waiting on an ASYNCHronous write I/O. As DBM1 is flushing a modified
buffer, is a thread allow a share lock on that page during (while in
flight) the asynch write process? Or, does DBM1 place an exclusive lock
during the write?

Jim Lewandowski

Leslie Pendlebury-Bowe

Re: DBM1 asynch write buffer flush
(in response to Jim Lewandowski)
Jim
Interesting question.

BP writes .. I may know a man who knows ..

I would assume that when DB2 has been told that this page is now
modifed and can be written to DASD the page then becomes available to
be read .... I would be very interested to know if this is the case
and the same man above may know the answer to that aswell .. I will
get back to you on it.

regards

Leslie
Fastrack Information Systems
DB2 OS390 SAP


______________________________ Reply Separator _________________________________
Subject: DBM1 asynch write buffer flush
Author: Jim Lewandowski <[login to unmask email]> at Internet
Date: 10/6/99 8:16 AM


When DBM1 takes a checkpoint and has to flush buffers, is there any
specific order to buffer flushing? Does DBM1 start at the lowest BP#
and go upwards?

The reason for this is that I would like to put the "least
important" writes in a BP# that is taken care of last by DBM1 such that
the more important writes finish first.

BTW, I had asked previously (months ago) about buffer LRU internals and
specifically wanted to know in what circumstances a thread could be
waiting on an ASYNCHronous write I/O. As DBM1 is flushing a modified
buffer, is a thread allow a share lock on that page during (while in
flight) the asynch write process? Or, does DBM1 place an exclusive lock
during the write?

Jim Lewandowski

Jim Lewandowski

Re: DBM1 asynch write buffer flush
(in response to Richard Humphris)
So, if a modified, uncommitted page is sitting in the bufferpool and
it's checkpoint time, DBM1 does NOT place any lock whatsoever on that
page, correct?

If we are getting waits on asynch writes, is the ONLY possibility that
our log buffers are getting full at that precise time?

Very interesting.

Jim Lewandowski


> Generally, DB2 never waits on asynchronous write I/O's to your databases.
> However, the one case a write can cause a thread to wait is when all the log
> buffers are full. This is something that should happen as rarely as
> possible for the entire DB2 sub-system. IBM, in the Administrator's Guide,
> recommends your system programmer to specify the maximum value for OUTBUFF
> (up to 4000KB) to prevent buffer shortages. In fact, IBM's exact words are:
> "The buffer MUST be large enough to prevent buffer shortages in order to
> decrease the number of forced I/O operations (forced because there are no
> more buffers) or wait conditions."

[login to unmask email]

Re: DBM1 asynch write buffer flush
(in response to Leslie Pendlebury-Bowe)
Just because a page is on the "write list" doesn't mean it can't be read, or
updated. This will depend on its lock status at the time of the request. And
of course the page will remain in the BP until it is stolen (no longer "in
use").

Rick Creech

Re: DBM1 asynch write buffer flush
(in response to truman.g.brown@BELLATLANTIC.COM)
The reason that asynchronous writes are called asynchronous writes is
because there is no waiting on locks or anything else by the transaction.
Once a commit has taken place, the locks are released and data pages may
stay in the buffer for a long period of time thereafter. The integrity of
the data is assured because the log records are written synchronously at
commit.

Rick Creech


>From: Jim Lewandowski <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: DBM1 asynch write buffer flush
>Date: Wed, 6 Oct 1999 08:16:58 -0500
>
>When DBM1 takes a checkpoint and has to flush buffers, is there any
>specific order to buffer flushing? Does DBM1 start at the lowest BP#
>and go upwards?
>
>The reason for this is that I would like to put the "least
>important" writes in a BP# that is taken care of last by DBM1 such that
>the more important writes finish first.
>
>BTW, I had asked previously (months ago) about buffer LRU internals and
>specifically wanted to know in what circumstances a thread could be
>waiting on an ASYNCHronous write I/O. As DBM1 is flushing a modified
>buffer, is a thread allow a share lock on that page during (while in
>flight) the asynch write process? Or, does DBM1 place an exclusive lock
>during the write?
>
>Jim Lewandowski

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

Richard Humphris

Re: DBM1 asynch write buffer flush
(in response to Rick Creech)
I think you'll be happy to know that all your pages that DB2 want's to flush
from the buffer are important to DB2. Even if the system crashes before
writting out your important pages, DB2 knows they have to be written because
they were already written to the active log (and all pages committed after
the last completed checkpoint will be written when DB2 comes back up; and
all rollback'ed changes and/or incompleted changes will be rolled back). In
addition, locking has nothing to do with the flushing of buffer pages; locks
are removed at commit and/or rollback, not when the page is physically
written out. Of course, if the page/row being written still has a update
lock on it you'll still be locked out. If the page/row was committed but
now the buffers are flushing, your SHR lock will just work normally.

Generally, DB2 never waits on asynchronous write I/O's to your databases.
However, the one case a write can cause a thread to wait is when all the log
buffers are full. This is something that should happen as rarely as
possible for the entire DB2 sub-system. IBM, in the Administrator's Guide,
recommends your system programmer to specify the maximum value for OUTBUFF
(up to 4000KB) to prevent buffer shortages. In fact, IBM's exact words are:
"The buffer MUST be large enough to prevent buffer shortages in order to
decrease the number of forced I/O operations (forced because there are no
more buffers) or wait conditions." Wait conditions are when your thread
WILL be forced to wait for a asynchronous I/O to the active log to complete
(so that a buffer is freed up so that your log update can be inserted into
the freshly emptied active log buffer). Generally, this number is zero at
our shop for days at a time; ie. we rarely see active log buffer shortages.
And even when you do see them, it should be a very low number.

> -----Original Message-----
> From: Jim Lewandowski [SMTP:[login to unmask email]
> Sent: Wednesday, October 06, 1999 8:17 AM
> To: [login to unmask email]
> Subject: DBM1 asynch write buffer flush
>
> When DBM1 takes a checkpoint and has to flush buffers, is there any
> specific order to buffer flushing? Does DBM1 start at the lowest BP#
> and go upwards?
>
> The reason for this is that I would like to put the "least
> important" writes in a BP# that is taken care of last by DBM1 such that
> the more important writes finish first.
>
> BTW, I had asked previously (months ago) about buffer LRU internals and
> specifically wanted to know in what circumstances a thread could be
> waiting on an ASYNCHronous write I/O. As DBM1 is flushing a modified
> buffer, is a thread allow a share lock on that page during (while in
> flight) the asynch write process? Or, does DBM1 place an exclusive lock
> during the write?
>
> Jim Lewandowski

Jim Lewandowski

Re: DBM1 asynch write buffer flush
(in response to sanjay jain)
sanjay jain wrote:

> Asynch I/O wait (read/write) are caused by delay at the channel/DASD level.
> If it is a sizable percentage of your total elapsed time, you should sit
> with DASD people and figure out a better dataset placement for db2
> tables/indexes.


So, where do we stand. How can an active thread wait on an asynchronous
I/O when:

A) there should be no locks placed on that page by DBM1 during the
asynch write I/O process?

B) if there WERE a share lock on that page placed there by DBM1, should
this thread want to UPDATE that page, yes a wait would make sense but
you would think this would be attributed to LOCK WAIT, not asynch write
I/O wait

C) this happens maybe a couple of dozen times a day

It's by no means a problem but I just wanted to get more familiar with
the asynch write I/O process

Jim Lewandowski

Jim Lewandowski

Re: DBM1 asynch write buffer flush
(in response to Jim Lewandowski)
Any thoughts on this from anyone?

Jim Lewandowski

> When DBM1 takes a checkpoint and has to flush buffers, is there any
> specific order to buffer flushing? Does DBM1 start at the lowest BP#
> and go upwards?
>
> The reason for this is that I would like to put the "least
> important" writes in a BP# that is taken care of last by DBM1 such that
> the more important writes finish first.
>
> BTW, I had asked previously (months ago) about buffer LRU internals and
> specifically wanted to know in what circumstances a thread could be
> waiting on an ASYNCHronous write I/O. As DBM1 is flushing a modified
> buffer, is a thread allow a share lock on that page during (while in
> flight) the asynch write process? Or, does DBM1 place an exclusive lock
> during the write?
>
> Jim Lewandowski

Joel Goldstein

Re: DBM1 asynch write buffer flush
(in response to Jim Lewandowski)
Message text written by DB2 Data Base Discussion List
>So, if a modified, uncommitted page is sitting in the bufferpool and
it's checkpoint time, DBM1 does NOT place any lock whatsoever on that
page, correct?

If we are getting waits on asynch writes, is the ONLY possibility that
our log buffers are getting full at that precise time?
Very interesting.
Jim Lewandowski>

> Generally, DB2 never waits on asynchronous write I/O's to your databases.
> However, the one case a write can cause a thread to wait is when all the
log
> buffers are full. This is something that should happen as rarely as
> possible for the entire DB2 sub-system. IBM, in the Administrator's
Guide,
> recommends your system programmer to specify the maximum value for
OUTBUFF
> (up to 4000KB) to prevent buffer shortages. In fact, IBM's exact words
are:
> "The buffer MUST be large enough to prevent buffer shortages in order to
> decrease the number of forced I/O operations (forced because there are no
> more buffers) or wait conditions."<

+++++++++++++++++++++++++++++++++++++++++++++
Log buffer full causes the entire Db2 system to stop... until
writes have completed and buffers are available. I don't think you
should see an application "asynch" wait from a log buffer full situation.

If you are running out of log buffers, it should be obvious from the
statistics reports, in the logging section.
Regards,
Joel
|=======
| Joel Goldstein
| Responsive Systems Company
|=======
| WEB Site
| www.responsivesystems.com
|=======
| Buffer Pool Tool for DB2 & DASD/Xpert for DB2
| >>> The Answers for your performance problems <<<
|-------------------------------------------------------
| Tel.(800) DB2-EXPErt Tel.(800) 322-3973
| Tel.(732) 972-1261 Fax.(732) 972-9416
|=======

sanjay jain

Re: DBM1 asynch write buffer flush
(in response to Joel Goldstein)
Richard,

I beg to differ from your interpretation of 'Asynch I/O wait'.

1) It is not due to shortage of log output buffer. Any delay in writing to
OUTBUFF is part of 'Service task wait time' in the accounting report, not
Asynch I/O wait time.

2) The statement you quoted from IBM manual " specify the maximum value for
OUTBUFF (up to 4000KB) to prevent buffer shortages. In fact, IBM's exact
words are:
>"The buffer MUST be large enough to prevent buffer shortages in order to
>decrease the number of forced I/O operations (forced because there are no
>more buffers) or wait conditions."

merely says that your OUTPUT buffer for writing logs should be large enough
to handle peak time load, otherwise OUTBUFF may be reached resulting in wait
on log write. In this case all the threads doing any update activity (hence
log records created) will just wait for the OUTBUFF to be cleared up. This
will show up as 'service task wait time in the accounting report'. IBM
recommends that the one must keep OUTBUFF value as large as you can afford,
or the other way is to keep WRTHRSH low to trigger frequent asynch write of
output log buffers to active log datasets.

Asynch I/O wait (read/write) are caused by delay at the channel/DASD level.
If it is a sizable percentage of your total elapsed time, you should sit
with DASD people and figure out a better dataset placement for db2
tables/indexes.


Sanjay
/* all disclaimer apply */
-----Original Message-----
From: Humphris,Richard,CNA Insurance <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: [login to unmask email] <[login to unmask email]>
Date: Thursday, October 07, 1999 10:24 AM
Subject: Re: DBM1 asynch write buffer flush


>I think you'll be happy to know that all your pages that DB2 want's to
flush
>from the buffer are important to DB2. Even if the system crashes before
>writting out your important pages, DB2 knows they have to be written
because
>they were already written to the active log (and all pages committed after
>the last completed checkpoint will be written when DB2 comes back up; and
>all rollback'ed changes and/or incompleted changes will be rolled back).
In
>addition, locking has nothing to do with the flushing of buffer pages;
locks
>are removed at commit and/or rollback, not when the page is physically
>written out. Of course, if the page/row being written still has a update
>lock on it you'll still be locked out. If the page/row was committed but
>now the buffers are flushing, your SHR lock will just work normally.
>
>Generally, DB2 never waits on asynchronous write I/O's to your databases.
>However, the one case a write can cause a thread to wait is when all the
log
>buffers are full. This is something that should happen as rarely as
>possible for the entire DB2 sub-system. IBM, in the Administrator's Guide,
>recommends your system programmer to specify the maximum value for OUTBUFF
>(up to 4000KB) to prevent buffer shortages. In fact, IBM's exact words
are:
>"The buffer MUST be large enough to prevent buffer shortages in order to
>decrease the number of forced I/O operations (forced because there are no
>more buffers) or wait conditions." Wait conditions are when your thread
>WILL be forced to wait for a asynchronous I/O to the active log to complete
>(so that a buffer is freed up so that your log update can be inserted into
>the freshly emptied active log buffer). Generally, this number is zero at
>our shop for days at a time; ie. we rarely see active log buffer shortages.
>And even when you do see them, it should be a very low number.
>
>> -----Original Message-----
>> From: Jim Lewandowski [SMTP:[login to unmask email]
>> Sent: Wednesday, October 06, 1999 8:17 AM
>> To: [login to unmask email]
>> Subject: DBM1 asynch write buffer flush
>>
>> When DBM1 takes a checkpoint and has to flush buffers, is there any
>> specific order to buffer flushing? Does DBM1 start at the lowest BP#
>> and go upwards?
>>
>> The reason for this is that I would like to put the "least
>> important" writes in a BP# that is taken care of last by DBM1 such that
>> the more important writes finish first.
>>
>> BTW, I had asked previously (months ago) about buffer LRU internals and
>> specifically wanted to know in what circumstances a thread could be
>> waiting on an ASYNCHronous write I/O. As DBM1 is flushing a modified
>> buffer, is a thread allow a share lock on that page during (while in
>> flight) the asynch write process? Or, does DBM1 place an exclusive lock
>> during the write?
>>
>> Jim Lewandowski

Venkat (PCA) Pillay

Re: DBM1 asynch write buffer flush
(in response to Jim Lewandowski)
> As DBM1 is flushing a modified
> > buffer, is a thread allow a share lock on that page during (while in
> > flight) the asynch write process? Or, does DBM1 place an exclusive lock
> > during the write?
> >
>
[Pillay, Venkat (PCA)]

Jim
While DBM1 writes the pages, there is no impact on locks
held on that page. Locks are maintained by IRLM. Here is an example -> Issue
an update statement from SPUFI with AUTOCOMMIT NO and then wait for two
checkpoints for the uncommitted page to be written to DASD (you could
confirm this by running DSN1PRNT or doing REPRO on the DB2 VSAM). The lock
is maintained till application want it and has no impact wether page is
written by DBM1 or not.

HTH
Pillay