How To Check For Missing Database Listeners in Qlik Sense on Windows

[Andrew_Delaney]

Qlik Sense Enterprise on Windows relies on database listeners to notify nodes when changes have occurred. These connections can 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

• Qlik Sense Enterprise on Windows, all versions

Click here for Video Transcript

Steps:

1. Connect to the QRS database
2. 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";

3. 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)
4. 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.
   1. Open the Qlik Sense Utility found in C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil
   2. Click on Connection String Editor
   3. Click Read to read in the connection string
   4. 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" />​

   5. Click Save Value in config file encrypted
   6. 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 One: Use the command prompt (CMD). This will not identify the time. 

 ping -t <central node> > ping_log.txt

Option Two: Use 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 Three: Use psping on tcp port 4432.

This is useful in an environment where either QoS is implemented, ping is blocked, or there are other environmental restrictions that could affect TCP connections only. In this scenario, Ping (which uses ICMP protocol) would not indicate latency, which can lead to concluding the 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

Qlik Sense - Randomly, some streams are not available in the Hub 

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.