topic Re: How to estimate size of transaction files in Qlik Replicate

How to estimate size of transaction files

HeleneExner — Fri, 24 Mar 2023 07:57:16 GMT

Dear community,

A major migration is planned in the source database. When performed on a test database, this migration produced 2.5 TB of redologs.
Can the transaction file size for Qlik Replicate be calculated from the size of the redo logs? Do the size of the redo logs match the potential size of the transaction files?

Many thanks in advance,

Helene

Re: How to estimate size of transaction files

Steve_Nguyen — Fri, 24 Mar 2023 12:17:43 GMT

@HeleneExner , what is your source and target, this will help?

also, if full load, you can break up the batch side , so not sure what the question could be. Replicate is not going to just take the whole 2.5TB and move it, Replicate would take chunk at a time moving the data.

Re: How to estimate size of transaction files

HeleneExner — Fri, 24 Mar 2023 12:39:50 GMT

Hi Steven,

many thansk for your replay!

The source and target are Oracle 19c.

I'm asking because this is what happened in the test environment:
before the migration all tasks were stopped. After the migration, when the tasks were resumed, it filled up disk space on the Qlik Server, making the Qlik console unreachable. This is the scenario that will soon take place in a productive environment. Now I have the possibility to increase disk space. I just need to know how big.

nice greetings,

Helene

Re: How to estimate size of transaction files

Steve_Nguyen — Fri, 24 Mar 2023 12:47:36 GMT

@HeleneExner

"migration all tasks were stopped " what migration ?

Disk space full sound like, it read a huge batch process and try to transfer to target , but target were slow, so Replicate write to Sorter file , causing disk full.

best to work with support or Professional Service team on what Migration are you doing.

Re: How to estimate size of transaction files

Heinvandenheuvel — Sun, 26 Mar 2023 21:51:47 GMT

>> it filled up disk space on the Qlik Server, making the Qlik console unreachable.

As @Steve_Nguyen indicates, Replicate does not typically 'suck up' the whole Redo log but reads and reads until it sees a commited transaction on source and commits that 'chunk'. Now if it can read much faster than it can store on target then Replicate will aggregate more and more, possibly too much - after some time.

Where did the files go and how much and how quickly ? Gigabytes or Terabytes? Hours or minutes? On Windows you may want to use a tool like 'TreeSizeFree' to get a quick resolution?

Where they perhaps Replicate Log files in the log directory suggesting many errors and a high LOGLEVEL which may be fine for DEV but not expected in PROD. Where they in the per-task sorter area? You mention 'tasks' - plural. Are they all reading the same source? Have you considered a LOGSTREAM task to have them share the source REDO reading? Can you resume one task at a time?

- Is there a single or just a few very large transactions doing the migration? Replicate Tasks would have to stage those in the Sorter directory until a 'Commit' is seen. No choice. You may need to redesign the upgrade to commit in smaller increments, or run fewer tasks such that they do not all stage at the same time.

- Is your replicate DATA directory on the 'C:' drive in PROD (or DEV)? It shouldn't be! Move it.

- Did you implement "High Disk Space Utilization Threshold" in Prod? You should! Server --> Resource Control -->
Disk Space

- With Terabytes of changes to be read, and processed, could it be quicker and easier to just reload the targets?

Good luck,

Hein

Re: How to estimate size of transaction files

HeleneExner — Mon, 27 Mar 2023 11:59:04 GMT

Hi Hein,

many thanks for your replay!

The architecture looks like this:

- Source is the Oracle database on Linux Server
- Qlik Server is on Linux (no access for me) Disk Space 500MB
- Ttarget is Kafka streaming platform and partioally Oracle DB
By migration I mean the following process: on the source DB: this is an application that gets a new module. A lot of existing data is also updated so that the new data can be integrated into the application. This creates many updates on the source database. According to REDO, that's 2.5 TB.
The data is loaded from the source using several tasks. All tasks read from the same source but write to 2 different targets - Oracle DB and Kafka streaming platform.
Yes, I can continue the tasks one by one , but that would increase the processing time significantly. I have very limited time for this process.
As for migration... I have absolutely no control over this process and can't control the transactions. This is major release and is done by application owner.
About Resource control for Qlik: yes, warning by 70% and stop the tasks at 80%.
Reloading from target is easy and safe. But that would take days. Unfortunately I don't have that much time.
For this reason, I wanted to use the size of the REDOs to calculate the need for disk space and possibly increase it.

Many thanks and best regrads,

Helene

Re: How to estimate size of transaction files

Steve_Nguyen — Mon, 27 Mar 2023 18:47:50 GMT

@HeleneExner

"About Resource control for Qlik: yes, warning by 70% and stop the tasks at 80%. " does the repsrv log show resource error or task fail on another issue ?

this may need further troubleshooting.

is the REDO log really 2.5TB, check with your DBA source team ,, what is the average REDO log and what is the largest ,, you can enable performance and source_capture ,, trace and see how fast we read.

Re: How to estimate size of transaction files

HeleneExner — Wed, 29 Mar 2023 06:58:00 GMT

Hi Steven_Nguyen,

many thanks for your replay!

Yes, the REDO logs are really 2.5TB. This is a very special and unique situation on the source database. I just want to know if it is possible to calculate the Qlik transaction files from the size of the REDO logs.

Many thanks and best regards,

Helene

Re: How to estimate size of transaction files

Steve_Nguyen — Wed, 29 Mar 2023 13:53:17 GMT

