VCSA 6.7 Database Maintenance

[nsousaarlington]

For larger VCSA footprints, have you had to do any manual database maintenance? Any db space issues?

On page 64 of the vSphere 6.7 Performance Best Practices PDF, it mentions the following:

1) Update statistics of the tables and indexes on a regular basis for better overall performance of the

database.

2) As part of the regular database maintenance activity, check the fragmentation of the index objects and

recreate indexes if needed (i.e., if fragmentation is more than about 30%).

Or are these tasks completed automatically with the embedded Postgres database on Photon?

[jonathanjabez · Accepted solution]

Hi,

In my experience, we found the vCenter log and archives were consuming the more disk space and the partition was getting full which led to delete some archived log bundles from VCSA DB.

There may be old log bundles that are consuming the space in the /storage/log folder. Please execute the below command to remove the log files and to recover the space. Share the output of the commands once done:

df -h

Filesystem                                Size  Used Avail Use% Mounted on

devtmpfs                                  7.9G     0  7.9G   0% /dev

tmpfs                                     7.9G  876K  7.9G   1% /dev/shm

tmpfs                                     7.9G  688K  7.9G   1% /run

tmpfs                                     7.9G     0  7.9G   0% /sys/fs/cgroup

/dev/sda3                                  11G  6.6G  3.5G  66% /

tmpfs                                     7.9G  1.5M  7.9G   1% /tmp

/dev/mapper/core_vg-core                   50G  1.3G   46G   3% /storage/core

/dev/mapper/imagebuilder_vg-imagebuilder  9.8G   23M  9.2G   1% /storage/imagebu                                                                                        ilder

/dev/mapper/netdump_vg-netdump            985M  1.3M  916M   1% /storage/netdump

/dev/mapper/archive_vg-archive             50G   47G   21M 100% /storage/archive

/dev/mapper/updatemgr_vg-updatemgr         99G  540M   93G   1% /storage/updatem                                                                                        gr

/dev/sda1                                 120M   31M   81M  28% /boot

/dev/mapper/log_vg-log                    9.8G  9.0G  272M  98% /storage/log

/dev/mapper/db_vg-db                      9.8G  1.8G  7.5G  20% /storage/db

/dev/mapper/autodeploy_vg-autodeploy      9.8G   36M  9.2G   1% /storage/autodep                                                                                        loy

/dev/mapper/seat_vg-seat                   25G  7.1G   17G  31% /storage/seat

/dev/mapper/dblog_vg-dblog                 15G  1.1G   13G   8% /storage/dblog

Login to the Postgres DB and run the query:

Command: /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

Command: SELECT COUNT(EVENT_ID) AS NUMEVENTS, EVENT_TYPE, USERNAME FROM VPXV_EVENT_ALL GROUP BY EVENT_TYPE, USERNAME ORDER BY NUMEVENTS DESC LIMIT 10;

sample output:

numevents |               event_type                |          username

-----------+-----------------------------------------+-----------------------------

    777512 | vim.event.TaskEvent                     | VSPHERE.LOCAL\Administrator

    189901 | vim.event.UserLoginSessionEvent         | VSPHERE.LOCAL\Administrator

    189604 | vim.event.UserLogoutSessionEvent        | VSPHERE.LOCAL\Administrator

    155977 | esx.problem.visorfs.ramdisk.full        |

     61599 | esx.audit.usb.config.changed            |

     35769 | esx.problem.vmfs.heartbeat.recovered    |

     35219 | esx.problem.vmfs.heartbeat.timedout     |

     33840 | vim.event.AlarmStatusChangedEvent       |

     19686 | com.vmware.vc.AllEventBurstsEndedEvent  |

     19665 | com.vmware.vc.EventBurstCompressedEvent | VSPHERE.LOCAL\Administrator

More commands:

• rm -rf /storage/log/<filename with vcenter server name and date>.tgz
• rm -rf /storage/log/<filename with vcenter server name and date>.tgz
• df -h
• cd /storage/log
• du -hs *

/Jon

[Ajay1988]

Hello ,

We run auto-vacuum on VCSA databases as part o maintenance and do not run Analyze/Re-index .

Whenever the VMware Postgres database is manipulated, the records that are modified are changed, but the old copy is kept for reference. During a delete operation, the records are only marked as deleted. Although the database in the VMware vCenter Server Appliance runs a maintenance job automatically to reclaim the space and remove these unused inaccessible records, sometimes it is necessary to run the maintenance job manually.

The maintenance process is called Vacuum and the database is configured for Auto-Vacuum. However, if the changes to the database are high in number and quick, it may be necessary to run the process manually to reclaim space.

Optimize and Improve PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX - Atlassian Documentat...