vCenter Database Stats Rollup Troubleshooting

vCenter Statistics SettingsVMware vCenter collects performance statistics, tasks and events for historical performance analysis and auditing.  The collection level and retention of performance statistics can be controlled through the vCenter GUI (see Administration | vCenter Server Settings | Statistics).   The level of statistics collection and retention periods can have a dramatic impact on your vCenter Server’s performance if not carefully planned and monitored.  In particular, the vCenter database can grow quite large and the database server required to support the increase in statistics increases in size and performance characteristics (increased disk IO capacity, CPU, and memory).  Fortunately, VMware has provided a vCenter database sizing tool within the vCenter client (see picture).  This is all well and good for initial sizing, and my experience shows that vCenter’s sizing estimates are fairly accurate assuming the environment remains healthy.

I recently migrated an environment from vCenter 2.5 to 4.0 and in the process switched from a Windows 2003 32-bit vCenter host and a SQL 2005 server (remote to vCenter) to a Windows 2008 64-bit vCenter server with a SQL 2008 server (again, a remote SQL server).  I experienced a few issues during the migration and thought I had worked through them all (I’ll post on those at a later date).  However, after a bit of time I found that performance statistics for objects in the vCenter were missing of not rendering at an acceptable pace.  Upon further investigation, I discovered the following warnings in the vCenter Service Status node indicating that performance rollups within the vCenter database were not taking place:

  • Performance statistics rollup from Past Day to Past Week is not occurring in the database
  • Performance statistics rollup from Past Week to Past Month is not occurring in the database
  • Performance statistics rollup from Past Month to Past Year is not occurring in the database

vCenter Database Performance Statistics Rollup

In a SQL-backed vCenter, statistics rollups are handled by the SQL Server Agent (note: if you are using SQL Server Express, statistics rollups are handled by vCenter itself as SQL Express does not offer SQL Server Agent jobs).  KB 1003570 describes this process (it applies to vCenter 2.5, but the principles in it can be applied to 4.0).  To troubleshoot and resolve the issue I opened SQL Server Management Studio and checked several items:

  1. Is the SQL Server Agent running?
  2. Are there statistics rollup jobs defined for SQL server agent?
  3. Are those jobs running?

In my case, the SQL Server Agent was running (you are prompted to configure this during the vCenter install).  However, when I checked for the presence of rollup jobs, I discovered that only a Past Day job had migrated with the database to the new SQL server.  Upon investigating the job history for that job I discovered that the job had not run since the migration (note to self: add these checks to your standard vCenter migration checklist).

To remediate the problem I completed the following steps:

  1. Remove the bad ‘Past Day stats rollupVirtualCenter’ job from the list of SQL Server Agent Jobs.
  2. Recreate the three standard stats rollup jobs.  To recreate the jobs, find SQL scripts on your vCenter server in C:Program Files (x86)VMwareInfrastructureVirtualCenter Server.  The .sql scripts you’ll need are stats_rollup1_proc_mssql.sql, stats_rollup2_proc_mssql.sql, and stats_rollup3_proc_mssql.sql.  Run these scripts in SQL Query Analyzer against your VirtualCenter Database in order from 1 to 3.  These scripts should create the rollup jobs and their associated stored procedures (this procedure is detailed at http://communities.vmware.com/thread/123715).
  3. After recreating the jobs I took a backup of the vCenter database.  The Past Day job soon kicked off to begin a stats rollup (this runs every 30 minutes by default).

I checked the server several hours later and discovered that rather than completing successfully, the Past Day job was still running and the drive holding my vCenter database transaction log was full.  Back to the drawing board..

  1. I disabled the Past Week and Past Month rollup jobs to avoid job conflicts.
  2. I backed up the vCenter database and then performed a shrink of the log file to get it back down to size.
  3. The vCenter was running as a VM, so I was able to quickly increase its disk size and use diskpart from within the guest to extend the partition.  The space required to process weeks of performance statistics is not included in the vCenter Database Sizing tool as it is assumed that the rollup/purge jobs will run as designed.

I wanted to see how bad the problem was before kicking off another job so I ran:

select count(*) from vpx_hist_stat1

against the vCenter database in SQL Query Analyzer.  The query ran for several hours (never a good sign) and eventually returned well over 20 million rows of performance statistics (thanks to http://communities.vmware.com/message/1318736 for pointing me in this direction).  I investigated options to truncate the tables (see above link), and also looked at a script from VMware KB 1000125: Purging old data from the database used by vCenter Server.  In the end, I decided to try to let the Past Day stats job run.

I stopped the vCenter  Server Service to prevent new statistics from being written to the database.  I also disabled the Past Week and Past Month SQL Agent jobs to prevent job conflicts and then manually started the Past Day job.  I had to stop the job several times as it filled the 100GB transaction log volume.  A backup & shrink operation gave me back the space on the log volume.  I saw about 300GB of transaction logs written over the course of this process, but the Past Day job eventually completed.

Finally, I re-enabled the Past Week and Past Month jobs and manually ran both of them (Past Week first, then Past Month), followed by a backup and shrink of the vCenter database.  I was impressed with the performance increase I saw in the vCenter client.  Lists and performance graphs rendered much faster than when stats rollups were not taking place.

It would be a good idea to include checking stats rollup job status and a count of rows from the vpx_hist_stat tables in the vCenter database in your regular maintenance tasks.  For other vCenter Database best practices, check out breakout session PO2061 from VMworld 2008.  If you did not attend or subscribe to VMworld, Scott Lowe covered the session in this post.  A VMworld 2009 “online only” session entitled VM3237 vCenter Databases: Setup, Management and Best Practices was also offered (subscription required).  I have not viewed this session so I cannot comment on its content.

Comments

  1. it’s called RRDtool, vmware! why am I not surprised vmware programmers are so stupid as to put metrics in a sql database where they never belong.

  2. Thanks for this write up – found it very helpful in solving our problems

  3. Thanks for the write up, I found it extremely helpful. However, I found a discrepancy in what happened on my vCenter 2.5 migration. Perhaps there were changes between 2.5 and 4.0.

    In your write-up you talk about the stored procedures not being transfered after a vCenter server migration. What I found on ours was that all the stored procedures were there, both the roll up and purge procedures, but it was the scheduled tasks in the SQL Server Agent that didn’t move.

    For your remediation to add these back you say to use the stats_rollup*_proc_mssql.sql scripts, but these add the roll up stored procedures. The scripts to install the SQL Server Agent jobs are actually the job_schedule*_proc_mssql.sql scripts.

    Again, thanks for the article.

  4. I’m writing here as after hours of troubleshooting this post fixed everything for me. A couple things to note:

    The procedures may still be existing, as such you’ll need to remove them:

    http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1009857

    Additionally, the queries you mention in your post will not recreate the jobs, take a look at this KB:

    http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1004382

Trackbacks

  1. […] The color coding may seem a bit odd and arbitrary, so here are some real-world numbers to help clarify: If you hit 10 seconds of CPU Ready out of every 20 seconds?  Big deal.  40,000 seconds out of a day’s 86400 seconds?  You’ve got problems. These are easy examples – you’re waiting for CPU cycles ~50% of the time.  But say you hit 90% CPU ready for 10 seconds out of a whole month? No big deal – a blip on the radar (it wouldn’t even show up in the vCenter Statistics Roll-ups). […]

Drop a comment below:

%d bloggers like this: