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

How To

Summary

QRadar uses a PostgreSQL database as a data store. Vacuuming and reindexing are routine database maintenance activities that help QRadar function optimally.

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.

Note: Suggest combining this procedure with "QRadar: How to clear the Tomcat cache"
https://www.ibm.com/support/pages/node/6348546?myns=swgother&mynp=OCSSBQAC&mync=E&cm_sp=swgother-_-OCSSBQAC-_-E.
The reason is that Tomcat needs to be down for both procedures and clearing Tomcat cache also helps with system performance.
To take advantage of this combining of the 2 procedures, first start with "QRadar: How to clear the Tomcat cache".
Once you get to the last part where you would restart Tomcat, stop.Next move on to the vacuum and reindex process to finish up thus combining both procedures.

Steps

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.

STEPS:

Stop the tomcat service:
```
systemctl stop tomcat
```
Confirm the HTTPD and Tomcat services are stopped:
```
systemctl status tomcat
systemctl status httpd
```
Stop the hostcontext service:
```
systemctl stop hostcontext
```
Confirm that all the services managed by hostcontext are stopped (except ecs-ec-ingress):
```
/opt/qradar/upgrade/util/setup/upgrades/wait_for_start.sh
```
Use Ctrl-C to exit from the script.
Stop the ecs-ec-ingress service (log collection on the console stops after this command):
```
systemctl stop ecs-ec-ingress
```
Restart the hostservices service:
```
systemctl restart hostservices
```
Confirm that the hostservices service has started:
```
systemctl status hostservices
```
To vacuum the tables, run each of these commands (only after the current command is finished):
```
psql -U qradar
```
```
vacuum full verbose;
```
```
reindex database qradar;
```
```
\q
```
Entering \q followed by a carriage return exits the psql command line.
Start the hostcontext service:
```
systemctl start hostcontext
```
Confirm all the services managed by the hostcontext service are running:
```
/opt/qradar/upgrade/util/setup/upgrades/wait_for_start.sh
```
Use Ctrl-C to exit from the script. Check that log collection resumes.
Start the tomcat service:
```
systemctl start tomcat
```
Confirm that the HTTPD and Tomcat services are up and running:
```
systemctl status tomcat
systemctl status httpd
```
Check the connection to tomcat:
```
/opt/qradar/bin/test_tomcat_connection.sh
```
Note: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"}]

Tips

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

How To

Summary

Steps

Document Location

Was this topic helpful?

Document Information

UID

Share your feedback

Need support?