vRealize Orchestrator workflows begin running slowly overtime

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:
vRealize Orchestrator workflows that query the vmo_logevent table may run more slowly over time. All functions of the vRealize Orchestrator system seem to be functioning as normal, but workflow runs take longer and longer to complete.

Environment

VMware vRealize Orchestrator 7.3.x
VMware vRealize Orchestrator 7.0.x
VMware vRealize Orchestrator 7.4.x
VMware vRealize Orchestrator 7.2.x
VMware vRealize Orchestrator 7.1.x

Cause

By default, the originatoruri column on the vmo_logevent table is not indexed. Queries on the vmo_logevent table that are filtered by originatoruri will take longer as the vmo_logevent table grows.

Resolution

To resolve this issue, add an index on the originatoruri column for the vmo_logevent table to dramatically speed up these queries.

1. Ensure there is enough storage space on /storage/db partition
   • Run the command to see available disk space:

df -h /storage/db

• ​​​​Query the current size of the vmo_logevent table. The new index will be smaller than the entire table.

SELECT pg_size_pretty(pg_relation_size('vmo_logevent'::regclass));

2. Log in to the VMware vRealize Automation virtual appliance using SSH.
3. Launch PostgreSQL CLI
   • cd /opt/vmware/vpostgres/current/bin
   • su postgres
   • psql vcac
4. Run the following query to create the index for originatoruri in the vmo_logevent table.

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_logeventoriginatoruri01 ON vmo_logevent (originatoruri);

Note: It is safe to create the index while vRealize Automation services are running, however it will take longer than if the services are stopped. 

5.  Inspect the vmo_logevent table to confirm the index has been created
   • \d vmo_logevent
   • Confirm the new idx_logeventoriginatoruri index appears in the "Indexes:" section of the output
   • \q