IDS Forum

Re: Checkpoint causes Db to "freeze"

Posted By: Keith Simmons
Date: Tuesday, 22 October 2013, at 10:34 a.m.

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

Jack

Is this an issue that has just started occuring or have you always had
longish checkpoints.
How are your disks connected, what sort of cache do they have ?
A few weeks ago my checkpoints went from 0-1 seconds to 30-50 with
no change in work-load. Investigation showed the cache battery on the
disk array was dead so the server was sriting directly to disk and
suffering for it.
If your issue is a recent occurrence it might be worth checking the
performance of your disk sub-system.

Keiht

On 22 October 2013 15:21, 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.
>
>

--047d7b66f2f7596cb804e9554c43

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.