Load Times by Table

[danestotzer]

Can I find run times by table for change processing?  I use File Change processing to execute the remote job as a batch load.

I have been asked, how long it usually takes to load 10 specific tables out of the 600 tables in the database. This would be for change processing, not full load. 

Would this be possible in Log Stream? 

[shashi_holla]

Load Times - This is usually referenced for time taken to do a Full Load. This we get in Full Load tab after reload.

Change Data Capture (CDC) - This is always running and depends on amount of data coming in.

Are you looking for something like throughput during CDC ?

[danestotzer]

Not really. I’m looking for the runtime by table during the CDC process in the remote task.

My replication is setup as a batch. The local task runs 23 hours then it is stopped, so the remote task can be run. Once the remote task has loaded everything in the file channel it is stopped, and the local task is resumed.

[shashi_holla · Accepted solution]

The local task runs 23 hours then it is stopped, so the remote task can be run

I assume:

Local Task - Task run on the source DB

Remote Task - Replicate CDC

So, the answer would be that when the Replicate CDC starts processing the data it would show the "Apply Latency" in the UI and gradually it will go down based on the data copy speed. We can increase logging for PERFORMANCE parameter to Trace and check the timestamp for start vs end (when the apply latency is zero) which should give you the exact time taken to load the Data.

[danestotzer]

PERFORMANCE logging did not provide run times by table. 

During CDC processing isn't the data processed as transactions, therefore it is not possible/logical that runtimes could be accumulated by tables.  Is this true? 

[Heinvandenheuvel]

> During CDC processing isn't the data processed as transactions, therefore it is not possible/logical that runtimes  could be accumulated by tables.  Is this true? 

It is partly possible, but tricky and most importantly not very useful notably when CSV files are involved.

I think you should answer the question with return questions - 'why do you want to know',  - 'what's the _real_ problem which needs to be solved', - 'how do you think an answer to the question will help sole the real problem' ?

It seems to me that the real problem they want to solve is 'when can we safely stop the remote task, and resume the local task, being confident that all of the last periods change have been applied and none for the next period. Correct? The answer for that is as earlier indicated - make sure the latency on the remote task dropped to zero. This can be best done with Enterprise Manager API calls, possibly with REPCTL batch jobs, or in a pinch by scanning the 'PERFORMANCE' lines in the log as suggested by @shashi_holla 

Now for some per-table insights you could activate TARGET_APPLY logging to TRACE LEVEL. You'll see entries which all start with "00THREAD: 2021-11-08T14:37:51 [TARGET_APPLY ]T: " Trimming the text some, you may see for example

00006412 15:59:55 Start applying of 'INSERT' events for table 'own'.'table' 
:
00006412 15:59:55 Going to run insert statement, from seq 93235 to seq 615897
:
00006412 15:59:59 Finished applying of 522663 'INSERT (1)' events for table...

The details vary by Replicate version and Target DB type (which you did not mention). So now you know there where a (mere) 4 seconds spend for that table (must have been a Snowflake target! 🙂  - for that one bulk entry. There may be more bulks with that table. And watch out to use the thread number to correlate a start with an end. Add all the times per table!  I have a script that does all that, and it can help understanding and sometimes fixing the actual target SQL duration. 

Great, but it is not even half of the story! There are likely also "Start handling table NN"  happening by the master apply thread and that time should be added to the individual table handling times. And there may be the CSV writing , transferring, loading to attrep_change table which is one action for all tables in a bulk combined, but should perhaps be counted as proportional to the size of the changes for the selected table. 

So while the original question is certainly interesting, an exhaustive answer can probably NOT be found and it would likely be of minimal value for 'the real problem which needs to be solved'.

Hein.

[danestotzer]

We are having performance issues in our daily batch cycle for various reasons, one of which is the amount of data we are trying to push though Qlik Replicate.  One option I'm investigating is to splitting the replication into 2 groups. Group 1 would be the 20 key tables needed for downstream batch processing. Group 2 would be the other 600+ tables.

The question I'm attempting to answer is, would processing group 1 separately actually be a runtime savings? These are the 20 largest tables in the DB and the remaining tables are mostly small.  

My assumption is that I cannot use the current CDC process (processing all 620+ tables) to reasonable predict the run time of 20 tables. Am I mistaken?

[Heinvandenheuvel]

It's difficult to help without more details. Versions of softtware involved? Sounds like the remote tasks source is good old File-Channel. What about the target, notably, does it allow for parallel apply within the task.

I'm pretty sure taking those 20 table first, and separate, will speed those up. It is the only way to get some sort of 'prioritization'. Unlike full-load, CDC does not 'listen' to table priority simply working its way through the changes by  tx-timeline, and alphabetic table names. Starting the other tables once done will make the total time (20+600) be slower than all at once, but probably just by a few minutes. You may well find that as long as you start the second task a few minutes after the first, they can run nicely in parallel and complete faster as a pair vs the single task overlapping activities like preparing a bulk of changes by one while actually applying in the other. You have no control over that, but it may just settle like that after a while. 

I've seen startups from file-channel remote tasks with lots (23 hours) of changes waiting take long - many minutes sucking in changes, growing memory and sort space. That would be done by both tasks, so some extra overhead and the reason to suggest only starting the second when a good change volume is seen in the first. Just watch it for a few days.

" tables are mostly small"  - but are they busy? And how WIDE are they.

Think number-of-columns times changes-per-hour  to determine small vs big. Watch the 'applied changes' for a bit to spot the true top 20.

Finally, just try! Create the two remote tasks as 'stores change' to a 'throw away schema or temp DB (preferably on Prod infrastructure). Run once with all, time, truncate. Next run as 20 + 600 in series, time, truncate. Next 20 + 600 in parallel, time, truncate.  Compare the times.  Just use the same FC files as source over and over starting by timestamp.

Note- if you do split, you can not longer trust replicate to delete the processed FC files as 'the other' task may have issues and still need them. So you'll have to come up with a  cleaning scheme, the easiest often being to delete the files more than 48 hours old.

hth,

Hein