Target database sizing

The main factors that affect database growth (and hence your database sizing requirements) are: the size of an event, the rate at which events are passed to the database, and the combination of the gateway operating mode (audit or reporting) and the mix of event types (insert, update, or delete). The following topics discuss these factors and how to assess your system to determine your optimal sizing requirements.

Tivoli Netcool/OMNIbus event size

The target database row size should correspond closely to the Tivoli Netcool/OMNIbus event size. This is because the supplied database schemas broadly mirror the Tivoli Netcool/OMNIbus schema.

The size of a row, or individual event, depends on the following factors:
  • Sizes of individual fields
  • Whether custom fields have been added
  • Whether default fields have been omitted
  • Whether fields are actually populated with data
The maximum allowable field size can be determined by describing the ObjectServer tables by running nco_sql and executing the following commands:
> describe alerts.status
> go

The maximum possible size of an event can be determined by a summation of a table's field sizes.

The following table shows the maximum allowable row sizes for a default Tivoli Netcool/OMNIbus 8.1 installation:
Table Maximum row size

alerts.status

10284 bytes

alerts.journal

4347 bytes

alerts.details

1028 bytes
The following table shows the typical row sizes that you are likely to encounter in most practical applications:
Table Typical operational row size

alerts.status

2048 bytes

alerts.journal

512 bytes

alerts.details

0 bytes

Event rate

The rate at which events are passed to the gateway for forwarding to the database can be assessed empirically (observed over a period of time), or an assessment can be made of the expected or required throughput.

The extent to which throughput correlates to input from data sources such as probes may be significantly affected by deduplication. Deduplication converts inserts into the ObjectServer (with the same Identifier values) to updates (incrementally increasing the Tally field) thus limiting ObjectServer size and potentially limiting the size of the target database. The effect of deduplication may be to reduce data volume by a factor of 10. To calculate the volume reduction that deduplication has on a particular data flow, calculate the ratio of actual events to inserted events in alerts.status by dividing the tally sum(Tally) by the count count(*).

Filter conditions applied in gateway configuration will also restrict the gateway's interest to a subset of events placed into the ObjectServer, and so limit the size of the target database.

Operation mode

Database gateways operate in one of two modes:
  • Audit: In audit mode, all Tivoli Netcool/OMNIbus event types (inserts, updates and deletes) are forwarded as inserts, thus maintaining an audit trail within the target database, subject to (or restricted by) the granularity of the IDUC cycle.
  • Reporting: In reporting mode, both Tivoli Netcool/OMNIbus updates and deletes are forwarded as updates to previously inserted rows.

A gateway operating in reporting mode is likely to populate the database with less data. However, triggers included in the default reporting database schemas populate additional tables with summarized data, from which reports are run. Typically an additional table will be populated and maintained in the database for each report that may be run. These tables are relatively small in comparison to the main status table; see below for further information.

Note: Database gateways do not delete rows from target database tables. Thus target databases will grow in size indefinitely without database pruning or archiving taking place as a separate activity. This is independent of the mode of operation of the gateway.

Monitored tables

The configuration of the gateway determines which Tivoli Netcool/OMNIbus tables are monitored. Most users configure gateways to monitor just the alerts.status table, but you can also monitor the alerts.journal and alerts.details tables. The alerts.details table, even if monitored by the gateway, is generally not populated with data in default probe configurations. In general, it is populated only in debugging or setup scenarios. Currently, none of the default database gateway reports depend on data received from the alerts.details table.

Although database gateways are primarily used for receiving data from the three main tables (status, journals and details), other tables may be monitored in a custom setup. This would impact on database size requirements.

Journal entries

Journal entries fall into two main categories: those automatically generated by automations, for example when an event is acknowledged or deleted by a right-click tool defined in the event viewer, and those created by users. Automatically generated journals are generally short, and the ratio of the number of journal entries to the number of events is low. The size of user generated journals is determined by the individual user behaviour or policy.

Reporting tables

