CDC Performance Issues

[NC_OFD]

Hi,

I am experiencing CDC performance issues in the form of low Apply Throughput when applying to target. 

I am using the Qlik Replicate solution on RedHat Linux with an external Oracle Database as source with ASM and target being a MSSQL Database. 

My goal is to have a complete image of the Oracle Database at all times within a couple of minutes of delay. Which would require a throughput of no less than 2.000 kb/s.

However, I am experiencing serious CDC performance issues with an average Apply Throughput of < 400 kb/s, with Incoming Changes waiting to be applied piling up causing an ever increase in delay. 

We have an idea that our issue is caused by the unusual database with CLOBS over 300 GB. However, our Full Load is showing throughputs 100x better than our CDC, which leads me to believe that there might be an obvious configuration that I am overlooking.

Some notes:

- Full Load is able to finish in less than 20 hours with throughput reaching over 40.000 kb/s.

- If using a null target - instead of MSSQL - I get satisfying CDC performance.

- We have a few tables with columns containing CLOBS of 300GB in size. 

What I have tried:

- I have tried my best to find helpful resources on this forum and in the documentation. However, none have seemed to solve the issue. (ref. https://community.qlik.com/t5/Support-Knowledge-Base/Latency-Performance-Troubleshooting-and-Tuning-...)

- Tried several settings such as tuning Replicate Lob Columns

- Used the Perl scripts (link above) to locate transfer issues, which all points to be a issue when applying to target.

- Splitting the replication into 3 different tasks. (1) for creating a log stream (2) replicating without CLOBS using the log stream as source (3) replicating the CLOBS also using the stream as source. 

Any questions are welcome and all tips are appreciated. @David_Fergen @Jeff_Schwartz 

[Jeff_Schwartz]

Hi Oliver,

Does the table have a PK in the SQL Server target endpoint database?  You should have a PK that really is a primary key for CDC to work best.

Another thing to investigate if target table has a PK are the values for the BCP settings on the advanced tab of the SQL Server target endpoint.  I'm not a SQL Server expert and don't know the best settings for them but it may be why Full Load works well but CDC doesn't.

Regards,
Jeff

[MarinaEmbon]

Hi,

you probably get good performance with NULL target as Null target does NOT call to "lookup" for the LOBS.

just want to confirm that the performance issues are with the LOBS. if you don't replicate the huge CLOB data, do you get satisfying CDC performance?

[NC_OFD]

Hi Jeff,

Your answer is greatly appreciated.

All tables do have primary keys. However, I thought that the BCP settings were not supported when using Linux - I see from the documentation that this no longer seem to be the case for versions since November. 

I am going to look into the BCP settings right away. I'll keep you posted 🙂

Thanks!

[NC_OFD]

Thanks for your question!

I haven't tested this yet specifically, but I am on it! I reckon I can have the result within a day (got to test during peak hours). I'll keep you posted 🙂

Thanks!

[MarinaEmbon]

BCP is now supported on Linux (what Replicate version do you use?) but BCP is used only for FL.

[NC_OFD]

Thanks! I am using 7.0.0 (areplicate-7.0.0-213.x86_64).

[MarinaEmbon]

BCP on Linux is supported starting with Replicate 6.6 but it's great that you use the newest version 🙂

[NC_OFD]

Hi @MarinaEmbon ,

I am glad for your question! As a follow up:

1. Surprisingly, excluding the CLOBS does not cause any increase in kb/s during CDC. 
   • Despite; I am still able to Full Load to target with much higher kb/s.
2. PK violations during CDC.
   • I have added a rule to update these PK if they occur. Thus, I am not suspecting this to have much impact on performance (am I wrong?).
3. When replicating directly to a target File on the Qlik Replicate Linux Server the performance is satisfying. No issues whatsoever.
4. When replicating to another external database (also MSSQL) I hit the same issue. Good performance during Full Load and same low kb/s during CDC.

From the above, I am quite confused on what is causing this problem. How can it be that we get great performance with Full Load and - comparably - very low kb/s during CDC?

As before, any tips or questions are appreciated 🙂 Thanks!

[MarinaEmbon]

unfortunately, as we eliminated LOBs, I don't see any reason for performance issues, I suggest to open a support case attaching the diagnostic package to let us analyze it.

sorry couldn't help more 🙂

Marina