Official Support Articles

[Zareh_T]

The Qlik Sense Repository Service can suddenly come to a situation where the CPU utilization of the central node increases beyond 95%, which causes effects in the Qlik Sense hub (sheets go blank and other unexpected slow behavior). The process that is consuming the maximum CPU can be seen as the Qlik Sense Repository Service. 

Identified in a Problem Investigation with R&D, one of the possible causes is related to large number number of open transactions in the repository database and locks held by large number of sessions impacting on the repository service communication with the repository database. 

Environment:

Qlik Sense Enterprise on Windows November 2017 and later versions 

Resolution: 

Setting the  idle_in_transaction_session_timeout  to 5 minutes would clear the threads the repository database and progresses the new threads in the queue

1. Connect to the QSR database using PGADMIN4
   • Reference: Installing and Configuring PGAdmin 4 to access the PostgreSQL database used by Qlik Sense or NPrinti...
   • Qlik Support does not support the direct query and access to the underlying product's database hosted by PostgreSQL. If any issues arise for which direct queries are deemed responsible, Qlik Support will reserve the right to request that a previous working backup is restored to the system in order to bring back full operation or resolve any isolated issue(s). The steps described here in this article are only used by Qlik Support for troubleshooting purposes.
2. Right-click the QSR database and bring up the Query Tool. 
3. Run the following command to look at the default value of the idle_in_transaction_session_timeout. 
   

   SHOW idle_in_transaction_session_timeout;​

4. By default, this is set to 0. 
5. Update the value by running the following command: 
   

   alter system set idle_in_transaction_session_timeout to '5min';​

6. Confirm the value has been updated running the following: 
   

   SHOW idle_in_transaction_session_timeout;  
   # reload the configuration     
   select pg_reload_conf();​

   
   Example:
   
   
   
7. The same setting could be done by editing the following file:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6\postgresql.conf
   
   By searching for:
   

   #idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled​

   And changing it to:
   

   idle_in_transaction_session_timeout = 300000        # in milliseconds, 0 is disabled​

   Save, and then schedule a restart. 

Cause:

Changing the value, idle_in_transaction_session_timeout  terminates any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused decreasing over CPU utilization.