-
Title
FMS repository topology cleanup scripts -
Description
A FMS takes a long time to start up or the nightly topology rollup runs for a long time (e.g. 12 hours) because there is a large number of expired topology objects.
-
Cause
The FMS should roll up old topology versions, that these numbers should decrease over time. However, there are some situations when topology objects have so many historical versions (Num Instance Versions count numbering in multi-millions) that the rollup cannot be completed in a single cycle. The rollup gets left until next DB maintenance window (when it likely again cannot complete either) with some very large numbers of objects remaining.
If there is a very large amount of objects, the topology history tables could grow quite a bit during the course of the day and be much larger than they need to be. There is also a possible issue of fragmentation, because of all the inserts and deletes performed on the table.
-
Resolution
Overview
Attached below to this Knowledgebase article is compressed zip file containing scripts to delete purge topology object history and/or purge topology objects from the FMS repository.
Included in the file are scripts for the following supported FMS repository database platforms
- MySQL
- Oracle
- SQL Server
- PostgreSQL
These scripts should be run by the database schema owner (i.e. the "foglight" database user") and should work as-is for all database platforms listed.
Delete Topology Object History Scripts
The scripts in this folder are used to recreate the topology object tables, and to remove the topology object history by keeping only a single version of each topology object. The single version that is retained will have the same state as the most recent version of the topology object. It will span a date range from the effective start date of the earliest version of the object, to the effective end date of the last version of the topology object.
These scripts were originally created for previous versions of the Knowledgebase article 89950. The scripts have been renamed to distinguish them from the similar scripts used to purge expired topology objects.
If the topology has a large number of expired objects due to previous topology growth/churn issues, then consider running the [Purge Expired Topology Objects Scripts](../purge-expired-topology-objects/README.md) instead.
Purge Expired Topology Objects Scripts
The scripts in this folder are used to recreate the topology object tables, and to remove the topology object history by keeping only a single version of each topology object, and to purge any topology objects that were not recently active. The single version that is retained will have the same state as the most recent version of the topology object. It will span a date range from the effective start date of the earliest version of the object, to the effective end date of the last version of the topology object.
When purging topology objects, the script defaults to using a period of one week to determine if objects have been recently active. That is, any object where the effective end date of the last history record is more than one week ago, will be purged by the scripts. If necessary, the scripts can be updated before being run to change the period of time that is used.
In order to remove the history without purging expired objects, use the [Delete Topology Object History Scripts](../delete-topology-object-history/README.md).Steps to run these scripts
Create new topology tables by copying across only the current version of the topology and then to drop the original tables which are copied by the script to a .bak extension using the script appropriate for each database type.
1). Download and extract the attached scripts
2). Shut down the FMS (these scripts must be run when the FMS is not running)
3). Backup the FMS repository database.
4). Have your DBA run the either delete-topology-object-history-{dbtype}.sql or purge-expired-topology-objects-{dbtype}.sql on the Foglight repository
NOTE: This script performs the following:
- Copies the object history and values into _new tables
- Renames the existing history tables to a .bak extension
- Renames the _new tables to the original history table names
-
Drops the original indexes and constraints and builds new ones
5). Check that the script completes without error. It has been known to take some time on large databases
6). Restart the Foglight Server
7). Login and navigate around the UI to ensure the topology is intact
8) Run the drop_original_topology_object_tables.sql script to remove the backup (.bak) tables that were created by the script during step 4
Note: BE CERTAIN TO CONFIRM WHICH TABLESPACE TABLES ARE BEING CREATED IN AS ERRORS IN THE SCRIPT MAY POINT TO THE TABLE BEING CREATED BUT IN THE WRONG TABLESPACE.
Note: The information in the script(s) provided is known to work successfully; however, they have not been officially tested by our Quality Control. If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by our Support and Development. Support and Development recommend always making a backup of the current database prior to execution of any script(s) that may modify it.
-
Attachments
-
Additional Information
Tables cleaned up topology_property_value topology_object_history