SDDC Manager /data partition repeatedly reaches 100% utilization

Products

• VMware SDDC Manager
• VMware Cloud Foundation

Issue/Introduction

• SDDC Manager /data partition filling up to 100% frequently. 
• SDDC UI inaccessible with "502 Bad Gateway" error. 
• Error in SoS logs -/var/log/vmware/vcf/sddc-support/vcf-sos-svcs.log

sosrest[1906]: sqlite3.DatabaseError: database disk image is malformed


• Repeated connections and disconnections occurring on the SDDC manager. In the auth.log - 
  
  sshd[860488]: Received disconnect from x.x.x.x port 48958:11: disconnected by user
  sshd[860493]: Received disconnect from x.x.x.x port 48966:11: disconnected by user
  sshd[860498]: Received disconnect from x.x.x.x port 48974:11: disconnected by user
  
  
• Session Table in the SDDC DB is disproportionately large in size - 
  

  /usr/pgsql/13/bin/psql -U postgres -h localhost --dbname sddc_manager_ui -c "\dt+"

   psql -U postgres -h localhost --dbname sddc_manager_ui -c "\dt+"
                                   
 Schema |         Name         | Type  | Owner | Persistence | Access method |  Size  |
--------+----------------------+-------+-------+-------------+---------------+--------+
 public | application_settings | table | vcfui | permanent   | heap           | 16 kB  |
 public | persistent_state     | table | vcfui | permanent   | heap           | 16 kB  |
 public | session              | table | vcfui | permanent   | heap           | 44 GB  |
 public | user_preferences     | table | vcfui | permanent   | heap           | 176 kB |
  
  

Environment

VMware Cloud Foundation

Cause

Frequent connections and disconnections to the SDDC Manager lead to the Sessions table getting updated at a high rate. This results in the Sessions table growing significantly in size, leading to high utilization of the /data partition.

Resolution

To resolve this issue, tune the autovacuum settings for the Session table to be more aggressive for improved space reclamation. 

IMP -  Take  snapshot of the SDDC Manager. 

1. Take SSH session to the SDDC Manager with vcf account and elevate to root. 
2. Run the command to connect to DB - 

/usr/pgsql/13/bin/psql -h localhost -U postgres -d sddc_manager_ui

3. Run the command to change the autovacuum configuration - 

ALTER TABLE session SET(autovacuum_vacuum_threshold=20, autovacuum_vacuum_scale_factor=0.1);

4. Exit the database - \q

5. Restart all SDDC manager services -
/opt/vmware/vcf/operationsmanager/scripts/cli/sddcmanager_restart_services.sh

6. Verify partition size - df -h 

Additional Information

The issue can also occur due to corruption in Session Table. If the /data partition remains at 100% and the steps above do not reclaim space, the table may require a TRUNCATE or a VACUUM FULL. Refer to KB - SDDC Manager /data partition filling up to 100% frequently