VCSA /storage/log partition full due to large postgres logs in /var/log/vmware/vpostgres

Products

• VMware vCenter Server

Issue/Introduction

• In VMware vCenter Server 7.0 Update 3g (VMware Postgres 13.6.0) and above, the postgresql-*log in the folder /var/log/vmware/vpostgres/ might grow to a large size.

• The file system in the /storage/log partition fills up.

• The vCenter Server performance becomes noticeably slow and users experience latency when logging in.

• Running the following command identifies postgresql.log as the largest file consuming space in the partition:

  find /storage/log -type f -print0 | xargs -0 du -h | sort -rh | head -n 10

Environment

• VMware vCenter Server 8.0.x
• VMware vCenter Server 7.0.x

Cause

• In vCenter Server 7.0U3g (VMware Postgres 13.6.0) and above, the auto_explain module was enabled by default.
• The auto_explain module provides a means for logging execution plans of slow database queries ( > 10s) automatically in the postgres logs.
• These explain plans can be very large and slow systems can generate a lot of execution plans for slow queries.
  For example,  many detailed explain plans generated for slow queries of the event tables and/or performance stat tables.

Resolution

Attention: Before making any change, ensure that a fresh backup or snapshot of the vCenter Server Appliance is created. If the affected vCenter is a member of an Enhanced Linked Mode (ELM) replication setup, offline snapshots (in powered off state) are to be created for all of the ELM nodes. 
If the vCenter has VCHA enabled, it should be disabled and turned off before making the below changes, followed by a fresh snapshot.

Step 1: Reclaim space on /storage/log filesystem

1. Empty the postgres file (with higher space utilization) to reclaim space immediately without disrupting the vpostgres service:
   

   # cd /var/log/vmware/vpostgres
   # cat /dev/null > postgresql.log

2. Before following the next steps, review the following KB articles: 
   • vCenter /storage/log is full or low
   • vCenter Server /storage/log filling up due to localhost_access.log and catalina.log in sso and lookupsvc log directories
   • Confirm that there is now enough free space in the /storage/log/ partition:

     # df -h /storage/log

3. If more space is required in the partition, clean up the vpostgres log file directory
   • In the directory /var/log/vmware/vpostgres, check the size of postgres log files older than 2 weeks

     # cd  /var/log/vmware/vpostgres
     # find . -name "p*\.log*" -mtime +14 -type f -exec du -sh  {} \; | sort -n

   • Remove these older log files if necessary:

     # cd  /var/log/vmware/vpostgres
     # find . -name "p*log*" -mtime +14 -type f -exec rm -f {} \; 

   • Check there is now sufficient free space on /storage/log/:

     # df -h /storage/log

Step 2: Reduce the auto_explain output to prevent the issue from occurring again

There are two options to reduce the auto_explain output. Neither of them requires a restart of the vpostgres service:

1. Increase the auto_explain.log_min_duration parameter to a value higher than 10s.
2. Disable auto_explain logging in vpostgres.

Option A - Increase the auto_explain.log_min_duration parameter to a value higher than 10s

1. Backup the postgresql.conf file:

   # cp /storage/db/vpostgres/postgresql.conf /storage/db/vpostgres/postgresql.orig

2. Set auto_explain.log_min_duration parameter to a value higher than 10s:

   # vi /storage/db/vpostgres/postgresql.conf

3. Find the following entry in the file:

   auto_explain.log_min_duration = 10s

4. Change this entry to:

   auto_explain.log_min_duration = 30s

5. Exit VI and save the file:

   :wq

6. Check the auto_explain settings in the postgresql.conf file:

   # grep auto_explain /storage/db/vpostgres/postgresql.conf

Sample Output:

root@<vc_name> [ ~ ]# grep auto_explain /storage/db/vpostgres/postgresql.conf
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 30s
auto_explain.log_analyze = true
auto_explain.log_timing = false
auto_explain.log_buffers = true
auto_explain.log_wal = true
auto_explain.log_triggers = true
auto_explain.log_nested_statements = true

7. Reload the vpostgres configuration from postgresql.conf:

   # /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "select pg_reload_conf();"
   

Option B - Disable auto_explain logging in vpostgres

1. Backup the postgresql.conf file:

   # cp /storage/db/vpostgres/postgresql.conf /storage/db/vpostgres/postgresql.orig

2. Check the auto_explain settings in the postgresql.conf file:

   # grep auto_explain /storage/db/vpostgres/postgresql.conf

Sample output:

root@<vc_name> [ ~ ]# grep auto_explain /storage/db/vpostgres/postgresql.conf
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 30s
auto_explain.log_analyze = true
auto_explain.log_timing = false
auto_explain.log_buffers = true
auto_explain.log_wal = true
auto_explain.log_triggers = true
auto_explain.log_nested_statements = true

3. Comment out the auto_explain related parameters:

   # sed -i 's/^session_preload_libraries/#session_preload_libraries/' /storage/db/vpostgres/postgresql.conf
   # sed -i 's/^auto_explain\./#auto_explain\./' /storage/db/vpostgres/postgresql.conf

4. Verify that the auto_explain settings in the postgresql.conf file have been commented out:

   # grep auto_explain /storage/db/vpostgres/postgresql.conf

Sample output:

root@<vc_name> [ ~ ]# grep auto_explain /storage/db/vpostgres/postgresql.conf
#session_preload_libraries = 'auto_explain'
#auto_explain.log_min_duration = 30s
#auto_explain.log_analyze = true
#auto_explain.log_timing = false
#auto_explain.log_buffers = true
#auto_explain.log_wal = true
#auto_explain.log_triggers = true
#auto_explain.log_nested_statements = true

5. Reload the vpostgres configuration from postgresql.conf:

   # /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "select pg_reload_conf();"

Additional Information

Contact Broadcom Support