Issues & Troubleshooting

vCenter Database Stats Rollup Troubleshooting

September 17, 2009 by Josh Townsend 5 Comments

VMware 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

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:

Is the SQL Server Agent running?
Are there statistics rollup jobs defined for SQL server agent?
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:

Remove the bad ‘Past Day stats rollupVirtualCenter’ job from the list of SQL Server Agent Jobs.
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 https://communities.vmware.com/thread/123715).
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..

I disabled the Past Week and Past Month rollup jobs to avoid job conflicts.
I backed up the vCenter database and then performed a shrink of the log file to get it back down to size.
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 https://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.

RAMDisk Usage in a vSphere Environment

September 11, 2009 by Josh Townsend 2 Comments

I had some folks from our .NET development team come to me with a problem today – their ASP.NET code was taking forever to recompile after updates to the code base. But these guys are cool – they came with a proposed solution (most people who grace my office door are simply dropping off problems). Their solution? A RAMDisk mounted in a VMware Windows guest. I give them credit for a novel approach, but I could see some issues:

What would happen if the balloon driver kicked in and demanded the memory the RAMDisk was running on?
A reservation would get around the balloon driver issue, but there is no way to specifically target the 512MB of RAMDisk, all memory in the VM must be reserved.
I’m a pragmatic Windows systems administrator at heart, with a heap of systems and processes to manage and monitor. I don’t want the additional burden of making sure the RAMDisk loads at boot, keeps a consistent image across boots, can be easily updated by new code pushes, and remains compatible with new VM hardware and Tools versions.
A RAMDisk would take from what are already memory constrained VM’s, possibly hurting performance more than helping.
If the disk subsystem is slow enough to get you thinking down the path of a RAMDisk, maybe it’s time for a new SAN…

I did some Googling around and couldn’t find any decent info. I did find a few hits on people running VMware guests entirely inside a RAMDisk – a concept that peaked my interest almost enough to think about trying it just to say I did…. Have any of you experimented with a RAMDisk inside a VMware guest? If so, what did you take away from the setup? Was there a performance gain? Where there gotcha’s? Leave a comment if you have experience, guesses, or advice on this idea.

ESXTOP Batch Mode Analysis with Windows Perfmon

September 10, 2009 by Josh Townsend 12 Comments

I needed to grab some stats from my ESX hosts for off-line analysis so I fired up my trusty ESXTOP intent on using batch mode to capture a .csv formatted output. I started to manually select the counters I was interested in while working in ESXTOP interactive mode (you can save your selected counters to the esxtop configuration file with the ‘w’ command) and thought that there must be a better way. I found that better way in the VMware Performance Community: https://communities.vmware.com/docs/DOC-3930. There is now a -a switch that can be used to include ALL performance counters. I’m sold.

I wanted detailed information, so I decided on a 15 second capture interval to run for a 2 hour window. Here’s the command I used:

esxtop -a -b -d 15 -n 480 > /tmp/esxtopout.csv

where -a is for ALL, -b is for batch mode, -d is for delay, and -n is for the number of iterations ((60/15)*60*2). I wrote out the results to a .csv in /tmp. The resulting CSV weighed in at a whopping 100MB after 2 hours.

The CSV can be analyzed in Excel (pivot tables work well for this) or in Windows Perfmon. I opened the log in Perfmon as I was after basic Min/Average/Max counters and Perfmon makes those easy to see. When adding the CSV log to Perfmon, you are prompted to select counters. I added all instances of Commands/sec, Reads/sec, and Writes/sec from Physical Disk (I was gathering some IOPS counts for a new storage proposal). I got a bit more than I bargained for: a mostly unresponsive Perfmon window and the ugliest darn graph I’ve ever seen.

Switching from a graph view to the report view allows you to easily view and remove specific counters that you are not interested in, or open the Properties of the data set, switch to the data tab and bulk select counters that you want to remove. I was not interested in vmhba1:x, specific VM’s or worlds, so I killed all of those, leaving just the base iSCSI device (vmhba32 in my case).

After some cleanup the graph looked a bit better and more importantly, I was able to easily read my Min/Average/Max stats:

Here are the takeaways –

ESXTOP is a powerful utility for performance monitoring
All stats (-a) can result in a huge file – use it wisely in batch mode; else use interactive mode to select your counters and write them to the user-defined configuration file. Invoke the config file with the -c option when running in batch mode.
Consider using vscsiStats for more granular reporting of storage performance and storage workload characterization.
ESXTOP physical disk stats do not include NFS volumes.

Do you use other tools or methods to collect basic disk IO counters for storage sizing purposes? If so, leave a comment describing your approach!

Balloon Driver Problems with SQL

September 9, 2009 by Josh Townsend 7 Comments

I have been meaning to write this up for a while; Scott Drummonds’ ‘Love Your Balloon Driver’ post today at his Virtual Performance blog gave me a nice reminder. I actually caught a sneak peak at the graphs with an explanation from Scott at his instructor-led lab at VMworld 2009. Scott calls out that the only workload they discovered suffers from balloon driver activity is Java. The reason for Java’s problems with balloon driver activity is that Java itself runs in a VM and so the guest OS cannot properly determine which pages should be swapped out when the balloon driver calls for it.

My experiences causes me to agree with Scott and the whitepaper he cites – in a properly designed and equipped environment the balloon driver is not detrimental for most every workload to a point. However, I recently discovered in a client site that the balloon driver can cause significant issues when the environment is poorly designed and under-sized. Here the background:

