Cisco Application Velocity System User Guide (Software Version 5.0) - Database Maintenance [Cisco AVS 3100 Series Application Velocity System]

Table Of Contents

Database Maintenance

Database Archiving Overview

Configuring Archiving

Managing Optimal Database Performance

Database Maintenance

The Management Console provides the capability to automatically summarize, backup, and delete old data from the Management Console database. This chapter discusses the following database maintenance topics:

•Database Archiving Overview

•Configuring Archiving

•Managing Optimal Database Performance

Note If you have installed only a Cisco AVS 3120 Application Velocity System, reporting and database functions are not available and you will not see a Reports folder in the menu at the left side of the Management Console window. You must be running the Management Console on a Cisco AVS 3180 Management Station in order to see the Report items in the Management Console. This note does not apply to users who have performed software upgrades to AVS 5.0 from older software products or on the Velocity Appliance.

Database Archiving Overview

Over time the Management Console database can grow very large and it's possible to run out of disk space to store new data. Also, the accumulation of more data results in longer query and update times and decreases the performance of the database. Routine database archiving addresses these concerns. There are four aspects to database archiving: summarizing, backup, pruning, and clean up:

•Summarizing: Older data is aggregated and summarized so that concise trend information is maintained for future analysis without having to keep all of the individual records. Transaction data is summarized in the transaction_aggregates table, and performance monitoring data (if AppScope is used) is summarized in the summarized_performance_monitorings table.

•Backup: Older data that is summarized is backed up to external files outside the database. This enables retrieval of the data if needed for some reason by restoring from the backup files.

Note This is not a backup of the entire database, but only of the older summarized data. A periodic file backup of the entire database, logs, cache files, etc. is recommended as part of a typical system recovery plan.

•Pruning: After summarizing and backing up, the unneeded older data is deleted from the database.

•Clean-Up: Transaction logs are cleaned up and disk space is recovered via the SQL vacuum command. This command also performs query optimization analysis that Postgres uses to improve the performance of the database.

The following section describes how to configure database archiving.

Configuring Archiving

To configure database archiving, use the Database Archiving command under the Reports folder in the menu at the left side of the main Management Console, as shown in Figure 11-1.

Figure 11-1 Database Archiving Configuration Page

The names and values of the database archiving parameters that appear on this page are listed in Table 11-1

Table 11-1 Archiving Parameters

Field

Description

Maintenance Hour

Integer value in the range 0-23 that specifies the hour of the day (using the 24-hour clock) during which the archiving is to be performed. The default is 1, meaning that archiving will be performed at 1 AM every day. Choose an hour at which database load is lowest.

Current Transaction Archive Age (Days)

Integer value that specifies the age in days of current data in the transaction_aggregates table to be considered old for archiving purposes. The default is set to 30 days.

Current Performance Monitoring Archive Age (Days)

Integer value that specifies the age in days of data in the performance_monitorings table to be considered old for archiving purposes. The default is set to 30 days.

Summarized Transaction Archive Age (Months)

Integer value that specifies the age in months of summarized data in the transaction_aggregates table to be considered old for archiving purposes. The default is set to 6 months.

Summarized Performance Monitoring Archive Age (Months)

Integer value that specifies the age in months of data in the summarized_performance_monitorings table to be considered old for archiving purposes. The default is set to 6 months.

Enable Backups

This radio button controls whether or not the backup step is performed. Backup of old data to external files is performed only when Yes is selected. The default is No.

Backup Directory

Name of the directory (on the database server) where backup files are to be placed. The full absolute name of the directory must be specified and you must ensure that the directory exists and that the Postgres process has the correct permissions to write to it. By default, this is set to the empty string during database installation.

Keep Detailed Transaction Records

This radio button controls whether or not detailed transaction records are stored in the transactions table for debugging purposes. The default is No.

Detailed Transaction Records Retention Period (Days)

Integer value that specifies the number of days that detailed transaction records should be retained in the database, if Keep Detailed Transaction Records is set to Yes. The default is set to 1 day. A short retention period is recommended because this table can grow at a fast rate.

Keep AppScreen Records For Severity Level (<=)

The severity level necessary for AppScreen incident records to be retained in the database. Set the numeric severity level at which records should be retained, or choose None. The default is set to severity 5 (all AppScreen incident records).

AppScreen Records Retention Period (Days)

Integer value that specifies the number of days that AppScreen incident records should be retained in the database. The default is set to 30 days.

You can change any of these parameters to suit your needs by entering the new values and clicking Save.

Managing Optimal Database Performance

Deployment of an application appliance requires that some amount of maintenance be performed on the database to ensure optimal use and efficiency. With this in mind, as data volumes increase because of transactions, the response time for various database operations will increase at a proportional rate. In order to manage performance to an acceptable level, you need to manage the number of data elements or rows in the database.

The performance_monitorings table size is the most critical component when various reports are generated using the Management Console. Managing the size of this table is the key to obtaining optimal performance for report generation and other related activities.

The growth of this table is governed by the number of page views that the node is handling and the number of page views that are measured, as configured by the sampling percentages defined in the standard production configuration file: fgn.conf. By default, this file includes these AppScope directives: AppScopeOptimizeRatePercent and AppScopePassThruRatePercent, which control the sampling rates for client performance monitoring. It is recommended that the performance_monitorings table be kept under 8 million rows in size to maintain reasonable database performance.

In general, an application appliance node is capable of handling one million page views per day (a day is defined as a 10 hour window of operations). Assuming network peak load and usage, this would yield a result in the performance_monitorings table of about one million rows of new data. Due to this, the database can process only eight days worth of data. The need to archive the oldest data (about one million rows) is required, which would bring the total amount of data back under the threshold.

Another example is handling 500,000 page views per day. In this scenario, it is possible to store approximately 16 days worth of data in the database using a single node. However, if you have a dual node configuration, then the total number of days is reduced by half, to only eight days (assuming each node is handling 500,000 page views per day).

Another important aspect of database maintenance is the archiving of older data. Currently, the archiving process takes approximately two hours to complete. By default, this activity is scheduled to run automatically every night starting 1:00 AM. Given the daily growth of the database with one million page views as explained above, the archive process will delete approximately one million rows nightly.

It is recommended that no other processing be done on the database system during the archiving maintenance window. This includes, for example, Condenser optimization operations or scheduled report generation. If additional activity is permitted during the archiving window, the archiving process will require more time to complete.

In order to control the number of rows that are archived automatically, the archive policy must be changed on the Management Console. Let's assume that you need to archive one million data rows as stated above, which represents a single day's data. To do this, you must alter the Current Performance Monitoring Archive Age archive parameter to archive only seven days worth of data (seven out of eight days). By default, this parameter is set to 30 days.