Database events table grows without limit in VMware Cloud Director

Products

VMware Cloud Director

Issue/Introduction

The database events table is growing without limit due to a bug impacting the cleanup process.

To verify this issue, you must connect to the VMware Cloud Director database and check the current table sizes:

Connect to the database on the primary cell:
```
db
```

Execute the following SQL query to determine the size on disk. The output will reveal if the event and audit_events tables have grown excessively (for example, to 205 GB and 4 GB, respectively):

SELECT schemaname AS schema_name, tablename AS table_name, pg_size_pretty(pg_table_size(schemaname || '.' || tablename)) AS table_size, pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size, pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

Environment

VMware Cloud Director 10.x

Cause

The automated SQL cleanup task fails to complete due to a statement timeout. A previously identified bug prevents the SQL cleanup statement from completing before timing out due to the excessive size of the event table. This inability to complete the cleanup causes the tables to grow without limit.

You can observe this specific timeout block by generating a custom log. Update your log4j configuration (Configure Logging for the VMware Cloud Director Cells) with the following properties to enable debugging for the ActivityLogCleanerJob:

# Component appender for ActivityLogCleaner
log4j.logger.com.vmware.ssdc.backend.monitoring.ActivityLogCleanerJob$ActivityLogCleanerJobActivity=DEBUG, activitylogcleaner
log4j.additivity.com.vmware.ssdc.backend.monitoring.ActivityLogCleanerJob$ActivityLogCleanerJobActivity=false
log4j.appender.activitylogcleaner=org.apache.log4j.RollingFileAppender
log4j.appender.activitylogcleaner.File=logs/activitylogcleaner.log
log4j.appender.activitylogcleaner.MaxFileSize=10240KB
log4j.appender.activitylogcleaner.MaxBackupIndex=9
log4j.appender.activitylogcleaner.layout=com.vmware.vcloud.logging.layout.CustomPatternLayout
log4j.appender.activitylogcleaner.layout.ConversionPattern==%d{ISO8601} | %-8.8p | %-25.75t | %-30.50c{1} | %m | %x%n
log4j.appender.activitylogcleaner.threshold=DEBUG

Once applied, the /opt/vmware/vcloud-director/logs/activitylogcleaner.log will reveal the following statement failing to complete:

WITH delete_event_ids "
            + "AS (SELECT event_id FROM event WHERE event_time <:date LIMIT :batchSize FOR UPDATE SKIP LOCKED) "
            + "DELETE FROM event WHERE event_id "
            + "IN (SELECT event_id FROM delete_event_ids)

Resolution

Resolving this issue requires direct SQL manipulation to manually truncate the event tables, reclaim database space, and analyze statistics to ensure the ActivityLogCleanerJob schedules correctly on cell startup.

Please open a Support Request with Broadcom Technical Support and note this Article ID (448105) in the problem description. For more information, see Creating and managing Broadcom support cases.

Additional Information

For issues related to database maintenance and storage, it is recommended to subscribe to this knowledge article to receive updates on best practices.
If you require further assistance with database performance, please refer to the Contact Support page.