Why is my ITPAM database so large?

Document ID : KB000021862
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

The ITPAM database mdf file along with the transaction log become large.

ITPAM makes heavy use of the database, it is not unusual for production usage to utilize gigabytes of database space in a relatively short amount of time, and we have seen ITPam utilize over 50GB of disk space in production environments.

Understanding how ITPam utilizes the database can help you prepare for, as well as minimize this usage as much as possible.

Solution:

ITPAM has three distinct schemas.

  1. Library: (a) definitions (e.g., process definitions) and (2) runtime states (state of running processes, their dataset)

  2. Queues: persistent queues that store request for operations and responses from those operations

  3. Reporting: data collected to support reports accessible from the management console.

Library:

  1. Definition objects are stored in XML which is compressed if it gets beyond a certain size. The size occupied by definitions is directly related to the number of definitions (processes, datasets, calendars, custom ops.) and their sizes. Every new version of a new object creates a new copy. It is therefore important when you are developing new contents to avoid creating new versions unnecessarily (take the option to override the existing version when it is practical to do so).

  2. Process instances have the following lifecycle:

    1. Process instance is started: a copy of the version is created (to allow runtime modification, to avoid issues if the version is overwritten while the instance is running). That copy is broken down into multiple rows in c2oruntimeobjects and child tables. These rows store the state and results of individual operators to avoid loading/parsing/serializing too much data when operators are started, complete etc.

    2. Process instance completes: after a couple of minutes, it is rolled up into a single row (and XML) since it can no longer change.

    3. Periodically, the system checks history settings defined in the Orchestrator's policies (ITPAM client configuration browser). It moves objects from the runtime tables to equivalent archive tables. The instances remain accessible, but you have to explicitly ask for archives. At that point, their only effect on the system is space.

In version 3.1SP1 CP07 and above, you can set up a schedule to purge archived instances under the Configuration Browser tab and under the Orchestrator palette on the left. Select the orchestrator and click on the Policies tab to see the automatic archive/purge settings.

To keep things small, it is important to understand what is growing:

  • Definitions: try to limit the number of useless versions you create

  • Archive tables (they all have arch in their name): periodically cleanup manually.

  • Transaction Logs: depending on the recovery model and the database itself (SQL Server, MySQL etc..), you may end up with more or less logs. Every state change of any process instance is transactionally committed to the database; the only particularity about ITPAM is that our state data is stored as BLOBS (XML) which can get pretty big. The DBMS generates and maintains the transaction log to be able to recover.

Queues:

  • They only store pending requests/responses. They do not grow significantly.

Reporting DB:

  • That data can be in a separate database. The system inserts data in bulk, and asynchronously from activities to progress running instances.

  • There is no automatic cleaning of that data.

  • If you don't use reporting, you can turn it off entirely by adding the following in the oasisConfig.properties file:

    oasis.disable.reporting.manager=true

For SQL Server 2008 please also see the help for "How Does Shrinking the Log File Work?". Please discuss the below with your DBA first.

  • Turn the DB recovery model from full to simple

  • Shrink the DB (using shrink-file operation detailed in SQL help topic mentioned above)

  • Issue "checkpoint" command (commit)