On VCSA, /storage/log filesystem is full due to very 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, potentially causing the file system in the /storage/log partition to fill up.

In addition, the vCenter performance might be slow.

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, please ensure that a fresh backup or snapshot of the vCenter Server Appliance was create. If the affected vCenter is a member of an Enhanced Linked Mode (ELM) replication setup, please be aware that offline snapshots (in powered off state) need to be created for all of the ELM nodes.

Additionally, before implementing the workaround, please empty the postgres file (with the issue):

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

 

To work around the issue, please apply the following steps:

 

Reclaim space on /storage/log filesystem

1. Before following the next steps, please 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

2. 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

 

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();"