Troubleshooting Qlik Replicate Latency and Performance Issues

[KellyHobson]

These are common questions our support engineers ask when investigating latency and performance issue in Qlik Replicate. These can help you troubleshoot the issue and identify the issue. 

1. Is the task running in Batch optimized or one-by-one mode? An in depth article about batches breaking, switching to one by one, and causing latency.
2. Is it Source or Handling latency (you can see this in PERFORMANCE trace output)?
   
   ex. [PERFORMANCE ]T: Source latency 751.41 seconds, Target latency 751.41 seconds, Handling latency 0.00 seconds (replicationtask.c:3703)
   
   
3. If Handling latency is affected, consider increasing buffers and buffer size. Refer to this article: Outgoing stream is full. Forwarding events to target is postponed 
4. If Source latency is affected, ask: Are there any current outages/service interruptions or is source maintenance being carried out?
5. Are there any large or long-running transactions running? 
6. Are any transactions stuck in a pending state?
7. How far away are servers located? Is Qlik Replicate in the same region as source/target?
8. Are network pings okay? i.e do you experience network latency bottlenecks between Qlik Replicate and the Target or Qlik Replicate and the Source? 
9. For Oracle Source, with Performance set to TRACE logging, search for the string "read time is".  In general for 50 MB, the read speed should be between 15 ms to 30 ms. If it is much larger then you need to investigate why the read to source is slow.
10. Are there any LOBs columns included in the tables of the affected task? Can you limit the LOB size?
11. How big are the tables - tall or wide?
12. Is there a very busy time of day when you expect a lot of transactions to commit on the source? You may see a jump in latency during the commit.
13. Does the target support updates/ deletes? ADLS, file. See Limitations 
14. Are there any long-running queries on the source?
15. Is the sorter file(s) size/folder growing?
16. Are changes accumulating on disk or in memory? Do you see any changes applying? To find out:
    1. Task monitor view
    2. Click Change Processing tab
    3. Click on 'Incoming changes' and look at vertical bar graphs
17. Are PKs changing on any tables?
18. If a Full Load takes a long time, is it possible to parallel load? See How to do parallel loading in Qlik replicate 
19. Specific to certain types of endpoints:
    1. Postgres: Are there any timestamptz to type columns?
    2. Snowflake: What is your warehouse size? Can it be increased?
    3. Snowflake: Is Apply batched changes to multiple tables concurrently set? This will allow more to be done in parallel. 
       • Task Settings
       • Change Processing
       • Change Processing Tuning
       • "Apply batched changes to multiple tables concurrently"
20. Is it possible to set a null target and see if the latency is being caused by the sorter or the target? Null File Target
21. For high handling latency, are there any tables without a Primary Key?

As a rule of thumb, we must have tables with and without PK in separate tasks.  Replicate will process changes for tables without PK in one-by-one mode or transactional apply mode depending on the source endpoint. Hence the entire bulk processing becomes slow. Logstream is very useful in scenarios like this as you can put all tables in a single logstream task and then create multiple replication/child tasks which allows us to read redo/tlogs only once.

      22. Is verbose logging enabled? If source_capture to verbose this can increase source latency. 

If you are looking to log a Support case to receive assistance to investigate your latency or performance issues, please be prepared to provide the following.

• Logs with trace enabled for the logging types:
  • PERFORMANCE
  • SOURCE_CAPTURE
  • TARGET_APPLY
• On the task's Monitoring tab, Tools drop-down menu, select Log Management.
• On the screen that opens scroll down to the following items and set them over one position to the right, Trace.
• The change will take effect immediately (no need to stop/resume the task).
• The task log will be fairly large so limit this to only 20-30 minutes before returning these logging levels back to Info, then zip the log file and upload it to this case.
• *Note* "Store trace/verbose logging in memory, but if an error occurs write to the logs" needs to be unchecked to produce PERFORMANCE logging.

Helpful Definitions

Source Latency is based on the time difference between the current time and the timestamp of the last change event read from the transaction log.

Apply Latency is based on the time delay (latency) between the time when a change is visible to the source (and committed), and the time when this same change is visible to the target. More info on apply latency

Techspert Talks - Troubleshooting Qlik Replicate Latency

Additional note

Sometimes a reload is a far better option for a problematic table.  If the time it takes to do a reload is less than the total latency, then a full load back to CDC mode can by you some time while you continue to investigate why the latency is increasing. 

Environment

Qlik Replicate 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.