IBM Support

QRadar: How to VACUUM and REINDEX the QRadar PostgreSQL database?

How To


Summary

QRadar uses a PostgreSQL database as a data store. Automatic vacuuming and reindexing are routine database maintenance activities that help QRadar function optimally, but it is sometimes necessary to run these processes manually.

Steps

Vacuuming reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until vacuuming is done. Vacuuming also updates data statistics used by the PostgreSQL query planner and updates the visibility map, which speeds up index-only scans. Periodic vacuuming is recommended for tables that are frequently updated.
PostgreSQL has an autovacuum feature that routinely vacuums the tables. However, in some situations, for example, a loaded environment with thousands of offenses or a reference set with many elements, a manual vacuum is beneficial.
PostgreSQL indexes tend to get bloated, that is, they contain many empty or nearly-empty pages. This bloating happens if QRadar deletes or updates a significant number of rows on larger tables. Reindexing helps rebuild the index, ensuring that row look-ups are faster.
Before you begin
If you run a manual vacuum and reindexing, consider clearing the Tomcat cache as well to help with system performance. Clear the Tomcat cache first, then before you restart Tomcat, complete the following vacuum and reindexing procedure.

IMPORTANT:
Based on diagnostics, QRadar Support advises you whether vacuuming or reindexing are necessary in your environment. Vacuuming and reindexing are both performed on the QRadar console. When these activities are done, all services on the QRadar console need to be stopped. Due to that the QRadar GUI goes down, offense generation stops, report generation stops and log sources associated with the console cease log collection. A maintenance window is advised to perform this activity.
Prior to performing any manual maintenance on Postgres, always take a backup of the database. This is covered in Step 6. 

Steps

  1. Stop the tomcat service. 
    systemctl stop tomcat
  2. Confirm the HTTPD and Tomcat services are stopped.
    systemctl status tomcat
systemctl status httpd
  3. Stop the hostcontext service. 
    systemctl stop hostcontext
  4. Confirm that all the services managed by hostcontext are stopped (except ecs-ec-ingress).
    /opt/qradar/upgrade/util/setup/upgrades/wait_for_start.sh
  5. Use Ctrl-C to exit from the script when the services are stopped.
  6. Before continuing, take a backup of the database with the following commands: 
    1. cd /store/ibm_support/
mkdir /store/ibm_support/ (if not already present)
pg_dump -U qradar > /store/ibmsupport/qradar-<timestamp>.dump
ls -lah /store/ibm_support/
      Example:  
      pg_dump -U qradar > /store/ibm_support/qradar-02202024.dump
  7. Stop the ecs-ec-ingress service (log collection on the console stops after this command). 
    systemctl stop ecs-ec-ingress
  8. Restart the hostservices service. 
    systemctl restart hostservices
  9. Confirm that the hostservices service has started. 
    systemctl status hostservices
  10. To vacuum the tables, run each of these commands, but be sure to run them only after the last command is finished: 
    1. psql -U qradar
    2. vacuum full verbose;
    3. reindex database qradar;

    4. \q
      Entering \q followed by a carriage return exits the psql command line.
  11. Start the hostcontext service. 
    systemctl start hostcontext
  12. Confirm all the services managed by the hostcontext service are running. 
    /opt/qradar/upgrade/util/setup/upgrades/wait_for_start.sh
  13. Use Ctrl-C to exit from the script. Ensure that log collection has resumed.
  14. Start the tomcat service. 
    systemctl start tomcat
  15. Confirm that the HTTPD and Tomcat services are up and running:
    systemctl status tomcat
systemctl status httpd
  16. Check the connection to tomcat:
    /opt/qradar/bin/test_tomcat_connection.sh

    Result
    Tomcat takes some time to start. Once the Tomcat connection command shows the status as connected, you can connect to the QRadar GUI, and verify whether other functions are working as expected.

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":"TS006374901","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
29 February 2024

UID

ibm16479685

Page Feedback