Database rotation

With data constantly being added to the system, and activity being logged into system tables in the database, these tables grow in size and require management. As data sets increase in size, the amount of I/O traffic associated with actions such as cleaning, deleting, and archiving can negatively effect the performance of an instance. Additionally, working with all rows in a data set, rather than a smaller working set, can create unnecessary risk.

The Database Rotation plugin preserves instance performance and averts risk associated with querying growing data sets utilizing two techniques. Both techniques are based on the concept of managing large quantities of data by separating whole sets into individual tables based on user-specified time parameters. After this task is performed, each technique handles data in a different manner:

Table Rotation works by rotating among a small set of tables, and deleting and reusing the old tables for new data.
Table Extension works by periodically starting a new table and allowing old tables to be easily archived and removed from the system.

Table rotation plugins

Database Rotations Plugin activates Table Rotation and Extension without any tables automatically included (com.snc.db.rotation)
Database Rotations Default Tables Plugin applies Table Rotation and Extension to specific tables (com.snc.db.rotation_default_tables)

Table 1. Database Rotation
Functionality	Tables applied to
Table Rotation	`syslog` `sys_querystat` `ecc_queue` `ecc_event` `cmdb_metric` `sysevent`
Table Extension	`sys_audit` `sys_email`

Reference values converted to strings

Archived data is stored as a flat file with no reference fields to other tables. The archive process converts any references to other tables to string values.

In the case of a reference field, the string uses the display value such as the caller's user name. For example, the Caller reference field in an incident would display the string ITIL User. If the reference was a document ID and the archive rule included the option to archive related document IDs, then the string is the document ID of the related record.

It is important to note that archive records do not receive any future changes to referenced values. For example, if you change the user name for "John Smith" to "John A Smith", all active incident records automatically show the caller as "John A Smith" because of the reference between the Incident and User tables. However, all archived incident records display the user name that existed at the time of the archive. Any incident for "John Smith" continues referencing this user. Likewise, if you delete a user from the system, current incidents no longer display the deleted user as a caller. However, there can be archived incidents that still display the string "John Smith" as the user because the user existed at the time of the archive.

Table rotation and extension

The System Definition Table Rotation module allows you to define a new table rotation, a new table extension or modify an existing one.

Name: auto-generated from table name
Duration: overall time parameter for function
Initialized: sets function as active (true) or inactive (false)
Rotations: number of tables to be created within Duration
Type: indicates Extension (archiving) or Rotation (deletion) functionality

When you define a new rotation, a schedule is created and new data is subsequently written to one of the tables in the rotation group. You'll notice the that the group includes the original table plus a number of additional tables. Be aware that deleting a rotation will delete the additional tables and all the data, therefore the rotation should not be deleted if the data is needed.

See these topics for more information on managing tables:

Contents