LOGLOAD and checkpoint frequencies

Roger Miller

LOGLOAD and checkpoint frequencies
There have been many changes over the various releases of DB2. I'll try to
note the key ones, but there are two concerns for checkpoints:
cost and disruption of the checkpoints
restart time for the subsystem after a crash

While 400 checkpoints per hour is too many, ten or fifteen minutes between
checkpoints is likely to cause problems in restart time, especially if you
use that for the batch update timing. After a normal quiesce stop, there
is no work to do. The concern is for a DB2 abend or modify irlm,abend.

I find that the costs and disruption of the checkpoints are often
overstated. DB2 checkpoints do not prevent work from proceeding.
Having a large LOGLOAD, with large bufferpools and default thresholds can
cause enough IO to make the checkpoint disruptive. Some customers increased
the LOGLOAD value and made the checkpoints less frequent, but more
disruptive, then corrected the situation. I've seen better performance and
availability by reducing the amount written and increasing the checkpoint
frequency.

The parameter with the most leverage for DB2 restart is the LOGLOAD, which
controls the DB2 checkpoint interval. The default is now 50,000. Some
customers have set the value to 1,000,000 or more, but find that the time
to restart DB2 is affected. Reducing the parameter from about 300,000 to
50,000 reduced the restart time from as much as 30 minutes to under 10
minutes in one customer situation, with no noticeable impact on
performance. The customer was getting a checkpoint every minute and did
not impact performance or availability. The key factor to watch in seeing
if LOGLOAD can be reduced is the write efficiency at DB2 checkpoints. If
data can be written using the deferred write threshold or the vertical
deferred write threshold, then there is less work to perform at each
checkpoint. Writing data more frequently in smaller amounts is also
helpful for the non-volatile storage in storage controllers.

Minimizing the number of data sets to be opened can also help restart time.
You control the number of data sets with the DSMAX parameter and also by
having the data written. GRS tuning is also important for opening data
sets.

Rough rule of thumb: Version 4 or 5, non-data sharing: Restart will take
.1 to .3 seconds per data set which needs to be opened plus 5 minutes for
each 100,000 records in LOGLOAD. Having a fast IO subsystem with cache
storage can make the time per data set closer to .1 second. Later versions
of DFSMSdfp also improve times per data set.

There have been many changes over the releases, with V4 shifting from
reading two checkpoint intervals to one and providing the data sharing
option. V5 data sharing restarts were much improved. V6 makes a similar
improvement for non-data-sharing, improves the log read, and helps with a
more consistent restart time even when there is a long running unit of
recovery. V6 also provides a command to alter LOGLOAD while DB2 is up.

Roger Miller


"Hayes, Chris" <[login to unmask email]>@RYCI.COM> on 12/21/99 08:04:55 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject:



While looking through some reports, I noticed that one of our production
DB2
subsystems is checkpointing over 400 times an hour during certain times. I
checked our checkpoint frequency parameter and it is set at 50,000. IBM
recommends using 25,000 to 500,000 in their Installation Guide. Am I
correct in thinking that 400 times an hour is excessive? Should I be
concerned about raising the threshold? IBM's Installation Guide mentions
that start-up time may be excessive (over 30 minutes) if you specify a
value
over 300,000 and you terminate DB2 without a quiesce. Is this a real
concern or should DB2 always be stopped with a quiesce?








[login to unmask email]

Re: LOGLOAD and checkpoint frequencies
(in response to Roger Miller)
I am fully convinced with good indepth analysis given by Roger about the
high restart time and mainly about using VDWQT and DWQT more smartly which
reduce the work to be performed during the check point.
Regarding the staying of the pages in buffer after getting written to DASD
during the checkpoint ,DB2 marks that pages as available pages and that can
be stealed for new use.

Sanjeev




Roger Miller <[login to unmask email]>@RYCI.COM> on 12/21/99 10:38:56 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: Re: LOGLOAD and checkpoint frequencies


There have been many changes over the various releases of DB2. I'll try to
note the key ones, but there are two concerns for checkpoints:
cost and disruption of the checkpoints
restart time for the subsystem after a crash

