How to remove past data from the Foglight database to reduce its size? (52431)

After applying a  Retention Policy to purge data aged older than 4 months on the Foglight Management Server (FMS), the size of the database does not shrink and there is still data in dashboards that is older than 4 months.

Is it possible to remove past collected data from the Foglight 5 database to reduce its size?

Defect ID FGL-14513 causes long term generations marked as roll-up complete not to be revisited when retention policies change, which causes the data that was previously rolled up not to be purged.  FGL-14513 was replaced with FGL-13215.

Using Foglight 5 retention policies and applying a purge period to any defined topology types will only perform this purge on any data this is no older than the defined purge age. Only metrics collected *after* the retention change will adhere to this new policy. It does not affect already-existing metrics.

For example, if  a Retention Policy has been set on the topology type TopologyObject with an age of 4 months and a Roll-Up Period of purge, data older than the 4 month time period will not be removed from the database. However, as data under the age of 4 months approaches and passes the 4 month age, it will be purged.

STATUS: FGL-13215 was implemented in Foglight 5.7.*.   

WORKAROUND:

NOTE:  Oracle Database Repository: Please review the storage-config.xml file before replacement.  If using Observation Tablespaces for Data, Index, Lob definitions, then this will require editing the existing storage-config.xml file rather than replacing with the attached examples.  Please contact Quest Support if there are any questions as improper modification of the storage-config.xml file can cause major issues.

The configuration file $FGL_HOME/config/storage-config.xml ($FGL_HOME is where Foglight/vFoglight has been installed) is used by the FMS to determine how long data is stored in the database. This file contains 3 generation spaces and data is moved from one generation to the next based on the configured retention policies. Changing the generation size means that the server isn’t able to store data for longer than the specified duration.

Be sure the appropriate change has been made to the Retention Policy section of the Data Management Dashboard. The Retention Policy and Storage-config.xml need to match in order to avoid problems and have a successful database purge.

1) Storage-config.xml

Below are attachments of 5 types of final storage values plus the default 100 Years in the attached SOL52431.zip file.
NOTE: If a different final storage value is required, please contact Support to validate the storage value if there are concerns.

2-Week-storage-config.xml
1-Month-storage-config.xml
3-Month-storage-config.xml
6-Month-storage-config.xml
1-Year-storage-config.xml
100-Year-storage-config.xml

1a) Please extract the appropriate file and rename the file to storage-config.xml

1b) Save the a copy of the original file in $FGL_HOME/config and replace with the selected renamed storage-config.xml file.

2) Load Storage-config.xml changes into the FMS

NOTE: The new values *must* be loaded into the JMX console; a reboot of the FMS server will NOT pick up the changes. If the JMX console is not available (in the event it has been disabled) - run the FMS Script Load listed below from the FMS script console to load the Storage-config.xml

JMX LOAD: Once the file has been modified, the new values need to be loaded into Foglight. This can be done as follows:

