Optimize MMC cleanup performance

Last update: 4 August 2017

Summary

In large environments, the process of cleaning up MMC Service areas may take very long. This article explains the creation of additional database indices to reduce the cycle time of the cleanup process.

Note: (Because of the different database model this article is not valid for Fabasoft Version 6 and below.)

Solution

Indices for tables fsclogcontentmappinglist and fscvershashes

The following indices can boost cleanup performance up to 10 times depending on the database system.

Note: This improvement is only valid for MMC service areas using Content Addressed Storage (CAS).

Create the following database index for fsclogcontentmappinglist (please use the syntax according to your database system):

• Table fsclogcontentmappinglist
• Columns: objlogmaphash, objid (the ordering of the columns is mandatory)

Create the following database index for fscvershashes (please use the syntax according to your database system):

• Table fscvershashes
• Columns: vershash, objid (the ordering of the columns is mandatory)

Special operator class for this index with PostgreSQL

With PostgreSQL - instead of the above indexes - use the following indices with the operator class varchar_pattern_ops , otherwise indices are not used.

       CREATE INDEX CONCURRENTLY ind_fsclogcontentmappinglist_objlogmaphash

ON fsclogcontentmappinglist (objlogmaphash varchar_pattern_ops);

CREATE INDEX CONCURRENTLY ind_fscvershashes_vershash

ON fscvershashes (vershash varchar_pattern_ops);

Index for table cooobject (Oracle only)

Oracle database uses Full-Table scans in case of IS NULL clauses, because Oracle does not save NULL values in the index. The Fabasoft cleanup process uses IS NULL clauses to identify archived objects.

Note: This improvement is independent of the MMC service area storage type.

Please refer to the following article that explains this Oracle issue.

The following index workarounds this problem by indexing an additional (in fact needless) column to get NULL values to the index:

• Table cooobject
• Columns: objid, objarchstoreid

This index can speed up the cleanup process for additional 5-15%.

Applies to

• Fabasoft Folio (all versions from Folio 2007)
• Fabasoft eGov-Suite (all versions from eGov-Suite 2007)