Troubleshooting VPX_HIST_STAT table sizes in VMware vCenter Server 5.x
search cancel

Troubleshooting VPX_HIST_STAT table sizes in VMware vCenter Server 5.x

book

Article ID: 308824

calendar_today

Updated On:

Products

VMware vCenter Server

Issue/Introduction

This article guides you on determining whether there is extensive growth in the VPX_HIST_STATx that is causing performance, sizing and rollup job issues.

Environment

VMware vCenter Server 5.0.x
VMware vCenter Server 5.5.x
VMware vCenter Server 5.1.x

Resolution

In previous versions of vCenter Server, the vCenter database contained four VPX_HIST_STATx tables where past Day, Week, Month and Year performance statistics were collected and stored. In vCenter Server 5.x, performance metrics are now stored with multiple tables storing the data for each time period.
To create a temporary table that contains the table sizes of all tables in the vCenter Server database, run the query below. After the temporary table is completed, query with the select statement included below to determine table sizes for VPX_HIST_STATx tables.
  1. Open a new SQL Query.
  2. Verify that the database selected is the vCenter Server database.
  3. From the SQL Management Studio, click New Query.
  4. Copy the query below into the query pane.
  5. Click Execute.

    Note: Replace the string VPX_HIST_STAT1% with the VPX_HIST_STATx table you wish to query for example: 'VPX_HIST_STAT2%' 'VPX_HIST_STAT3%' 'VPX_HIST_STAT4%'


    For SQL:

    create table #TEMP
    (
    [NAME] NVARCHAR(128),
    [ROWS] VARCHAR(MAX),
    RESERVED VARCHAR(18),
    DATA VARCHAR(MAX),
    INDEX_SIZE VARCHAR(MAX),
    UNUSED VARCHAR(18)
    )
    -- Find size of each table
    insert #TEMP exec sp_msforeachtable 'exec sp_spaceused "?"'
    --Replace %VPX_HIST_STAT1% with the table name
    Select * from #TEMP where NAME like '%VPX_HIST_STAT1%' ORDER BY DATA DESC
    --Drop #TEMP table from Database
    drop table #TEMP


    For Oracle:

    select table_name, num_rows from dba_tables where table_name like 'VPX_HIST_STAT1%' order by 1


    For vPostgres used with vCenter Server Appliance 5.1 Update 1 and earlier:

    sudo /opt/vmware/vpostgres/1.0/bin/psql -d VCDB vc -c "select relname, pg_table_size(oid) from pg_class where relname IN (select tablename from pg_tables where tablename like 'vpx_hist_stat4%');"

    For vPostgres used with vCenter Server Appliance 5.1 Update 2 and later:

    sudo /opt/vmware/vpostgres/9.0/bin/psql -d VCDB vc -c "select relname, pg_table_size(oid) from pg_class where relname IN (select tablename from pg_tables where tablename like 'vpx_hist_stat4%');"


  6. Determine which table has the largest growth from the query executed in the preceding step.
To truncate the VPX_HIST_STAT Table:
  1. Create a backup of the vCenter Database:

  2. Stop the vCenter Server Service. For more information, see Stopping, starting, or restarting vCenter services (1003895).
  3. Truncate the table(s) that are identified in the above query. To truncate a single table based on the output from the above script run the following query (example):

    truncate table VPX_HIST_STAT1_4


    Note: If there are multiple vCenter Server databases on the same instance, this script will not distinguish between databases and will truncate data as populated from systables.


Additional Information

How to stop, start, or restart vCenter Server services
Reducing the size of the vCenter Server database when the rollup scripts take a long time to run
Backing up the vCenter Server database running on Microsoft SQL or SQL Express server
Backing up and restoring the vCenter Server Appliance 5.x vPostgres database
VMware vCenter Server 5.1 における VPX_HIST_STAT テーブル サイズのトラブルシューティング

Powered by Wolken Software