While 400 checkpoints per hour is too many, ten or fifteen minutes between
checkpoints is likely to cause problems in restart time, especially if you
use that for the batch update timing. After a normal quiesce stop, there
is no work to do. The concern is for a DB2 abend or modify irlm,abend.

I find that the costs and disruption of the checkpoints are often
overstated. DB2 checkpoints do not prevent work from proceeding.
Having a large LOGLOAD, with large bufferpools and default thresholds can
cause enough IO to make the checkpoint disruptive. Some customers increased
the LOGLOAD value and made the checkpoints less frequent, but more
disruptive, then corrected the situation. I've seen better performance and
availability by reducing the amount written and increasing the checkpoint
frequency.

The parameter with the most leverage for DB2 restart is the LOGLOAD, which
controls the DB2 checkpoint interval. The default is now 50,000. Some
customers have set the value to 1,000,000 or more, but find that the time
to restart DB2 is affected. Reducing the parameter from about 300,000 to
50,000 reduced the restart time from as much as 30 minutes to under 10
minutes in one customer situation, with no noticeable impact on
performance. The customer was getting a checkpoint every minute and did
not impact performance or availability. The key factor to watch in seeing
if LOGLOAD can be reduced is the write efficiency at DB2 checkpoints. If
data can be written using the deferred write threshold or the vertical
deferred write threshold, then there is less work to perform at each
checkpoint. Writing data more frequently in smaller amounts is also
helpful for the non-volatile storage in storage controllers.

Minimizing the number of data sets to be opened can also help restart time.
You control the number of data sets with the DSMAX parameter and also by
having the data written. GRS tuning is also important for opening data
sets.

Rough rule of thumb: Version 4 or 5, non-data sharing: Restart will take
.1 to .3 seconds per data set which needs to be opened plus 5 minutes for
each 100,000 records in LOGLOAD. Having a fast IO subsystem with cache
storage can make the time per data set closer to .1 second. Later versions
of DFSMSdfp also improve times per data set.

There have been many changes over the releases, with V4 shifting from
reading two checkpoint intervals to one and providing the data sharing
option. V5 data sharing restarts were much improved. V6 makes a similar
improvement for non-data-sharing, improves the log read, and helps with a
more consistent restart time even when there is a long running unit of
recovery. V6 also provides a command to alter LOGLOAD while DB2 is up.

Roger Miller


"Hayes, Chris" <[login to unmask email]>@RYCI.COM> on 12/21/99 08:04:55 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject:



While looking through some reports, I noticed that one of our production
DB2
subsystems is checkpointing over 400 times an hour during certain times. I
checked our checkpoint frequency parameter and it is set at 50,000. IBM
recommends using 25,000 to 500,000 in their Installation Guide. Am I
correct in thinking that 400 times an hour is excessive? Should I be
concerned about raising the threshold? IBM's Installation Guide mentions
that start-up time may be excessive (over 30 minutes) if you specify a
value
over 300,000 and you terminate DB2 without a quiesce. Is this a real
concern or should DB2 always be stopped with a quiesce?













James Wu

Re: Load
(in response to ssethi@LOT.TATASTEEL.COM)
You can convert the FLOAT to DECIMAL during the UNLOAD process (with
PARM('SQL' and SELECT). Then load the target table. For that amount of
data, you SHOULD use load instead of insert.

James Wu :-)

(847)646-5548
[login to unmask email]

> -----Original Message-----
> From: ajay kumar [SMTP:[login to unmask email]
> Sent: Wednesday, December 22, 1999 8:42 AM
> To: [login to unmask email]
> Subject:
>
> Hi..
>
> What is the limitation of mass insert..?
>
> We are inserting 236000 records, Record length is 630. When we use
> DSNTIAUL
> mass insert, it is idle no movement for more than 5 hrs. Is any other
> systems variables to be set.
>
> We cannot use LOAD utility. Why INSERT is using means, In source table the
> data is in float we want to convert to decimal,for that we used INSERT.
> For
> small table, it went thru but for biggrer table it is idle.
>
> I allocated sufficient priqty and SECQTY, interms of CYCLINDER.
> Can any one suggest how to avoid this problem...?
>
> Thank
> CICS Admin
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
>
>
>