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. 

Note: If the vCenter has VCHA enabled, it should be disabled and removed before making the below changes, followed by a fresh snapshot. For reference on removing VCHA: KB427313

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