IBM Support

QRadar: Reference Set Management takes a very long time load when opened, often leading to Tomcat restarting

Troubleshooting


Problem

In QRadar, users might experience issues where the Reference Set Management interface or the Reference Data Management app takes a long time to load. The size of the reference set can impact how long it takes the data to load in an app or user interface. As loading the data can take as little as 2 minutes or up to 30 minutes to complete, which can cause Tomcat instability. This article provides guidance to administrators on how to improve performance for large reference sets and steps administrators can take to reduce the data volume.

Symptom

The Reference Set Management Window opens, but remains blank for many minutes. Reference Data Management App opens, but Reference Sets are not viewable and showing loading indications for many minutes.
image-20230721133612-1
Figure 1: The user interface attempts to load larger than expected reference sets without fully displaying the columns.

Environment

  • IBM QRadar SIEM 7.4.0 to 7.4.2.
  • IBM QRadar SIEM 7.4.3 and later might be affected, but the issue is far less likely to occur.

Diagnosing The Problem

On the console, the file /var/log/qradar.error might contain warnings from the TxSentry process, similar to the following:
[hostcontext.hostcontext] [{GUID}/SequentialEventDispatcher] com.q1labs.hostcontext.tx.TxSentry: [WARN] [NOT:0000004000][IPADDR/- -][-/- -]
Found a process on host {IP address}: tomcat, pid=29335, TX age=657 secs
[hostcontext.hostcontext] [{GUID}/SequentialEventDispatcher] com.q1labs.hostcontext.tx.TxSentry: [WARN] [NOT:0000004000][IPADDR/- -][-/- -] 
TX on host {IP address}: pid=29335 age=657 IP=x.x.x.x port=36360 locks=8 query='SELECT a1.key1, a1.key2, a1.data, a1.source, a1.first_seen, 
a1.last_seen, a1.domain_info FROM ( SELECT rdk.key1, rdk.key2, rde.data, rde.source, rde.first_seen, rde.last_seen, rdk.domain_info FROM 
reference_data_key rdk, reference_data_element rde WHERE rdk.id = rde.rdk_id AND rdk.rd_id = $1 ORDER BY rde.first_seen, rdk.key1, rdk.key2, 
rde.data ) AS a1 INNER JOIN ( SELECT DISTINCT rdk.key1 FROM reference_data_key rdk, reference_data_element rde  WHERE rdk.id = rde.rdk_id 
AND rdk.rd_id = $2 ) AS b1 ON b1.key1 = a1.key1 ORDER BY a1.first_seen, a1.key1, a1.key2, a1.data'
Or
[hostcontext.hostcontext] [<GUID>/SequentialEventDispatcher] com.q1labs.hostcontext.tx.TxSentry: [WARN] [NOT:0000004000][IPADDR/- -][-/- -]
Lock acquired on host {IP address}: rel=reference_data_element age=657 granted=t mode=AccessShareLock query='SELECT a1.key1, a1.key2, a1.data, 
a1.source, a1.fi'
[hostcontext.hostcontext] [<GUID>/SequentialEventDispatcher] com.q1labs.hostcontext.tx.TxSentry: [WARN] [NOT:0000004000][IPADDR/- -][-/- -]
Lock acquired on host {IP address}: rel=reference_data_key_pkey age=657 granted=t mode=AccessShareLock query='SELECT a1.key1, a1.key2, 
a1.data, a1.source, a1.fi'

 

Resolving The Problem

As the problem is a combination of multiple smaller issues, there are multiple workarounds that can help improve performance and stability of Reference Sets or Tomcat.
1. Upgrade to a newer version of QRadar
This issue is far less pronounced with QRadar 7.4.3 and later versions. Often, upgrading to a newer version is enough to resolve the issue without tuning changes to the configuration.
2. Apply a Time-to-Live (TTL) on large Reference Sets
Large Reference Sets contribute to the issue and can quickly grow to millions of entries. Administrators need to configure reference sets to expire older data that has low value to test against smaller data sets and increase performance. One method to reduce the number of elements in a reference set is to configure a Time-To-Live (TTL). When values in the reference set reach a set age, QRadar can automatically remove the values. Configuring a TTL might be unique for each reference set or use case, as some elements are needed for longer periods of time.

Procedure
  1. Log in to QRadar as an administrator.
  2. Click the Admin tab.
  3. Click the Reference Set Management.
  4. Review any large reference sets (greater than 100,000 elements)
  5. If these sets do not have a TTL set, consider setting one for a number of days based on your needs to remove elements from the reference set.
    Note: The default setting is not to use TTL and instead "Lives Forever" is checked.

    Results
    The TTL starts removing old elements in the background and system performance might gradually improve.
     
3. For remaining large reference sets, apply Tomcat memory overrides
If you added a TTL and the change does not improve the situation or is not possible for your particular use case, large reference sets can be tuned to assign a larger cache to improve performance. This override works by assigning more cache to an individual reference set on Tomcat startup, which reduces the frequency of loading values from disk to locate data when requested by a rule or search.

