topic Re: Spiking Throughput during full load in Qlik Replicate

Spiking Throughput during full load

guilherme-matte — Fri, 12 May 2023 00:28:21 GMT

Hello guys

I recently had to reload a task, which was running fine before. Its from a SQL MS-CDC on Prem to an Azure SQL db.

But now, my throughput stays in 0 for minutes, spikes to 1k for 5 seconds and gets back to 0 for some minutes. (in the full load settings its set to 4.000)

If i decrease the throughput, it does the same, put the spikes go to a max of 165 or 336. (in the full load settings set to 1.000)

Even if I increase it more, it stays in 0 for minutes, spikes for a second and goes back down again, I've change from batch mode to transactional mode, with unlimited LOB and limited LOB.

The logs does not prompt errors nor warnings:

Any ideas on what might be causing it? Any logs that might be helpful for troubleshooting this?

Kind Regards

Re: Spiking Throughput during full load

Steve_Nguyen — Fri, 12 May 2023 11:21:46 GMT

@guilherme-matte

1. set target_load ,, trace

set source_capture,, trace

set performance ,, trace

set sorter ,, trace

review the log on what causing the spike

Re: Spiking Throughput during full load

Heinvandenheuvel — Fri, 12 May 2023 14:25:33 GMT

"was running fine before." - so what changed? - where those currently loading tables recently added? Where columns changes (lobs added) in those tables? - was the "task-settings" - "metadata" - "Replicate LOB columns" configuration changed perhaps?

@Steve_Nguyen - hmmm, the tracing you suggest would create an awful lot of noise to wade through.

For now, the OP appears concerned with FULL-LOAD performance only, likely caused at the source.

Also based on the attached picture I'd recommend turning all tracing OFF except SOURCE_UNLOAD to TRACE to begin with, increasing to VERBOSE if needed and adding TARGET_LOAD TRACE if needed.

The picture indicates "1 row per fetch" and "non-optimized full lob support" in the SOURCE_UNLOAD lines. That may be the entire problem.

I would also recommend a test task with just one offending table (focus3_latest) to decrease noise and increase clarity. And I would decrease ""Task-Settings" - "full-load" - "Tuning" - "Commit rate during full load:" from the current setting (4000???) to 100 for the initial tests to see some movement sooner. Increate back to 1000, 10000, 100000 when the performance allows for it. With that in place be sure to investigate the source DB top-SQL.

I might even use a NUL target for the initial testing.

As it is, those table loads are project to 'never finish' - well, more then 12 hours. So for your testing just look at that and KILL when you see it is not going anywhere fast and logs/trace were captured.

@guilherme-matte Thanks for the REPTASK_xxx.LOG output. But for heaven's sake why a stupid picture? Why not (pieces of) the raw text such that folks trying to help can 'play' with it or at least can copy and paste an interesting word or line which needs to be highlighted?? Use a CODE/TEXT BOX in -line or attach a .TXT file ???

hth, Hein.

Re: Spiking Throughput during full load

guilherme-matte — Mon, 15 May 2023 00:22:32 GMT

Hello @Heinvandenheuvel , @Steve_Nguyen

