Qlik Sense Enterprise on Windows relies on database listeners to notify nodes when changes have occurred, it is possible for these connections to be silently closed, which can result in a variety of strange behavior such as newly published apps not being visible on all nodes, or newly created streams or load balancing rules appearing to not take effect until the node is restarted.
Fortunately, it is easy to check if your environment is currently impacted by this issue, and configure keep alive packets to help prevent termination of the listeners.
Environment
Resolution:
Click here for Video Transcript
Steps:
- Connect to the QRS database
- Run the following query:
select cast(count(client_addr) as text), 'Total Listeners' as "query" from pg_catalog.pg_stat_activity where "query" like 'LISTEN%Entity%'
union select cast(count("ID") as text), 'Total Nodes' from public."ServerNodeConfigurations" snc
union select cast(client_addr as text), "query" from pg_catalog.pg_stat_activity where "query" like 'LISTEN%Entity%'
order by "query";
- The Total Listeners should match the Total Nodes value, if there are more nodes than listeners, at least one has been silently terminated, you can determine which ones by comparing the IP addresses listed in the results with those of your nodes. (The one marked ::1 is the node that hosts the database)
- We can ensure that these database connections remain open by adding a keep alive to the connection string. You can do this using the following instructions. NB This will require a brief outage, as all nodes will need to be restarted.
- Open the Qlik Sense Utility found in C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil
- Click on Connection String Editor
- Click Read to read in the connection string
- Add Keep Alive=30; to the connection string for both QSR and QSMQ, the end result should look something like:
<add name="QSR" connectionString="User ID=qliksenserepository;Host='<host>';Port='4432';Database=QSR;Pooling=true;Min Pool Size=0;Max Pool Size=90;Connection Lifetime=3600;Unicode=true;Password='<pwd>';Keep Alive=30;" providerName="Devart.Data.PostgreSql" />
<add name="QSMQ" connectionString="User ID=qliksenserepository;Host='<host>';Port='4432';Database=QSMQ;Pooling=true;Min Pool Size=0;Max Pool Size=90;Connection Lifetime=3600;Unicode=true;Password='<pwd>';Keep Alive=30;" providerName="Devart.Data.PostgreSql" />
- Click Save Value in config file encrypted
- Restart the Qlik Sense Repository service
We can also identify this drop in connection by initiating a ping processes on each of the rim nodes towards the central node. A simple way is to run the following command on rim node and leave it running for at least 24 hours then compare the two resulting txt files which are placed on the current directory:
Option 1: Using cmd (Ok but will not identify the time)
ping -t <central node> > ping_log.txt
Option 2: Using PowerShell. This will use Get-Date to generate a timestamp for each ping which helps with cross-referencing environmental events.
ping.exe -t <DestinationFileServer_IP> | Foreach{"{0} - {1}" -f (Get-Date),$_} > ping_DestinationFileServerName.txt
Option 3: Using psping on tcp port 4432.
This is useful in environment where either QoS is implemented, ping is blocked, or has other environmental restrictions that could affect TCP connections only. In this scenario, Ping (which uses ICMP protocol) would not indicate latency which can mislead to concluding TCP protocol is not observing latency as well. To rule this out use psping.
See Test latency to Qlik Server on a specific port
Related Content:
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
