Problem

Active nodes in the Jira DC cluster query the data for the offline nodes which might be long gone. That adds overhead to DB.
The problem should be largely mitigated by a fix in JRASERVER-42916

Steps to reproduce:

• Set up two node JDC instance (jdc-node-1 / jdc-node-2)
• Enable SQL logging on node one
• Node 1: tail -f atlassian-jira-sql.log | grep jdc-node-2
  • Observe the SQL queries repeating
• Perform normal shutdown of jdc-node-2 (stop-jira.sh)
  • Observe the SQL queries continue forever

Experienced Behavior

• Active nodes query for offline node messages and index operations

Expected Behavior

• Active nodes do not query for messages and index operations for nodes known to be offline.

Note:

• Node 1 shows node 2 as being offline in System Information and in database clusternode table.
• Removing node 2 row from clusternode immediately stops these queries. They start up immediately when the row is added back.

Example SQL Logging

• tail -f atlassian-jira-sql.log | grep jdc-node-2

  2017-06-28 17:52:33,255 NodeReindexServiceThread:thread-1     0ms "SELECT ID, INDEX_TIME, NODE_ID, AFFECTED_INDEX, ENTITY_TYPE, AFFECTED_IDS, OPERATION, FILENAME FROM public.replicatedindexoperation WHERE (NODE_ID =  'jdc-node-2' ) AND (ID >  '9999' )"
  2017-06-28 17:52:34,870 ClusterMessageHandlerServiceThread:thread-1     0ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  2017-06-28 17:52:37,870 ClusterMessageHandlerServiceThread:thread-1     0ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  2017-06-28 17:52:38,258 NodeReindexServiceThread:thread-1     1ms "SELECT ID, NODE_ID, SENDING_NODE_ID, INDEX_OPERATION_ID FROM public.nodeindexcounter WHERE (NODE_ID =  'jdc-node-1' ) AND (SENDING_NODE_ID =  'jdc-node-2' )"
  2017-06-28 17:52:38,259 NodeReindexServiceThread:thread-1     0ms "SELECT ID, INDEX_TIME, NODE_ID, AFFECTED_INDEX, ENTITY_TYPE, AFFECTED_IDS, OPERATION, FILENAME FROM public.replicatedindexoperation WHERE (NODE_ID =  'jdc-node-2' ) AND (ID >  '9999' )"
  2017-06-28 17:52:40,870 ClusterMessageHandlerServiceThread:thread-1     0ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  2017-06-28 17:52:43,261 NodeReindexServiceThread:thread-1     0ms "SELECT ID, NODE_ID, SENDING_NODE_ID, INDEX_OPERATION_ID FROM public.nodeindexcounter WHERE (NODE_ID =  'jdc-node-1' ) AND (SENDING_NODE_ID =  'jdc-node-2' )"
  2017-06-28 17:52:43,262 NodeReindexServiceThread:thread-1     0ms "SELECT ID, INDEX_TIME, NODE_ID, AFFECTED_INDEX, ENTITY_TYPE, AFFECTED_IDS, OPERATION, FILENAME FROM public.replicatedindexoperation WHERE (NODE_ID =  'jdc-node-2' ) AND (ID >  '9999' )"
  2017-06-28 17:52:43,870 ClusterMessageHandlerServiceThread:thread-1     0ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  2017-06-28 17:52:46,870 ClusterMessageHandlerServiceThread:thread-1     0ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  2017-06-28 17:52:48,264 NodeReindexServiceThread:thread-1     0ms "SELECT ID, NODE_ID, SENDING_NODE_ID, INDEX_OPERATION_ID FROM public.nodeindexcounter WHERE (NODE_ID =  'jdc-node-1' ) AND (SENDING_NODE_ID =  'jdc-node-2' )"
  2017-06-28 17:52:48,266 NodeReindexServiceThread:thread-1     1ms "SELECT ID, INDEX_TIME, NODE_ID, AFFECTED_INDEX, ENTITY_TYPE, AFFECTED_IDS, OPERATION, FILENAME FROM public.replicatedindexoperation WHERE (NODE_ID =  'jdc-node-2' ) AND (ID >  '9999' )"
  2017-06-28 17:52:49,870 ClusterMessageHandlerServiceThread:thread-1     0ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  2017-06-28 17:52:52,870 ClusterMessageHandlerServiceThread:thread-1     0ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  2017-06-28 17:52:53,268 NodeReindexServiceThread:thread-1     1ms "SELECT ID, NODE_ID, SENDING_NODE_ID, INDEX_OPERATION_ID FROM public.nodeindexcounter WHERE (NODE_ID =  'jdc-node-1' ) AND (SENDING_NODE_ID =  'jdc-node-2' )"
  2017-06-28 17:52:53,269 NodeReindexServiceThread:thread-1     1ms "SELECT ID, INDEX_TIME, NODE_ID, AFFECTED_INDEX, ENTITY_TYPE, AFFECTED_IDS, OPERATION, FILENAME FROM public.replicatedindexoperation WHERE (NODE_ID =  'jdc-node-2' ) AND (ID >  '9999' )"
  2017-06-28 17:52:55,890 ClusterMessageHandlerServiceThread:thread-1     10ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  2017-06-28 17:52:58,271 NodeReindexServiceThread:thread-1     1ms "SELECT ID, NODE_ID, SENDING_NODE_ID, INDEX_OPERATION_ID FROM public.nodeindexcounter WHERE (NODE_ID =  'jdc-node-1' ) AND (SENDING_NODE_ID =  'jdc-node-2' )"
  2017-06-28 17:52:58,272 NodeReindexServiceThread:thread-1     1ms "SELECT ID, INDEX_TIME, NODE_ID, AFFECTED_INDEX, ENTITY_TYPE, AFFECTED_IDS, OPERATION, FILENAME FROM public.replicatedindexoperation WHERE (NODE_ID =  'jdc-node-2' ) AND (ID >  '9999' )"
  2017-06-28 17:52:58,871 ClusterMessageHandlerServiceThread:thread-1     1ms "SELECT ID, SOURCE_NODE, DESTINATION_NODE, CLAIMED_BY_NODE, MESSAGE, MESSAGE_TIME FROM public.clustermessage WHERE ((DESTINATION_NODE =  'jdc-node-1' ) OR (DESTINATION_NODE =  'ANY' ) OR (DESTINATION_NODE =  'ALL' )) AND (SOURCE_NODE =  'jdc-node-2' ) AND (ID >  '10700' ) ORDER BY ID"
  

Note on fix

Problem should be obsoleted by JRASERVER-42916.