We can sometimes see too many checkpoints at intervals of about 3 or 4 seconds on secondary node.

Normal transaction processings might be blocked due to handling excessive checkpoints.

[srvlog]

  I. 06/30 12:20:10. Starting checkpoint of “PKLIDW_R” (PKLIDW_R.db) at Mon Jun 30 2014 12:20

  I. 06/30 12:20:10. Finished checkpoint of “PKLIDW_R” (PKLIDW_R.db) at Mon Jun 30 2014 12:20

  I. 06/30 12:20:16. Starting checkpoint of “PKLIDW_R” (PKLIDW_R.db) at Mon Jun 30 2014 12:20

  I. 06/30 12:20:16. Finished checkpoint of “PKLIDW_R” (PKLIDW_R.db) at Mon Jun 30 2014 12:20

  …

  …

  I. 06/30 12:20:58. Starting checkpoint of “PKLIDW_R” (PKLIDW_R.db) at Mon Jun 30 2014 12:20

  I. 06/30 12:20:58. Finished checkpoint of “PKLIDW_R” (PKLIDW_R.db) at Mon Jun 30 2014 12:20

[iqmsg]

  I. 06/30 12:15:47. 0000000139 Connect:  SA connHandle: 1000000127  SA connID: 22  IQ connID: 0000000139  User: dbo

  I. 06/30 12:15:47. 0000000139 ev_begintxn started

    …

  I. 06/30 12:16:29. 0000000139 Chk

  I. 06/30 12:16:29. 0000000139 ChkDone [NumTxnCP: 6]

  I. 06/30 12:16:29. 0000000139 PostChk

  …

  I. 06/30 12:16:34. 0000000139 Chk

  I. 06/30 12:16:34. 0000000139 ChkDone [NumTxnCP: 6]

  I. 06/30 12:16:34. 0000000139 PostChk

  …

  I. 06/30 15:06:55. 0000000139 Disconnect:  SA connHandle: 1000000127  SA connID: 22  IQ connID: 0000000139  User: dbo

[Cause]

An ev_begintxn event includes a below “sp_iqmpxprocesstlvlog procedure”.

So whenever cmtcount is getting bigger than 100, checkpoint is issued.

      

[sp_iqmpxprocesstlvlog()]

     …

     if

      cmtcount > 100 then

     set cmtcount = 0;

     call

     dbo.sp_iqmpxddlcheckpoint()

     end if

     …

[Resolution]

SAP IQ is oriented to Data Warehouse not an OLTP system.

So need to change and split the job in order to avoid large volumes of transactions consisting of single-row inserts or other small writes.

Ex)
  1) single-row insert  -> load operation
  2) grant command -> use a group concept.

Thanks

Gi-Sung Jang

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Biao Liu

    another question:

    when iq run  “backup dataase full virtual decoupled” and “backup database incremental since full” commd, this command do successfuly , iq server will be hang,until restart iq server.

    iqmsg file have many “ev_begintxt” output .

    note: iq version  IQ 16 SP08  MPX  OS SUSE 11 SP2

    (0) 

Leave a Reply