topic Re: Latency in target apply in Qlik Replicate

Latency in target apply

Titus — Mon, 27 Nov 2023 23:02:42 GMT

We are encountering latency issues with Snowflake as our target. I'm unsure why this is happening, as all transactions seem to be committing to disk. There are almost 60,000 transactions on disk. Upon reviewing the logs, I noticed the "attrep exception error." I need to understand the cause of this latency and how to address it.

00009656: 2023-11-27T21:38:38 [TARGET_APPLY ]W: Source changes that would have had no impact were not applied to the target database. Refer to the 'attrep_apply_exceptions' table for details (endpointshell.c:7174

Re: Latency in target apply

Dana_Baldwin — Mon, 27 Nov 2023 23:15:38 GMT

Hi @Titus

Generally that warning message can be ignored as it means that nothing would have changed if the transaction was applied to the target, something like a delete on a row that is already gone on the target, that type of thing - but it depends upon what you find when you query the attrep_apply_exceptions table on the target.

Target latency generally points to an issue sending data to the target. Root cause can include issues on the network or the target itself, those items should be explored.

Are you seeing the latency during the full load phase of the task or change processing?

Has this task worked well previously (meaning it is not a brand new task)?

I can also share with you "best practice" task settings when using Snowflake target, these may also help:

Set the Max file size on the advanced tab of the target endpoint to 2000 MB
To increase the size of the batches sent to Snowflake, please edit the settings in Task Settings \ Change Processing \ Change Process Tuning:

Longer than (seconds) to 299
But less than (seconds) to 300
Force apply a batch when memory exceeds to 2000
Transaction offload tuning:
Total transactions memory size exceeds (MB) to 5000
Transactions duration exceeds (seconds) to 86400

If this information does not help, please open a support case and provide a diagnostics package for the task that includes logging elevated for Performance to trace for 10-15 minutes. If there is an error condition or evidence of a software defect, Support can help. If not, we can involve our Professional Services team.

You might also find this knowledge article helpful:

Latency / Performance Troubleshooting and Tuning f... - Qlik Community - 1734097

Thanks,

Dana

Re: Latency in target apply

Heinvandenheuvel — Tue, 28 Nov 2023 14:48:15 GMT

>>> We are encountering latency issues with Snowflake as our target.

And it is confirmed to be target latency in UI or in log with PERFORMANCE DEBUG?

To understand this in general you should set LOG level for TARGET APPLY to DEBUG and see which queries takes long. You can and should correlate this with SQL activity for the Attunity User on target. The Snowflake UI makes this relatively easy: how many queries - how long.

>>> , I noticed the "attrep exception error." I need to understand the cause of this latency

Well, that make this issue easy to understand i guess... . Replicate will normally (Snowflake always) apply changes in batches which could be just a few rows, or thousand, or a hundred thousand. Snowflake will gladly swallow those batches in a few seconds. However, when an error occurs and the task has requested exception logging then Replicate goes out on a mission to find which row(s) caused the error. To do so it issues a singleton change request for each row in the batch. Now while Snowflake is super fast in handling thousands or millions of changes in seconds, it is very slow for a singleton request. I rarely see a single update take less than a second and 2 seconds is 'normal'. Compare that to SQLserver or Oracle which answer in milliseconds - thousand times faster.

So if 1 row fails in a 5000 row batch, Replicate may take 5000 or even 10000 second to figure out which one, one at a tine. That's and hour or two. There is your latency!

Hein.

Re: Latency in target apply

Heinvandenheuvel — Thu, 30 Nov 2023 16:37:51 GMT

Update please?

If the issue is still there and not understood can you attach a relevant chunk of the reptask.log (as .txt?!) With TARGET _APPLY logging set to Trace/Debug please.

Hein.