topic Re: Data Gateway "System.OutofMemoryException" - Direct Access 4020 in Connectivity & Data Prep

Data Gateway "System.OutofMemoryException" - Direct Access 4020

Manguirish — Sun, 17 Nov 2024 07:44:54 GMT

Hi,

We have qlik data connections that query the SQL server using the MS SQL connector (via public IP of SQL server). Data loads/automations etc are running fine and have never caused any issues. This is on Qlik Enterprise SaaS.

For security reasons we have now installed the direct access gateway (v1.6.9) and data connection are made using ODBC via direct access gateway connector (I also tried using MS SQL vial direct access gateway connector). Upon using these gateway connectors our reloads and automation for some of our apps (which are reloading large fact tables - 8 mil rows, 80 columns) fail with Out of Memory exceptions as shown in screenshot below

The data gateway server has 8 core cpu, 32 GB ram and 60GB Hard disk. We are observing minimal resource pressure (less than 30%) on this server and hence conclude that there is no lac of resources on the gateway.

I have tried all the optimisations listed in the below article.
https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Gateways/direct-access-gateway-troubleshooting.htm

I tried increasing the RELOAD_CACHE_MEMORY_MB,
tried setting the PROCESS_ISOLATION_MODE to Load and ODBC_MAX_PROCESS_COUNT to 10 (parallelism). ChangingODBC_MAX_PROCESS_COUNT to 30 seem to work sometimes but fails 4 out of 5 times.

Increasing the RELOAD_CACHE_MEMORY_MB to 100 does seem to resolve the issue but the speed of load is extremely slow and unacceptable. (usually the load finishes in 30 mins but at this speed it will take more than 2 hours plus)

Is this a know issue with using the direct access gateway?
Please help

Re: Data Gateway "System.OutofMemoryException" - Direct Access 4020

RenuLalwani — Thu, 21 Nov 2024 07:18:04 GMT

Hello,

I have few follow-up questions here.

- how is the Disk space looking during this reload failure ?

- can you confirm if this server ist just dedicated to Direct Access gateway only ?

- can you try increasing the RELOAD_CACHE_MEMORY_MB to 200

- setting ODBC_MAX_PROCESS_COUNT between 30-60 count should be good enough.

Close monitoring of memory/resource consumption is needed with enabling the data chunk recovery feature. The text is also added here as suggestion https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Gateways/direct-access-gateway-troubleshooting.htm

In case none of the above suggestions or pointers work and you have more questiosn, we will need to closely look at the logs from your machine and review the Windows events logs too. For that please open case with support and attach the needed logs.

Thanks

Renu

Re: Data Gateway "System.OutofMemoryException" - Direct Access 4020

Manguirish — Tue, 26 Nov 2024 08:12:30 GMT

Hi

Thanks for the suggestion. We will try these settings and check again.

However, we had already tried with RELOAD_CACHE_MEMORY_MB to 500 and ODBC_MAX_PROCESS_COUNT to 30.

and it didn't work.

Infact for some reason, the increased cache memory seem to slow things down a lot, however, the connection was not breaking with the out of memory error.

Is there any significance to using 200MB here?

For time being, we have reset the connection using native SQL connector and using public IP of the server and it is working fine. (I also have another issue here of making this connection point to AG listener so that it hits readable secondary. ApplicationIntent=ReadOnly doesn't seem to work. So for now we are directly pointing to readable secondary)

Will try the revised setting and post the results on this thread.

Regards

Manguirish