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..!

--

--

--

APIs enable connecting today’s businesses to consumers. Read the articles in this publication to understand how APIs can facilitate your business for digital transformation.

Recommended from Medium

Microservices design patterns: API Gateway, Backend for Frontend (BFF)

Test Automation

Getters and setters are not encapsulation

Slicing data the smarter way in Python

Linux Remove Files [Unlink Command in Linux]

Params Wrapper in Ruby on Rails Explained

Literally, stuck in the past

Remote Development using PyCharm

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Tharika Madurapperuma

Tharika Madurapperuma

Associate Technical Lead @ WSO2 | Toastmaster

More from Medium

Redis Transactions and LUA script

Secure connections using Shared Signals and Events (SSE) Framework

How to Handle s3:TestEvent with Lambda + SQS or SNS

Kata Containers — Northflank Case Study