Procedure
Tuning the spillover threshold is not a required change for all reference sets. The goal of this procedure is to tune reference sets that greatly exceed the default Reference Data spillover threshold the most. The sets with counts fewer the Reference Data Spillover Threshold value are to be ignored.
  1. Use SSH to log in to the QRadar Console as the root user.
  2. Find the Reference Data Spillover Threshold value by using the following command:
    grep "ReferenceData.spillover.threshold" /opt/qradar/conf/spillovercache.properties 
    For example, the following output displays the number of elements that can be loaded at one time in memory:
    ReferenceData.spillover.threshold=36648
  3. Find the largest, most likely to be cause sets by using this command:
    psql -Uqradar -c "select id,name,time_to_live,current_count from reference_data order by current_count desc limit 20;"
    The output displays the largest reference sets by number of elements. Note the names and IDs and counts of sets with large element counts.
    image-20220110110706-4
    Tip: Typically the sets that are 10x of the Reference Data Spillover Threshold value are good candidates for tuning.
  4. Record the reference set ID numbers and names for large reference sets.
  5. Edit the /opt/qradar/conf/spillovercache.properties file with any text editor.
  6. Add an override for the reference sets that exceeds your Reference Data Spillover Threshold, in the following format:
    RefData_<ID goes here>.spillover.threshold=<Count, rounded up>
    For the example MillionsSet Reference set, add the following line to the file:
    RefData_30_domain_2147483647.spillover.threshold=400000
    Important: This change does not have to be done for all sets, but instead the ones that exceed the Reference Data Spillover Threshold the most. The sets with counts fewer the Reference Data Spillover Threshold value are to be ignored.
  7. Save the changes to the spillovercache.properties file.
  8. Type the following command to confirm the file permissions for the spillovercache.properties file.
    ls -l /opt/qradar/conf/spillovercache.properties
    For example, the permissions must display as follows:
    -rw-r----- 1 nobody nobody 14100 Jul 20 03:31 /opt/qradar/conf/spillovercache.properties 
  9. Optional. To set the correct file permissions, type:
    ​chmod 640 /opt/qradar/conf/spillovercache.properties
  10. Optional. The owner of spillovercache.properties must be nobody. To set the correct owner, type:
    chown nobody:nobody /opt/qradar/conf/spillovercache.properties
    Important: The following step restarts the Tomcat service in QRadar. Restarting Tomcat stops the user interface and logs out all users, stops event exports in progress, and can prevent scheduled reports or scans from launching while the service restarts. It is important to notify users before you restart Tomcat. For more information, see 
  11. To load the change, type the following command to restart Tomcat.
    systemctl restart tomcat
    Results
    After the Tomcat service restarts, log in and review the system to determine whether performance or stability is improved.
4. Increasing Transaction Sentry Timeout settings
As a temporary workaround to Tomcat instability, administrators can increase the Transaction Sentry Timeout setting in QRadar in order to allow more time for the process to complete.

Procedure
  1. Log in to QRadar as an administrator.
  2. Click the Admin tab.
  3. Click the System Settings icon.
  4. Select Advanced.
  5. From the Transaction Sentry Settings, increase the Transaction Max Time Limit to 30 minutes.
  6. Click Save.
    image-20230720173426-1
  7. Click the Admin tab.
  8. Click Advanced > Deploy Full Configuration.
  9. When prompted, click OK to deploy changes to all managed hosts.
 


 

What to do next

If the provided steps do not significantly help with your performance issues or you experience issues where the user interface is slow, you can open a case with QRadar Support. In order to speed up resolution, it is recommended to provide the following logs with your case:
  1. The QRadar log files. For more information, see How to collect log files for QRadar support from the user interface.
  2. The threadTop, pg_stat, and qlocks.out outputs. To collect these outputs, run the following command from the Console:
    mkdir -r /store/ibmsupport/refdump; for i in {1..40}; do (date; /opt/qradar/support/threadTop.sh -p 7779 --full)>> /store/ibmsupport/refdump/tomcat.out; (date; psql -U qradar -c "select * from q_locks" )>> /store/ibmsupport/refdump/qlocks.out; (date; psql -U qradar -c "select * from pg_stat_activity where state='active'")>> /store/ibmsupport/refdump/pg_stat.out; sleep 5 ; done
    Note: This command takes about 5 minutes to run and generates three files to /store/ibmsupport/refdump/ that you can add to your case.
  3. Upload all files to your QRadar case.

    Results
    A case is open and assigned to Waiting on IBM. A support representative contacts you to discuss your case. If there is an alternate number or a better contact method, you can add a note to your case with the most recent contact information.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000cwtiAAA","label":"Performance"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.4.0;7.4.1;7.4.2;7.4.3;7.5.0"}]

Document Information

Modified date:
21 July 2023

UID

ibm16412231