Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Gisung
Advisor
Advisor
0 Kudos

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

1 Comment