Why you should delete outdated vCenter Server data
With SQL commands, you can delete superfluous vCenter Server database information and save disk space and boost system performance. But you need to monitor database table size.
VMware's vCenter Server database acts as VMware's storage repository. In vCenter, dozens of tables store information on resources, clusters, VMware Distributed Resource Scheduler, snapshots, VMware ESX hosts, virtual machines (VMs), alarms, performance statistics, tasks and events, and more.
The problem is that if you have many VMware host servers and VMs in your environment, the database can grow quite large. But this growth comes largely from a few tables that contain task, event and historical statistics (see the descriptions below). Deleting extraneous task and event files from vCenter Server database can save space, boost system performance, speed backup and minimize the chance of database corruption. In this article, we outline how to use Microsoft's SQL Server to delete unneeded information from a vCenter database.
Navigating vCenter table data
But before you can purge files, you should understand vCenter Server's table data. Here is a brief rundown of the kind of information stored in these tables.
- Task information. This table contains information on all the tasks performed in vCenter Server
- VPX_TASK
-
Event information – These tables contain information on all the events that occur on vCenter Server. For every event, there is one row in the VPX_EVENT table, which is linked to one or more rows in the VPX_EVENT_ARG by the EVENT_ID field.
- VPX_EVENT
- VPX_EVENT_ARG
- Historical statistics.These tables contain statistical counter information for the hosts and VMs that vCenter Server manages. For vCenter Server 2.0.x servers, this information was stored in a single VPX_HIST_STAT table. But in vCenter Server 2.5, this method has been changed: Daily statistics are now stored in VPX_HIST_STAT1, rolled into VPX_HIST_STAT2 for weekly stats, rolled into VPX_HIST_STAT3 for monthly stats and, finally, into VPX_HIST_STAT4 for yearly stats. Additionally, there are four sample time tables used that correspond to the historical statistic tables.
- VPX_HIST_STAT (VC 2.0.x)
- VPX_HIST_STAT1 (VC 2.5)
- VPX_HIST_STAT2 (VC 2.5)
- VPX_HIST_STAT3 (VC 2.5)
- VPX_HIST_STAT4 (VC 2.5)
- VPX_SAMPLE_TIME1 (VC 2.5)
- VPX_SAMPLE_TIME3 (VC 2.5)
- VPX_SAMPLE_TIME3 (VC 2.5)
- VPX_SAMPLE_TIME4 (VC 2.5)
Click to enlarge.
Purging data with vCenter Server
By changing the statistic interval configuration, you can indirectly purge this data with vCenter Server. When you change a particular interval, only the data for that interval is purged. If you change the weekly interval only, for example, weekly data is purged, but not daily, monthly and yearly data. You can also change the intervals so less data is collected, or disable intervals, which reduces the size of your VPX_HIST_STAT tables.
There are several reasons to delete this data. The first is simply to reduce the amount of space used on the database server. In large environments, such a database can easily be more than 20 GB in size and while this may not be a big deal on a SQL Server running on large local disk, it can be an issue on a database server that uses valuable storage area network (SAN) disk space.
The second reason is performance. The bigger the database, the more time it takes to search for data and to complete database operations such as updating indexes. Finally, the less data you have, the more efficient the database will be and the less likely you are to experience database corruption. It will also take less time to back up the database.
The bottom line is that you should ask yourself whether you really need an extensive history of task and event data. Will you ever review performance data from a year ago? Having old performance data is valuable on occasion for spotting trends. But if you don't need it, get rid of it. Also, consider not collecting this data in the first place. You might just collect the daily and weekly data to use for troubleshooting purposes and disable the longer-term monthly and yearly data.
Checking table size
With SQL commands, you can check the size of your tables. For Oracle databases, use the following SQL commands after you log in to the database with SQL*Plus or another SQL client.
select count (*) from VPX_EVENT
This command shows the number of rows (or records) in a table. For the other tables, change the table name.select num_rows * avg_row_len from user_tables where table_name = 'VPX_EVENT'
This command shows the amount of disk space the table is using in bytes, not including free table space.select bytes from user_segments where segment_name = 'VPX_EVENT'
This command shows the amount of disk space the table uses in bytes, including free space.
Click to enlarge.
For SQL Servers, you can use the SQL Query Analyzer tool that is installed as part of SQL server by using the following commands.
In the Query window, type
use <VC database name>
Then type:
EXEC sp_spaceused <table name>
Next, click the Execute Query icon or press F5. This runs the sp_spaceused stored procedure (essentially a SQL server script), which returns information about the table, including the number of rows in use, the amount of disk space in kilobytes that is reserved for the table and the amount of table space that the data takes up. To see the same information for other tables, just repeat these commands with the names of the other tables.
Click to enlarge.
Now that we know about tables and how to determine their size, we can begin purging data from them. In the next part of this tip series, we'll cover how to use the VMware-provided SQL scripts to purge data. Stay tuned.
ABOUT THE AUTHOR: Eric Siebert is a 25-year IT veteran who specializes in Windows and VMware system administration. He is a guru-status moderator on the VMware community VMTN forums and maintains VMware-land.com, a VI3 information site. He is also the author of an upcoming book titled VI3 Implementation and Administration due out in June 2009 from Pearson Publishing and is a regular on VMware's weekly VMTN Roundtable podcast.
