Performing a manual vacuum and analyze operation in vFabric Hyperic Server 5.x database tables
search cancel

Performing a manual vacuum and analyze operation in vFabric Hyperic Server 5.x database tables

book

Article ID: 327834

calendar_today

Updated On:

Products

VMware

Issue/Introduction

This article provides information on performing a manual vacuum and analyze operation on a subset of the vFabric Hyperic database tables.

Symptoms:
When vFabric Hyperic Server database tables require the vacuum and analyze operation, you may experience these symptoms:
  • Platforms display as Available when they are not available, or vice versa.
  • Metric insertion rates are slow.
  • The metric data queue backs up.
  • Applications are slow or sluggish.
  • The user interface (UI) responds very slowly.


Resolution

To manually vacuum the Hyperic Database tables:
Log in to the Hyperic Database using psql.
  1. Connect to your database VM via SSH as the root user.
  2. Change to the /opt/vmware/vpostgres/current/bin/ directory.
  3. Log into postgres:
./psql -d HQ -p 5432 -U hqadmin

Note: You will then be prompted for a password. Enter the hqadmin password.
  1. Run these psql commands against the database:
Note: You may not want to run too many at a time as these can be very time consuming so running four or five at a time is recommended.

vacuum analyze eam_metric_aux_logs;
vacuum analyze eam_service_request;
vacuum analyze eam_dash_config;
vacuum analyze eam_control_schedule;
vacuum analyze eam_autoinv_history;
vacuum analyze eam_calendar_ent;
vacuum analyze hq_metric_data_0d_0s;
vacuum analyze eam_aiq_service;
vacuum analyze eam_escalation_state;
vacuum analyze eam_resource_aux_logs;
vacuum analyze eam_monitorable_type;
vacuum analyze eam_resource_type;
vacuum analyze eam_service_dep_map;
vacuum analyze eam_measurement_templ;
vacuum analyze hq_avail_data_rle;
vacuum analyze eam_role;
vacuum analyze eam_galert_defs;
vacuum analyze eam_server_type;
vacuum analyze eam_escalation;
vacuum analyze eam_gtrigger_types;
vacuum analyze eam_registered_trigger;
vacuum analyze eam_audit;
vacuum analyze eam_platform_type;
vacuum analyze eam_principal;
vacuum analyze eam_cprop;
vacuum analyze eam_platform_server_type_map;
vacuum analyze eam_galert_action_log;
vacuum analyze eam_subject;
vacuum analyze eam_resource_group;
vacuum analyze eam_galert_aux_logs;
vacuum analyze eam_galert_logs;
vacuum analyze eam_measurement_data_1d;
vacuum analyze eam_service_type;
vacuum analyze eam_config_props;
vacuum analyze eam_operation;
vacuum analyze eam_cprop_key;
vacuum analyze eam_crispo_opt;
vacuum analyze eam_measurement_data_6h;
vacuum analyze eam_measurement_data_1h;\
vacuum analyze hq_avail_data_rle;
vacuum analyze eam_measurement;
vacuum analyze eam_audit;
vacuum analyze eam_resource_edge;
vacuum analyze eam_measurement_bl;
vacuum analyze eam_res_grp_res_map;
vacuum analyze eam_cprop;
vacuum analyze eam_srn;
vacuum analyze eam_service;
vacuum analyze eam_resource;
vacuum analyze eam_event_log;
vacuum analyze eam_agent_plugin_status;
vacuum analyze eam_alert_action_log;
vacuum analyze eam_config_response;
vacuum analyze eam_server;


Note: You will know your commands were successful when you see something similar to:
HQ=# vacuum analyze eam_control_schedule;
VACUUM
HQ=# vacuum analyze eam_autoinv_history;
VACUUM
HQ=# vacuum analyze eam_calendar_ent;
VACUUM
  1. Once you have finished running all commands and you are returned to the prompt, type \q to exit postgres.
  2. Change to the hqadmin user:
su hqadmin
  1. Restart the postgres database:
./pg_ctl -D ../data restart
  1. Once the database has restarted, SSH back into the Hyperic application VM:
cd /opt/hyperic/server-yourversionofhyperic-EE/bin/
  1. Restart the application services:
./hq-server.sh start


Once the Hyperic UI has loaded, you should notice substantial performance improvements. Our best practice is to perform these vacuum commands as routine maintenance monthly.


Additional Information

Notes:
  • The vacuum commands are available in the 2074308_vacuum_script.zip file attached to this knowledge base article.
  • Press CTRL + C to stop the vacuum process without damaging the database.
  • The length this process takes depends on several factors, such as the size of the database and speed of the storage system. On large databases, this may take a few hours. If the process is not complete in less than 24 hours, file a Support Request. For more information, see Filing a Support Request in Customer Connect (2006985).


For more information, see: How to file a Support Request in Customer Connect

Impact/Risks:
  • During the vacuum analyze, the database performance may degrade slightly.
  • If the system appears to be severely impacted by the vacuum process, stop the vacuum and contact support.

**IMPORTANT** Before proceeding, please take snapshots of both the application and database VM. Please see Managing snapshots in vSphere Web Client for details on how to take a snapshot.

Further, please ensure the Hyperic server is stopped. To stop the service:

cd /opt/hyperic/server-yourversionofhyperic-EE/bin/ and then run the command ./hq-server.sh stop

Once Hyperic has stopped, please proceed with these steps to connect to the database to run the vacuum commands


Attachments

2074308_vacuum_script.zip get_app