VMware Cloud Community
nsousaarlington
Enthusiast
Enthusiast
Jump to solution

VCSA 6.7 Database Maintenance

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?

Reply
0 Kudos
1 Solution

Accepted Solutions
jonathanjabez
Hot Shot
Hot Shot
Jump to 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

View solution in original post

Reply
0 Kudos
2 Replies
jonathanjabez
Hot Shot
Hot Shot
Jump to 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

Reply
0 Kudos
Ajay1988
Expert
Expert
Jump to solution

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

If you think your queries have been answered
Mark this response as "Correct" or "Helpful".

Regards,
AJ
Reply
0 Kudos