IDS Forum

Re: Checkpoint causes Db to "freeze"

Posted By: Art Kagel
Date: Tuesday, 22 October 2013, at 10:36 a.m.

In Response To: Checkpoint causes Db to "freeze" (JACK PAPA)

There are two possibilities:
1) At the time the checkpoint starts there are sessions that are in a
critical code section and before the checkpoint can enter its critical
section. The engine will block all other sessions that request a critical
section until it is finished with that portion of the checkpoint setup.
This will, in turn, have to wait until any processing already in a
critical section have exited the protected code block in the engine and
released their critical section(s).

2) After the critical section of the checkpoint begins a hard checkpoint
will begin flushing dirty pages to disk using chunk writes. A "fuzzy"
checkpoint also has some mandatory IO and housekeeping to do at this point.
Once these operations are complete, the checkpoint releases its critical
section and the block on other sessions and everything goes back to normal
for the second half of the checkpoint. If these IO operations take a long
time, that will cause the engine to seem to pause or hang. Only read-only
queries can progress under these conditions.

There isn't much you can do about #1 except to examine your application
code to minimize the operations that may cause its server threads to
require a critical section. To minimize the length of the blocked portion
of a checkpoint, #2 above, you need to decrease the LRU_MIN/MAX_DIRTY
levels from 1,2 to say 0.5,1 or lower so that there isn't much IO required
at checkpoint time. You can also look for operations and conditions that
will required a hard checkpoint rather than a fuzzy one. (See the 9.40
manuals.)

Art

Art S. Kagel, Principal Consultant

Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. 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, Oct 22, 2013 at 10:21 AM, JACK PAPA <informix2009@gmail.com> wrote:

> Hi All,
>
> Has anyone encountered this issue? i'm running OLTP on an IDS 9.4 and AIX
> 5.
> I'm having problem on isolating the problem. When a checkpoint is being
> performed, the system hangs for 1-3 minutes.
>
> PHYSDBS physdbs0000 # Location (dbspace) of physical log
> PHYSFILE 100000 # Physical log file size (Kbytes)
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
>
> LRUS 64
> LRU_MAX_DIRTY 2.000000 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 1.000000 # LRU percent dirty end cleaning limit
> LOGBUFF 64
> CKPTINTVL 180 # Check point interval (in sec)
> RA_PAGES 32 # Number of pages to attempt to read ahead
> RA_THRESHOLD 16 # Number of pages left before next group
>
> ====================
>
> Prior to the "freeze", i'm seeing this large user process.
>
> => onstat -u|grep G-
> a5fb0308 G-BPX-- 1509649 user02 - a0ccdea0 0 1 734922 6256025
>
> => onstat -g sql 1509649
>
> IBM Informix Dynamic Server Version 9.40.UC5W2 -- On-Line -- Up 16 days
> 02:34:12 -- 2072192 Kbytes
>
> Sess SQL Current Iso Lock SQL ISAM F.E.
> Id Stmt type Database Lvl Mode ERR ERR Vers Explain
> 1509649 INSERT prd_db DR Not Wait 0 0 9.03 Off
>
> Current SQL statement :
> INSERT INTO table1 (col1, col2_num, col3_data) VALUES (?,?,?)
>
> Host variables :
>
> address type flags value
>
> ---------------------------------
>
> 0xa33b9020 INT 0x000 9587639
>
> 0xa33b9084 INT 0x000 1080
>
> 0xa33b90e8 CHAR 0x000 Travel constant 0.0000 19.4157 0.0374
>
> Last parsed SQL statement :
> SELECT * FROM tab2 WHERE tab2_id = ?
>
> FYI, DB is in "unbuf" and Fuzzy checkpoint ON.
>
> How can i make the DB eliminate the "Freeze". Should i decrease the
> LOGBUFF to
> 32? or should i set the CKPINTVL to 60 seconds?
>
> Please need your help.
>
> Thanks,
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001a11c3327a52882d04e955524f

Messages In This Thread

• Checkpoint causes Db to "freeze"
  JACK PAPA -- Tuesday, 22 October 2013, at 10:21 a.m.
  • Re: Checkpoint causes Db to "freeze"
    Keith Simmons -- Tuesday, 22 October 2013, at 10:34 a.m.
    • Re: Checkpoint causes Db to "freeze"
      JACK PAPA -- Tuesday, 22 October 2013, at 1:49 p.m.
    • Re: Checkpoint causes Db to "freeze"
      JACK PAPA -- Tuesday, 22 October 2013, at 1:52 p.m.
      • Re: Checkpoint causes Db to "freeze"
        Keith Simmons -- Wednesday, 23 October 2013, at 5:18 a.m.
  • Re: Checkpoint causes Db to "freeze"
    Art Kagel -- Tuesday, 22 October 2013, at 10:36 a.m.
    • Re: Checkpoint causes Db to "freeze"
      JACK PAPA -- Tuesday, 22 October 2013, at 2:01 p.m.
      • Re: Checkpoint causes Db to "freeze"
        Art Kagel -- Tuesday, 22 October 2013, at 2:40 p.m.
        • RE: Checkpoint causes Db to "freeze"
          Habichtsberg, Reinhard -- Wednesday, 23 October 2013, at 8:22 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.