[MPX]A relationship between ev_begintxn and checkpoint.

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