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.
- Qlik Sense November 2017 and later
Changing the value, idle_in_transaction_session_timeout t
erminates 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. Information provided on this defect is given as is at the time of documenting. For up to date information, please review the most recent Release Notes, or contact support at support.qlik.com with the ID QLIK-86668 for reference.
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
- Connect to the QSR database using PGADMIN4
- Reference: Installing and Configuring PGAdmin 4 to access the PostgreSQL database used by Qlik Sense or NPrinting
- 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.
- Right-click the QSR database and bring up the Query Tool.
- Run the following command to look at the default value of the idle_in_transaction_session_timeout.
- By default, this is set to 0.
- Update the value by running the following command:
alter system set idle_in_transaction_session_timeout to '5min';
- Confirm the value has been updated running the following:
# reload the configuration
Same setting could be done editing the C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6\postgresql.conf searching for the line :
#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
and change to :
idle_in_transaction_session_timeout = 300000 # in milliseconds, 0 is disabled
save and schedule the restart .