1. Log in to the jmx-console (http://fmsmachinename:port/jmx-console.

  Use the 'foglight' user to access jmx-console. If already logged in to the FMS with another user, an access denied error will appear. Close the browser and login again.

  Here is an example URL for the JMX Console. Change the server name and port number 8080 to the values appropriate for the Foglight install:    http://FMSServer:8080/jmx-console

2. Click on the "service=StorageManager" link (under com.quest.nitro section)

3. Look for "com.quest.nitro.service.persistence.obs.config.StorageConfiguration mergeConfiguration()"
In the file name field, enter the location/name of the modified storage-config.xml file.
Here are two examples, one for UNIX, one for WINDOWS:

UNIX:
config/storage-config.xml

WINDOWS:
config\storage-config.xml

Click Invoke

4. A successful update will result in the a message similar to below
StorageConfiguration: id=1; version=2129; partitions-per-destination=3

5. Go back to the StorageManager screen and invoke "java.lang.String diagnosticSnapshotAsString()"
Get a screen shot of this screen as proof the storage.config file was loaded. This example is for a 2 Month retention.

Storage configuration: num partitions per destination=3

Generations:
    StorageGeneration id=null; number=0; version=0; timeslice-size=8 HOUR; generation-size=3 DAY
    StorageGeneration id=null; number=1; version=0; timeslice-size=3 DAY; generation-size=2 WEEK
    StorageGeneration id=null; number=2; version=0; timeslice-size=1 MONTH; generation-size=2 MONTH

FMS Script Load:  Run this from the FMS Script Console if jmx-console access is not possible.

import com.quest.nitro.service.util.MBeanRef;
import javax.management.ObjectName;
def storageMgr = new MBeanRef(ObjectName.getInstance("com.quest.nitro:service=StorageManager")).ref();
def storageConfigXml = "config/storage-config.xml";
storageMgr.mergeConfiguration(storageConfigXml);

Data Removal Details
In a urgent state, please see SOL97501 for forcing a purge .

Other Information - Data Management: Delete and Purge Icons

There are also options to delete and purge on the Data Management screen under the category "Manage Monitored Objects". As there is some confusion about how they function, here is an explanation.

The “Delete” button will just delete the topology object.
The observations will remain in the database but will be removed when the tables in which they reside are next rolled up (or truncated for the shorter term tables).

The “Purge” option will save some configuration to record the fact that the user wants to discard the observations older than a certain age.

If you have selected Purge from the Data Management Dashboard, then procede with the scheduled task "Off-Hours Database Maintenance".

Off-Hours Database Maintenance: Schedule to remove observations from the database. The scheduled task will process all the configured purge operations in a single pass over the observation tables to discard the observation values. This can be an expensive operation if there are a lot of observation history in the database. It also may not have much effect if data is only being purged for a fairly small percentage of objects. It would really only be worthwhile to request a purge if a significant amount of data needs to be purged. Otherwise it might be better to just use the “Delete” option and let the values age out.

Additional Information:

Note that the change can be a temporary one to trigger the data to be purged. Once the older data has been removed, the generation size can be increased to allow data to be retained for a longer period of time. Assuming that retention policies have been changed in the process the server would hopefully only retain a more manageable set of data going forward.

There is a periodic task that runs on the server every 5 minutes to check the generations and makes sure that the necessary tables are in place. Changes made to the schema are deliberately performed slowly to prevent schema changes while operations are being performed on a table. Once the configuration has been loaded, it will take about 5 minutes for the server to recognize that there are tables that it no longer needs. One will observe messages in the FMS log regarding the timeslices being marked as OBSOLETE.  Five minutes later the server will deallocate the timeslices. Messages then will be logged about tables changing to an UNASSIGNED state when they are truncated.

Confirm that the purge has taken place by examining the Diagnostic Snapshot of the StorageManager. Output like the following should be noted:

ObservationGeneration id=94998a1a197bc84701197bc92db40068; number=2
ObservationTimeslice id=94998a1a1ac5c13a011ad8484a6e0e82; start-time=2008-07-01 00:00:00.0; end-time=2008-08-01 00:00:00.0; ...

The ObervationGeneration will be number=2 for the final (oldest) generation changed, and no ObservationTimeslice entries with an end-time that is outside of that generation should be seen.

Reclaiming Table Space

MySQL with innodb engine does not shrink the tablespace file, even after truncating the tables.

To decrease the size of the tablespace, use the following procedure:

NOTE:  Be sure a cold backup of the FMS directories (copy directories to another drive) exists before performing this task.

1. Stop the Foglight Management Server:
    $FOGLIGHT_HOME\bin\fms -q

2. Start up MySQL in standalone moder:
    $FOGLIGHT_HOME\bin\runDb.bat

3. Use mysqldump to dump all InnoDB tables:
    cd $FOGLIGHT_HOME\mysql\bin
    mysqldump -u [database_user] –p -P[database_port_number] –h [database_hostname_or_ip] [database_name] --routines > mysqldata.sql

    Note:Substitute the correct username, password, port, and dbname.

4. Stop MySQL server:
    $FOGLIGHT_HOME\bin\shutdownDb.bat

5. Remove all the existing tablespace files:
    cd $FOGLIGHT_HOME\mysql\data
    del ib_log*
    del ibdata*

6. Restart MySQL server:
    $FOGLIGHT_HOME\bin\runDb.bat

7. Import the dump files:
    cd $FOGLIGHT_HOME\mysql\bin
    mysql -P [dbport] -u [dbuser] -p [dbpwd]   <  mysqldata.sql  

NOTE: If the FMS is UNIX, add the --socket argument. Reference SOL52918.

8. Stop MySQL server.
    $FOGLIGHT_HOME\bin\shutdownDb.bat

9. Restart Foglight Management Server.
    $FOGLIGHT_HOME\bin\fms

Note: For Oracle and Microsoft SQL Server, please ask a DBA for help.