Clearing events in vCenter database

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

• Events table in vCenter database contains a large number of esx.audit.ssh.session.opened and esx.audit.ssh.session.closed events

Following query can be used to check the count of events:

• Connect to vCenter database using: /opt/vmware/vpostgres/current/bin/psql -U postgres VCDB
• Execute query: SELECT COUNT(EVENT_ID) AS NUMEVENTS, EVENT_TYPE, USERNAME FROM VPXV_EVENT_ALL GROUP BY EVENT_TYPE, USERNAME ORDER BY NUMEVENTS DESC LIMIT 10;

Example output:

VCDB=# SELECT COUNT(EVENT_ID) AS NUMEVENTS, EVENT_TYPE, USERNAME FROM VPXV_EVENT_ALL GROUP BY EVENT_TYPE, USERNAME ORDER BY NUMEVENTS DESC LIMIT 10;

numevents |               event_type                |          username
-----------+-----------------------------------------+----------------------------
   3145462 | esx.audit.ssh.session.opened            |
   3144915 | esx.audit.ssh.session.closed            |

Environment

VMware vCenter Server 7.0.x
VMware vCenter Server 6.7.x

Cause

This issue is caused by frequent API calls from third-party products, generating an excessive volume of events in the database.

Resolution

This document outlines a process to regularly clear out events in the vCenter database aiming to reduce the impact on system performance.

Disclaimer: The below procedure involves interacting with vCenter database and should be performed with extreme caution. Contact Broadcom support for any assistance

vCenter without High Availability:

Initial Cleanup:

• Objective: Completely remove all events from the database.
• Procedure:
  • Stop all services: service-control --stop --all
  • Start the vPostgres service: service-control --start vmware-vpostgres
  • Execute the following SQL commands:
    sql /opt/vmware/vpostgres/current/bin/psql -U postgres VCDB <<EOF CREATE OR REPLACE FUNCTION event_full_cleanup_p () returns void as $$ DECLARE event_partition VARCHAR (200); BEGIN TRUNCATE TABLE VPX_EVENT_PARTITION_LOOKUP; -- Truncate partitions FOR part IN 1..92 LOOP event_partition = 'TRUNCATE TABLE VPX_EVENT_' || CAST(part AS TEXT) || ' CASCADE '; EXECUTE event_partition; event_partition = 'TRUNCATE TABLE VPX_EVENT_ARG_' || CAST(part AS TEXT) || ' CASCADE '; EXECUTE event_partition; event_partition = 'ANALYZE VPX_EVENT_' || CAST(part AS TEXT); EXECUTE event_partition; event_partition = 'ANALYZE VPX_EVENT_ARG_' || CAST(part AS TEXT); EXECUTE event_partition; END LOOP; TRUNCATE TABLE VPX_ENTITY_LAST_EVENT; END $$ LANGUAGE plpgsql; SELECT event_full_cleanup_p(); DROP FUNCTION event_full_cleanup_p; EOF
  • Restart all services: service-control --start --all

Regular Cleanup (Scheduled Task):

• Objective: Remove events older than 1 day.
• Procedure:
  • Create the cleanup script:
    • Copy the remove_some_events_create.sql and run_cleanup.sh files to the /storage/updatemgr directory on the VCSA.
    • Execute the remove_some_events_create_audit.sql script to create the necessary database procedure:
      bash /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -f remove_some_events_create_audit.sql
    • Modify permissions on run_cleanup.sh: chmod u+x /storage/updatemgr/run_cleanup.sh
  • Test the cleanup script:
    • Execute run_cleanup.sh at a quiet time to ensure it works correctly.
    • Note: The cleanup process may take up to an hour, especially if run shortly after the initial cleanup.
  • Schedule the cleanup script:
    • Use crontab -e to add the following line to your crontab:
      0 2 * * * /storage/updatemgr/run_cleanup.sh
      This will run the script daily at 2:00 AM. Adjust the cron entry for a different schedule as needed.

vCenter High Availability(VCHA):

Initial Cleanup:

• Objective: Completely remove all events from the database.
• Procedure:
  • Put VCHA in disabled mode.
  • Stop all services: service-control --stop --all --vmon-profile ALL
  • Start the vPostgres service: service-control --start vmware-vpostgres
  • Execute the following SQL commands:
    sql /opt/vmware/vpostgres/current/bin/psql -U postgres VCDB <<EOF CREATE OR REPLACE FUNCTION event_full_cleanup_p () returns void as $$ DECLARE event_partition VARCHAR (200); BEGIN TRUNCATE TABLE VPX_EVENT_PARTITION_LOOKUP; -- Truncate partitions FOR part IN 1..92 LOOP event_partition = 'TRUNCATE TABLE VPX_EVENT_' || CAST(part AS TEXT) || ' CASCADE '; EXECUTE event_partition; event_partition = 'TRUNCATE TABLE VPX_EVENT_ARG_' || CAST(part AS TEXT) || ' CASCADE '; EXECUTE event_partition; event_partition = 'ANALYZE VPX_EVENT_' || CAST(part AS TEXT); EXECUTE event_partition; event_partition = 'ANALYZE VPX_EVENT_ARG_' || CAST(part AS TEXT); EXECUTE event_partition; END LOOP; TRUNCATE TABLE VPX_ENTITY_LAST_EVENT; END $$ LANGUAGE plpgsql; SELECT event_full_cleanup_p(); DROP FUNCTION event_full_cleanup_p; EOF
  • Stop all Services: service-control --stop --all
  • Start all services: service-control --start –all --vmon-profile HAActive
  • Put VCHA in enabled Mode

Regular Cleanup (Scheduled Task):

• Objective: Remove events older than 1 day.
• Procedure:
  • Create the cleanup script:
    • Copy the remove_some_events_create.sql and run_cleanup.sh files to the /storage/updatemgr directory on the VCSA.
    • Execute the remove_some_events_create_audit.sql script to create the necessary database procedure:
      bash /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -f remove_some_events_create_audit.sql
    • Modify permissions on run_cleanup.sh: chmod u+x /storage/updatemgr/run_cleanup.sh
  • Test the cleanup script:
    • Execute run_cleanup.sh at a quiet time to ensure it works correctly.
    • Note: The cleanup process may take up to an hour, especially if run shortly after the initial cleanup.
  • Schedule the cleanup script:
    • Use crontab -e to add the following line to your crontab:
      0 2 * * * /storage/updatemgr/run_cleanup.sh
      This will run the script daily at 2:00 AM. Adjust the cron entry for a different schedule as needed.

Important Considerations:

• Quiet Time: Schedule the cleanup process during off-peak hours to minimize potential performance impact. Avoid scheduling it during backup windows.
• Impact: The initial cleanup will significantly reduce the database size. Subsequent scheduled cleanups should have minimal impact as long as the initial cleanup is performed correctly.

Additional Information

For additional details about editing the vCenter HA configuration refer Edit the vCenter HA Cluster Configuration

Impact/Risks: The database can become full, causing the services to crash and prevent them from restarting. Prior to this, the vpxd service will experience significant slowdowns.