from all the information, provided, best you work with our professional service team to see if this is the correct migration path.

Re: How to estimate size of transaction files

HeleneExner — Wed, 29 Mar 2023 13:55:56 GMT

Hi Steven,

thank you very much 🙂 will do this.

Beste Reagds,

Helene

Re: How to estimate size of transaction files

Heinvandenheuvel — Wed, 29 Mar 2023 21:33:45 GMT

Some more thoughts.

>> Now I have the possibility to increase disk space. I just need to know how big.

It's tempting to ask for 4TB and give some back when done. Test and measure actual usage before Prod system upgrade.

>> - Qlik Server is on Linux (no access for me) Disk Space 500MB

Three problems -

minor - 500MB is surely a typo : 500 GB.

500 Gb is not much, just get more as you indicate?

More random ramblings to possibly help you:

No access for you - and/or not having an admin to work with is a serious organizational issue which you need to have addressed. How do you know which space was exhausted? Sorter area for a specific task? Space for task logs? How do you know by how much? And again how fast / slow did it will up? no answers! Maybe there was excessive (virtual) memory use and the swap space filled up? That will make a system unresponsive! You need Linux admin access or partnership to determine that.

How much CPU was available on the Replicate server? If it was limited then maybe the lighter source reader threads managed to keep reading and filling up the pipe without the (CPU) power to empty out the pipe on the target side? Where some (the wrong) replicate tasks/thread 'niced' away to the background. You need Linux admin access or partnership to determine that.

Back to the basic understandings: Replicate does NOT read and store the REDO log, it processes and forwards. It needs to hold all changes in a transaction until a transaction is committed. You can see that in the CDC monitor UI - "Accumulating (0 transactions) (until source commit)" - What did you see while processing? Queue being build? a few very large transactions? Once committed they are routed to go to the target. You can see that in the CDC monitor "Applying (0 transactions)
(until target commit)" what did you see there?

Everything depends on you application, but it is not uncommon to see 1K .. 2 KB per chance. Migrations typically do bulk transactions - a whole table or set of tables. With for example a modest 1 million rows that becomes 1 GB which have to be held - no choice. With 100 million rows (in a single transaction) that's 100GB times two - once to Kafka once to Oracle. Now you start pushing your server limits already. You should be able to relate this to you Oracle UNDO space needed. Was it complaining? Did it need to be grown? Did you get the infamous "ORA-01555 Snapshot Too Old" during tests?

Now let's say reading the first transactions fits. Now Replicate start to push to the target. How fast can the target accept? Could it accept at all? If it is accepting slow then obviously the pipeline will fill up. So when you tested, did you see 'target latency'? no answers.

Maybe you can de-tune the Oracle source reader, like restricting parallel ASM threads. That way the pipe might not fill up as quickly.

Converting to LOGstream is probably NOT a good idea because it would add staging space. However, it could allow you to throttle the input by stopping and resuming the logstream task.

>> I have very limited time for this process.

noop, you have all the time you need. you just have to explain to management what can be done and what cannot be done in the current configuration and guide them to better configuration if needed. If you cannot do that, then tell them you need help perhaps engaging Qlik professional services. Time is not YOUR problem it is an application design / deploy problem which you are trying to help with.

>> before the migration all tasks were stopped. After the migration, when the tasks were resumed,

Why stop the tasks? If you allow them to keep running everything is 'warmed up' and Replicate can start pushing transactions and cleaning up after them as soon as they are committed. You may be able to overlap the generation of changes with the application on target, more or less keeping up versus being faced with everything at once. And perhaps the migration is done in (big) steps and just maybe you can work with the migration team to hold up on some steps some until prior steps are processed?

>> About Resource control for Qlik: yes, warning by 70% and stop the tasks at 80%.

Well, did that trigger?

You really need to gather more information - from everywhere! Replicate can tell you about several critical measure, the Linux admin can tell you more, the source folks can tell you how many rows and transactions to expect, the target folks can tell you whether the 'apply' gets all the resources and speeds it needs or is a 'top SQL' bogging down the system slowing down the applies.

>> This is the scenario that will soon take place in a productive environment.

You probably need consulting help. Good luck!

Hein.

Re: How to estimate size of transaction files

kishetty03 — Tue, 11 Apr 2023 18:30:29 GMT

HI@HeleneExner

I have got an exact same scenario at my end ditto.
what action were performed to resolve this if you can suggest that would be very helpful

Thanks,

Kiran

Re: How to estimate size of transaction files

HeleneExner — Wed, 12 Apr 2023 11:13:12 GMT

Hi Kiran,

now it's all behind me The new release and subsequent migration in the source database had many problems. This migration took 2 days. All tasks were stopped during this time.
It was very smart of me to order extra disk space. Disk Space has been doubled - 1TB.
A Taks has caused a big problem. 221 tables are to be loaded with the task. During the release, 25 tables (partly very large) should be reloaded, the rest continue the load. That did not work. The disk space was full (1TB) and Qlik tasks were stopped automatically. The disk space was expanded by another 600 GB, which was fully written even after 2 hours. So continuous application was no longer possible.
We had only one possibility to run this task as a full load for all tables. After the full load (about 20 hours) this task runs in continuous application mode.
In my experience, a mix of reload and resume doesn't work well. For such a scenario I would prefer full load. If there are still open questions, I'll be happy to answer them.

Best Regards,

Helene

Re: How to estimate size of transaction files

kishetty03 — Thu, 13 Apr 2023 07:55:01 GMT

Thanks a lot @HeleneExner for the information