Registry DB Cleanup in WSO2 API Manager

Photo by Steve Johnson on Unsplash

Different flows in WSO2 API Manager such as creating and updating APIs, adding tags and ratings cause different registry properties to be added to the Registry database. This is the expected behavior. But when “Registry Versioning” is enabled, older properties are persisted in the database. Over time, this could lead to unnecessary record growth in the Registry tables in the DB.

This can directly affect the performance of the product if the number of records exceed millions in number. Then there will be a time that we need to cleanup the fast growing database tables from the Registry.

Due to this limitation, registry versioning is disabled by default in WSO2 API Manager 3.0.0 onwards to prevent unnecessary DB growth.

In WSO2 API Manager versions before 3.0.0 (2.x.x and 1.x.x), Registry Versioning is enabled by default and hence we have to cleanup the Registry DB if there happens to be large tables with millions of records.

Out of all the tables available in the Registry DB, we can cleanup the following tables without affecting the functionality of the product.

1. REG_LOG
2. REG_PROPERTY
3. REG_RESOURCE_PROPERTY
4. REG_TAG
5. REG_RESOURCE_TAG

You can use the following DB Scripts to cleanup these tables.

Note:

1. You can always take a backup of the Registry database before the cleanup to face any issues that can occur during the cleanup process.

2. Remember to shut down the server and stop any DB transactions before the database cleanup. This is to prevent any inconsistencies that could happen with new data that gets added to the DB during the cleanup.

REG_LOG Table Cleanup

You can use the following script for all the database types, H2, DB2, Mysql, Mssql, Oracle and Postgresql.

REG_LOG Cleanup Script

REG_PROPERTY and REG_RESOURCE_PROPERTY Table Cleanup

Use the following script for H2, Mssql and Postgresql database types.

H2, Mssql and Postgresql REG_PROPERTY and REG_RESOURCE_PROPERTY Cleanup Script

Use the following script for Mysql database type.

Mysql REG_PROPERTY and REG_RESOURCE_PROPERTY Cleanup Script

Use the following script for DB2 and Oracle database types.

DB2 and Oracle REG_PROPERTY and REG_RESOURCE_PROPERTY Cleanup Script

REG_TAG and REG_RESOURCE_TAG Table Cleanup

Use the following script for H2, Mssql and Postgresql database types.

H2, Mssql and Postgresql REG_TAG and REG_RESOURCE_TAG Cleanup Script

Use the following script for Mysql database type.

Mysql REG_TAG and REG_RESOURCE_TAG Cleanup Script

Use the following script for DB2 and Oracle database types.

DB2 and Oracle REG_TAG and REG_RESOURCE_TAG Cleanup Script

Turnoff Registry Versioning

To prevent having to constantly run the cleanup scripts when the DB grows, you can turnoff registry versioning in previous WSO2 API Manager Versions.

You can follow the steps provided under the topic “Disabling versioning in the registry configuration” in WSO2 API Manager official documentation in order to do this.

Cheers..!