IDS Forum

Re: Checkpoint Duration

Posted By: Art Kagel
Date: Wednesday, 8 April 2009, at 11:28 a.m.

In Response To: Checkpoint Duration (PANJIE KITT)

You do not show the ONCONFIG setting for the CLEANERS parameter. If LRUS is
set to 256, CLEANERS should be set to 256 also (but at least the greater of
128 or the number of chunks). With too few page CLEANERS the write
operations during a checkpoint will take longer and the critical section
blocks discussed below remain in place until all dirty buffers have been
flushed to disk.

Checkpoints will wait for any user session that is in a critical section.
There are critical sections at various points within INSERT, UPDATE, and
DELETE operations mostly so large numbers of INSERTs will increase the
likelyhood that a checkpoint will have to wait for one or more sessions to
exit their critical section code. Inversely, the beginning of a checkpoint
will prevent any session from entereing a new critical section with a latch
block that does not time out.

You are seeing expected behavior. In later releases of IDS (9.40 and later)
you would be able to set LRU_MIN/MAX_DIRTY to fractional values and reduce
the number of dirty buffers which would improve the checkpoint duration once
the critical sections had been cleared, but there's nothing to do about the
critical section problem except to upgrade to IDS 11.50 which now has
completely non-blocking checkpoints. Note that IDS 7.31 is out-of-support!

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

On Tue, Apr 7, 2009 at 9:54 PM, PANJIE KITT <panjiejump@gmail.com> wrote:

> I have an IDS, 7.31.FD3 as a database server for Java GUI client
> application.
> There were many times that the database checkpoint duration was very long
> while concurrent users were not as many as usual (there were some working
> days
> that the users work load was low). As checked in the message log, there was
> no
> error report or anything. I tried to check if there was any abnormal
> session
> working on the database but the session activities found seemed to be the
> user
> normal processes. The only thing I found out was there were INSERT
> transactions processing while the onstat output header said that
> transactions
> was blocked by checkpoint request. Also, the client applications got slow
> responds too.
>
> At first I thought lots of INSERT transactions processing at the same time
> caused this to happened but the strange thing is there were many times that
> it
> does not happen when there were many concurrent users inserting large
> amount
> of data but when there were less concurrent users inserting smaller amount
> of
> data the problem occurs!
>
> I also checked with my engineer if there were failed disks and the answer I
> got was all disks are working just find too. Could you possibly give me an
> advice on how to figure out what causes the problem, please?
>
> onconfig <partial>
> ==============
> # Shared Memory Parameters
>
> LOCKS 1000000 # Maximum number of locks
> BUFFERS 320000 # Maximum number of shared buffers
> NUMAIOVPS 9 # Number of IO vps
> PHYSBUFF 512 # Physical log buffer size (Kbytes)
> LOGBUFF 512 # Logical log buffer size (Kbytes)
> LOGSMAX 300 # Maximum number of logical log files
> CLEANERS 96 # Number of buffer cleaner processes
> SHMBASE 0xa000000 # Shared memory base address
> SHMVIRTSIZE 5120000 # initial virtual shared memory segment size
> SHMADD 16384 # Size of new shared memory segments
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 400 # Check point interval (in sec)
> LRUS 256 # Number of LRU queues
> LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
> LTXHWM 40 # Long transaction high water mark
> LTXEHWM 60 # Long transaction high water mark
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 64 # Stack size (Kbytes)
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636920273882cc904670cc92f

Messages In This Thread

• Checkpoint Duration
  PANJIE KITT -- Tuesday, 7 April 2009, at 9:54 p.m.
  • AW: Checkpoint Duration
    Andreas.KUTSCHE@spar.at -- Wednesday, 8 April 2009, at 3:10 a.m.
    • Re: AW: Checkpoint Duration
      PANJIE KITT -- Wednesday, 8 April 2009, at 11:59 p.m.
  • Re: Checkpoint Duration
    Art Kagel -- Wednesday, 8 April 2009, at 11:28 a.m.
    • Re: Checkpoint Duration
      PANJIE KITT -- Thursday, 9 April 2009, at 2:08 a.m.
      • Re: Checkpoint Duration
        PANJIE KITT -- Thursday, 9 April 2009, at 2:21 a.m.
        • AW: Checkpoint Duration
          Andreas.KUTSCHE@spar.at -- Thursday, 9 April 2009, at 2:51 a.m.
          • Re: AW: Checkpoint Duration
            PANJIE KITT -- Thursday, 9 April 2009, at 6:16 a.m.
  • Re: Checkpoint Duration
    DAVE GRIFFEN -- Wednesday, 8 April 2009, at 4:21 p.m.
    • Re: Checkpoint Duration
      PANJIE KITT -- Thursday, 9 April 2009, at 2:05 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.