I was called into an already established environment where the client was running on an older blade with VMware ESX 3.5. The blade maxed out at 16GB RAM and had dual dual-core CPU’s with no hope for an upgrade. On the blade was a single guest VM running Windows 2003 with SQL 2005, in it’s full 32-bit glory. The VM was configured with 4 vCPU’s and 16GB of memory. Some of you can probably already guess where this is going….

The x86 Windows guest had PAE configured, and SQL took advantage of AWE to use the additional memory beyond the 4GB limit of a 32-bit system. Additionally, the Windows guest had the /3GB switch enabled in boot.ini. Finally, as per SQL best practices, the ‘Lock Pages in Memory‘ permission was granted to the SQL Server service account. What the guest was left with was 1GB of kernel mode memory and 15GB of User Mode/Extended addressable memory.

And here’s the problem. The client was using ESX, not ESX 3.5, so the Service Console required memory. In this case, the service console had approximately 512MB allocated to it. Futhermore, VM’s require some overhead on ESX to run. The memory overhead consumed by a Windows guest on ESX 3.5 with 4 vCPU and 16GB of memory is a bit more than 512MB. On a properly sized ESX server with multiple similar guests/workloads, you could probably gain much of the overhead back through transparent page sharing; but in this case I had a 1:1 P2V ratio. If you are any good at math you see that the environment is running about 1GB short of memory. A quick check of the balloon driver stat in vCenter show that the balloon driver was constantly active and demanding about 1GB back from the guest… constantly.

Under normal circumstances this might not be an issue, but in this case the Windows guest was being absolutely punished. The guest CPU’s were pegged at 100% with an excessive amount of kernel time, often indicating IO issues. And indeed I did experience terrible disk and network performance on the guest. At the root of the problem is this – the Lock Pages in Memory permission allows SQL to get a firm grasp on the user mode memory available to it (15GB) and lock it up. This left the already starved (because of the 3GB switch in the boot.ini) guest kernel with it’s 1GB the only thing the balloon driver could really swap out.

The client suggested a reservation of 16GB on the VM, knowing that memory reservations prevent balloon driver activity. I calmly asked them to back away from the keyboard as I explained how if a starved guest was bad, how much worse a starved Service Console would be. In the end the fix was quiet easy – I convinced the customer that they should reduce the amount of memory allocated to the guest by about 1GB, enough to let the 512MB SC and the 512MB of overhead run without contention. I was able to show them the difference between allocated and active memory in vCenter – the 1GB being surrendered was not really being actively used, SQL just had it locked up. In fact, surrendering the 1GB of memory back to ESX breathed new life into the guest VM, bringing its performance back in line with expectations.

Ideally, I would have brought in a bigger ESX server that could serve additional VM’s, driving greater levels of efficiency across the environment. It just wasn’t an option for the client in this case. In the end, the problem was fixed and I was reminded just how fun it can be to explain some of these backwards sounding virtualization concepts to customers – fewer vCPU’s can lead to better performance of guests, less guest memory can fix performance issues, and increasing the quantity of similar guests on a host can drive better performance to a point because of transparent page sharing.

Stay tuned over the next few weeks as I digest and write on my VMworld experience – from VMUG activities to Paul Maritz’s press conference announcing the vCloud Express, and plenty of great sessions in between. Like many of you, I returned from VMworld with quite a backlog of work but I’ll do my best to squeeze in some posts and tweets.

Virtual Infrastructure Client Opens Off Screen

July 16, 2009 by Josh Townsend 1 Comment

A user reported an issue with one of the VM’s in my environment this morning. It seems that an automated process had spun up the CPU to 100% in the Windows guest and the system was deadlocked. I was still at home when I received the message on my BlackBerry, so I fired up the VPN on my Windows 7 laptop, opened the VI3 client and….., um, where is it? The VI3 client icon was in the taskbar, but the app was nowhere to be found – it had opened off-screen where my secondary monitor usually lives. This is nothing new for the VI client – I have experienced it numerous times in the past. But this was my first time with the problem on Windows 7.

Pre-Windows 7, I would have right-clicked the Windows taskbar for the app, selected ‘Move’, and then used the keyboard arrow keys to guide the phantom window home. Windows 7 does not have the same Windows positioning options on a right-click to the taskbar so I had to find another way. Enter Windows shortcut keys. Here’s how I brought the VI3 Client window back into view:

Make sure that the VI3 Client window is in the foreground by selecting it in the taskbar. You’ll know that it is in the foreground when the taskbar icon gets a white glow as pictured here:
Press the hotkey combination: “ALT+Space, M” for Move.
Use the keyboard arrow keys to move the window to your active monitor, pressing “Enter” once the window is visible to commit the move.
If the arrow keys fail to move the window and/or you hear the Windows error sound, your VI3 Client windows is probably maximized. The move option is not available when a window is maximized. To work around this condition use the hotkey combination: “ALT+Space, R” for Restore. You should now be able to move the window using steps #2 & #3 above.

If you are still really struggling, break out the trusty old registry editor and follow along:

Close any open VMware Infrastructure Client windows
Navigate to HKEY_CURRENT_USERSoftwareVMwareVMware Infrastructure ClientPreferencesUI
Locate the ApplicationLocation key. This key provides the X-Y coordinate for the VI Client window at startup.
Modify the string value to 0-0. This value will cause the VI3 client to open in the center of your primary display.
If you run different sized/resolution displays, you may also want to change the ApplicationMaximized or ApplicationSize keys to fit your needs.
Launch the VMware Infrastructure Client and get back to work.