Thank you for the inputs and feedbacks (not posting prints of the logs again 🙂

I've selected only the focus_action table and used the following parameters:

* Commit rate: 100

*Target Load and Source_Unload: Trace

* Allow unlimited LOB size

The exact same still happens, the task still holds for a huge time and spikes, one thing that seems to loop for a while on the log is the following statement (log attached):

"UPDATE_LOB statement for table 'dbo.focus3_action' was not found in the pool, going to allocate a new statement"

Some additional info:

If I let the standard task configuration, the task will run but will truncate the lob columns out of it.

If I select "Allow unlimited lob sizes" the current issue happens (little spikes of throughput with huge downtime)

if I do not allow unlimited lob sizes, but increase the size manually, the task critically fails and stops, prompting the log (attached) and error attached in "Increases_LOB_sized_Error.txt" and "Increases_LOB_sizedLOG.txt

@Heinvandenheuvel , why would the "non-optimized full lob support" happen?

Kind regards!

Re: Spiking Throughput during full load

Heinvandenheuvel — Mon, 15 May 2023 14:29:14 GMT

>>> Thank you for the inputs and feedbacks (not posting prints of the logs again

Thanks, and you are welcome. Providing the text allowed me (notepad++) for example to count there are 524 columns requested in when the INSERT statement could not be parsed. I also see that the argument list is not finished (300+, not 500+) and the total bytes is 10005. The number of columns and/or the length MIGHT have run into a restriction - most likely the columns count. The length might have been cut somewhere during reporting.

For bonus points rename .LOG files to be attached to .TXT allowing previews ( in this forum ).

I'd recommend trying further with reduced column list (pk - PK_Focus3_Action - a couple of dates and useful identifiers and some or all LOBs.). And I might chage to full-load only - for now - for further added focus.

Did you see: Bulk is set to ignore max row size warnings

And: Warning: The table "focus3_action" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

!? With apparently 500+ columns and 20 LOBs (VARCHAR(MAX) ? ) on the table those 8K will soon be reached.

>> Commit rate: 100

>> The exact same still happens, the task still holds for a huge time and spikes, one thing that seems to loop for a while on the log is the following statement (log attached):

That was expected - but now we have more clarity, more focus.

We see the 400 rows transferred in 9m55s - that's 4 batches of 100, 2+ minutes each, or 1+ second/row. This is confirmed by the 1 second time interval with which the "INSERT statement for table" pops in the log. With almost a million rows to go, that maps to 10 days - yikes.

>> "UPDATE_LOB statement for table 'dbo.focus3_action' was not found in the pool, going to allocate a new statement"

Not a big worry. The Replicate statement cache is poorly implement (Broken!) IMHO. You could try Task Setttings - Change Processing - Tuning - "Miscellaneous Tuning - Statements cache size (number of statements):" change from 50 to 200 (max) and hope it is honored in fullload. I suspect it will be because that entry will end up in the json as "target_settings": { ... "statements_cache_size": 200,... in a general section, not CDC specific but you have to enable (and re-disable if desired) CDC to get to it in the UI.

>> If I let the standard task configuration, the task will run but will truncate the lob columns out of it.

That's indeed the default I believe.

>> if I do not allow unlimited lob sizes, but increase the size manually, the task critically fails and stops, prompting the log (attached) and error attached in "Increases_LOB_sized_Error.txt" and "Increases_LOB_sizedLOG.txt

I think it is time to submit a support case for that.

>>> why would the "non-optimized full lob support" happen?

If I remember correctly, the 'optimized' lob support sets up a max lob-sized buffer and during CDC (fullload is different) during target apply will go back to the source and perform essentially a 'select lob1,lob2,lob3,... from table where pk-column(s) = pk-value(s).'. For unlimited it can obviously not provide an unlimited buffer and will instead 'chunk' its way through the lob in a loop with a fixed size buffer until done.

Hope this helps some more.

Hein.

Re: Spiking Throughput during full load

guilherme-matte — Wed, 17 May 2023 06:59:01 GMT

Hello @Heinvandenheuvel

Thanks again the insights, I tried the "Miscellaneous Tunning" tips but without luck.

I've removed all CLOB columns (about 57) and the task runs perfectly, even with the all the other 450 columns (and a lot of VARCHAR (500)). I've then tried even running the task only with 15 of the CLOB Columns, but no luck again.

Always receiving the same error (reptask_Log1_15tables) with this line in between the logs: Not enough memory resources are available to process this command........... (size: 614400400 bytes)

Though I've noticed that the more I reduce the number of columns, or decrease the full load commit rate, the smaller this number SIZE number of bytes in the log. If i decrease the commit rate enough (20 commit rate considering all columns or about 200 commit rate considering 15 CLOB columns) the task runs, not prompting this error in the logs anymore.

Any ideas what this memory error refers to? Could there be ways of working around this?

Might open a support ticket for this case as well, as you recommended.

Regards!

Re: Spiking Throughput during full load

Steve_Nguyen — Wed, 17 May 2023 13:52:01 GMT

@guilherme-matte

not sure what your LOBs limit is set for. but lower the commit rate will help if you have many table with lobs.

Re: Spiking Throughput during full load

Heinvandenheuvel — Wed, 17 May 2023 17:25:43 GMT

This all seems to happen right-away, preparing.

There appears to be a big multiplier going taking you from a couple of 100 MB to size: 4096002000 (4GB).

I'm sure commit-count, lob-count, and max-lob-size are a factor. Number of tables should be a multiplier factor but maybe it is? It feels like regular column-count is a factor. If that gets multiplies by log-size instead of actual sizes being added up then you'd get into the GB space rather quickly.

Pick one of the 'medium failing test' like that reptask_Log1_15tables- size: 614400400 bytes. Now drop 1 lob-column what does the byte number become. Pick commit read 20,19 and 18. What is the step in bytes per extra row? Drop a normal column, what is that impact. I suspect that in a hour of testing you can come up with the formula used.

You might want to check the VM settings for the processes and system. Is there enough pagefile to backup a multi-GB virtual memory allocation?

Finally you are nicely running a recent version 2022-11. Very recent some might say. Maybe something change - can you perhaps on an other (dev/qa/prod) server with 2022-05. And just maybe try 2023-05?

The more info you gather, the better, faster you support case can be dealt with.

Regards,

Hein

V2022.11.0.208 qlik.corp.barwonwater.vic.gov.au Microsoft Windows Server 2012

Re: Spiking Throughput during full load

guilherme-matte — Thu, 18 May 2023 23:14:53 GMT

Hello @Heinvandenheuvel and @Steve_Nguyen

I've been tweaking with the suggested configs and found a way to successfully finish the full load in the end.

Apparently, enabling unlimited LOB size here is out of question, it just spikes once in a while and doesn't go anywhere. What i had to do was slowly increasing the max lob size, in a trial and error and checking the task if no columns were got truncated mid load. if there happened a Truncate, i would stop the task and increase a bit more. If received the memory issue, i would reduce the throughput, so it was always a balancing between the throughput and the max lob size to avoid the memory issue. with about 600KB max lob size and 150 throughput i was able to load it under 3h.

I was thinking about creating two tasks for this DB, one with the LOB tables and one without, would this be considered a good practice? Each task would be configured differently then.

Kind regards and thank you the help!