As mentioned above, the reporting schemas contain other tables in addition to the analogues of the three main Tivoli Netcool/OMNIbus tables (status, journal and details). These fall into two categories: tables from which reports are generated (one per report definition, currently there are four, generally named rep_audit_fieldname, and containing one row per status event) and tables of mostly static or rarely changing data. The second type are generally small and can be ignored in sizing calculations, or absorbed into margins for error. Rows in tables used for generating reports are typically less than 256 bytes.

Target implementation and tuning

Multiplying event rate by event size provides a good rough estimate of the target database size, but database implementation and tuning can easily, and significantly, increase these calculations. One factor to consider is block size and how empty or full you allow data blocks to become when they are updated.

Depending on the database implementation and the level of tuning, you can expect to increase the rough estimate by a factor two to four.

Assessing your system

One way to assess your system would be to run the Flat File Gateway and monitor the growth in its output file. However, note that the output of this gateway will be closer to that of a database gateway running in audit mode rather than reporting mode, because all event types are written (analogous to inserted) to the output file. Nevertheless an analysis of the output file will provide an insight into the mix of inserts and updates encountered in a particular system. For a database gateway running in reporting mode, updates and deletes can be largely discounted from calculations.

Note: 4 byte integers within an ObjectServer will equate to larger amounts of data in the output of the Flat File Gateway due to their representation in character format.

Use the following formula to calculate a rough annual database sizing requirement:

<inserts per day> * (<bytes per event> + (<number of report tables> * <bytes per report table row>) * <52 weeks> * <7 days>) / <bytes per GB>

For example, using the following values:
  • 10,000 inserts per day, after deduplication
  • 2048 bytes per event
  • 4 report tables
  • 256 bytes per report table row
  • 52 weeks
  • journals and details not included

The annual database storage requirement would be:

(10000 * (2048 + (4 * 256)) * 52 * 7) / 1024^3 = 10.4 GB

<inserts per day> * (<bytes per event> + (<number of report tables> * <bytes per report table row>) * <52 weeks> * <7 days>) / <bytes per GB>

Housingkeeping event data

Periodically you will need to purge old event data from the database after consuming the event data for reporting or audit purposes. The following sample SQL shows how to delete data from the database that is older than 60 days for both DB2 and Oracle.

The three key tables are the reporter_status, reporter_journal and reporter_details tables. When the gateway is running in REPORTER mode, the secondary audit tables that track changes in Severity, Acknowledged state, who the OwnerUID and OwnerGID also need to be cleaned.

DB2


# Primary Reporting or Audit tables
delete from reporter_status where lastoccurrence < CURRENT DATE - 60 DAYS;
delete from reporter_details where identifier not in (select identifier from reporter_status);
delete from reporter_journal where chrono < CURRENT DATE - 60 DAYS;
# Secondary audit tables (Reporting mode only)
delete from rep_audit_severity where enddate < CURRENT DATE - 60 DAYS;
delete from rep_audit_ack where enddate < CURRENT DATE - 60 DAYS;
delete from rep_audit_ownergid where statechange < CURRENT DATE - 60 DAYS;
delete from rep_audit_owneruid where statechange < CURRENT DATE - 60 DAYS;

ORACLE


# Primary Reporting or Audit tables
delete from reporter_status where trunc(lastoccurrence) < trunc(sysdate) - 60;
delete from reporter_details where identifier not in (select identifier from reporter_status);
delete from reporter_journal where trunc(chrono) < trunc(sysdate) - 60;
# Secondary audit tables (Reporting mode only)
delete from rep_audit_severity where trunc(enddate) < trunc(sysdate) - 60;
delete from rep_audit_ack where trunc(enddate) < trunc(sysdate) - 60;
delete from rep_audit_ownergid where trunc(statechange) < trunc(sysdate) - 60;
delete from rep_audit_owneruid where trunc(statechange) < trunc(sysdate) - 60;

Additionally, you may also apply partitioning in the database tables. When the database tables are filled with a large number of events, the database performance will be affected and deadlock and timeout issues may occur. This will even cause the gateway insert/update operations to fail on the database.