Data Replication 2
Results 1 to 8 of 8

Thread: CDC Apply On Disk getting very large

  1. #1
    rangga.satya is offline Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0

    CDC Apply On Disk getting very large

    After finished running full load on 125 tables (approx. 4 billion record), which took about 15-17 hours to complete without any error, the task have switched to CDC since.

    I noticed a few things on my CDC Monitor:
    1. The incoming changes is getting very large, 17 Million per this thread is created, about 2.4 million transactions.
    2. The 17 million is building up on Apply On Disk. It also says applying 2.4 million transactions (until target commit).
    3. The Apply latency actually continues to drop from 11 hours about 2 hours ago before this thread is created, to 3 hours when this thread is created.

    The attached screenshot depicts the above.

    Name:  attunity_screenshot.JPG
Views: 816
Size:  45.6 KB

    Question:
    - What is actually happening?
    - Is this normal behavior after a very long full load duration?
    - Is there some tuning that can be done?

    Thanks!

    Kind regards,

    R

  2. #2
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    301
    Rep Power
    6
    rangga.satya

    1. What version of Replicate are you running?
    it could be that you on an older version of Replicate and may need upgrade.

    2. Are you using Replicate as an Express edition or BYOL version? if BYOL is best that you open a support case. since you have an account.

  3. #3
    rangga.satya is offline Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by stevenguyen View Post
    rangga.satya

    1. What version of Replicate are you running?
    it could be that you on an older version of Replicate and may need upgrade.

    2. Are you using Replicate as an Express edition or BYOL version? if BYOL is best that you open a support case. since you have an account.
    5.0.2.25. This seems to be the latest provided by AWS.

    Replicate Premium Hourly.

  4. #4
    rangga.satya is offline Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0
    5.0.2.25. This seems to be the latest provided by AWS.

    Replicate Premium Hourly.

  5. #5
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    301
    Rep Power
    6
    it is best that you upgrade to the current 5.2xx kit. sending you the link via PM.

    how to upgrade below:

    **Note if your enviroment is a Cluster install, Stop and notify Support for cluster upgrade guide**


    NOTE to run the command prompt as Administrator, to execute the repctl.exe.
    The repctl.exe command is under the ~attunity\replicate\bin directory.


    1.) Stop all tasks and Replicate services.


    2.) From the Replicate Command Line Console run the following:


    repctl exportrepository


    for alternate data directories use the syntax:


    repctl -d YOUR DATA DiRECOTRY PATH exportrepository


    3.) Back up the Replicate\data directory to a directory outside of the Attunity product.


    4.) Perform the upgrade by running the executable.


    5.) Resume the tasks.




    back Out:




    1.) The back out requires the product to be uninstalled and then installed to the same directory using the previous kit.


    2.) Once the install is done stop the Attunity Services and copy/overwrite the data directory with your backup.


    3.) start the Attunity service

  6. #6
    rangga.satya is offline Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by stevenguyen View Post
    it is best that you upgrade to the current 5.2xx kit. sending you the link via PM.

    how to upgrade below:

    **Note if your enviroment is a Cluster install, Stop and notify Support for cluster upgrade guide**


    NOTE to run the command prompt as Administrator, to execute the repctl.exe.
    The repctl.exe command is under the ~attunity\replicate\bin directory.


    1.) Stop all tasks and Replicate services.


    2.) From the Replicate Command Line Console run the following:


    repctl exportrepository


    for alternate data directories use the syntax:


    repctl -d YOUR DATA DiRECOTRY PATH exportrepository


    3.) Back up the Replicate\data directory to a directory outside of the Attunity product.


    4.) Perform the upgrade by running the executable.


    5.) Resume the tasks.




    back Out:




    1.) The back out requires the product to be uninstalled and then installed to the same directory using the previous kit.


    2.) Once the install is done stop the Attunity Services and copy/overwrite the data directory with your backup.


    3.) start the Attunity service

    Hi thanks for the reply.

    We try using the full Attunity Replicate Premium (trial license). We are using 5.5.0.367, which according to the local partner is already the latest version. With this set-up, we no longer use Cloudbeam, however the same issue still occur. We are still seeing On Disk Queue very high, approx. 6.5 Million+ transactions.


    We've already made the cluster slightly bigger, from 6 node to 8 node (d2.large). I assume this very high queue is because the performance of insert/update in Redshift is slow with a default configuration. Is there any suggestion on how to tune the Redshift side to improve the CDC performance with Attunity.

  7. #7
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Posts
    301
    Rep Power
    6
    1. make sure that your Redshift Endpoint advanced tab, have max file size set for 1000MB.

    2. Check your logs to see any warning or error.

    3. Make sure all LOBs tables have PK.

  8. #8
    rangga.satya is offline Junior Member
    Join Date
    Nov 2017
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by stevenguyen View Post
    1. make sure that your Redshift Endpoint advanced tab, have max file size set for 1000MB.

    2. Check your logs to see any warning or error.

    3. Make sure all LOBs tables have PK.

    Does the way Attunity perform insert and update to Redshift have anything to do with the slow CDC process? Below is sample update statement from the log, which took 23 seconds to perform on Redshift:

    0004416: 2017-11-20T16:09:15:59547 [TARGET_APPLY ]V: Construct statement execute internal: 'UPDATE "indon"."pd_prd_hist" SET "wirelss_prd_dtl_desc"= CASE
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol1" IS NULL THEN "indon"."pd_prd_hist"."wirelss_prd_dtl_desc" WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol1" = '<att_null>' THEN NULL ELSE CAST ( "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol1" as NVARCHAR(12000)) END ,"advrt_stmt"= CASE
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol2" IS NULL THEN "indon"."pd_prd_hist"."advrt_stmt"
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol2" = '<att_null>' THEN NULL ELSE CAST ( "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol2" as NVARCHAR(4500)) END ,"prd_nm"= CASE
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol3" IS NULL THEN "indon"."pd_prd_hist"."prd_nm"
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol3" = '<att_null>' THEN NULL ELSE CAST ( "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol3" as NVARCHAR(2250)) END ,"wirelss_prd_nm"= CASE
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol4" IS NULL THEN "indon"."pd_prd_hist"."wirelss_prd_nm"
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol4" = '<att_null>' THEN NULL ELSE CAST ( "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol4" as NVARCHAR(2250)) END ,"orgn_nm"= CASE
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol5" IS NULL THEN "indon"."pd_prd_hist"."orgn_nm"
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol5" = '<att_null>' THEN NULL ELSE CAST ( "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol5" as NVARCHAR(900)) END ,"seller_prd_cd"= CASE
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol6" IS NULL THEN "indon"."pd_prd_hist"."seller_prd_cd"
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol6" = '<att_null>' THEN NULL ELSE CAST ( "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol6" as NVARCHAR(450)) END ,"create_ip"= CASE
    WHEN "PREM_GRP_ALL"."attrep_changesB239EDFB846C5933"."c ol7" IS NULL THEN "indon"."pd_prd_hist"."create_ip"
    WHEN "PREM_GRP_ALL"."attrep_...' (ar_odbc_stmt.c:4058)


    00004416: 2017-11-20T16:09:38:295500 [TARGET_APPLY ]T: After get rows affected, rows affected 1 (cloud_bulk.c:483)


    It's a different approach than what I read in this article: https://www.flydata.com/blog/how-to-...d-performance/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •