Have you ever wondered why there are so many queries executed by the WSO2 API gateway each second? Let us explain to you what happens.
Situation
If you install for instance ‘sysdig’ on your local WSO2 API gateway a large number of SQL queries will be fired to the underlying database each second. Usually each 60 seconds, then hold for a while, and then it starts all over again repeatedly.
First let’s understand the WSO2 API gateway. This gateway is part of the WSO2 API manager solution, just like the other components like the API Store, API Key Manager and API Publisher. The API Gateway itself is resonsible for securing, protecting, managing, and scaling each API call. It is a simple API proxy that intercepts API requests and applies policies such as throttling and security checks. It is also instrumental in gathering API usage statistics.
Back to this query which fires each time. The query which the WSO2 API gateway fires each time looks like:
select
reg_path, reg_user_id, reg_logged_time, reg_action, reg_action_data
from
reg_log
where
reg_logged_time>’some-timestamp’
and reg_logged_time<‘ some-timestamp ‘
and reg_tenant_id= some-number
But the funny think is: actually there is no load on the API gateway at all. So what is this?
Explanation
This particular query is the result of the registry indexing task that runs in the background of the WSO2 API gateway. The REG_LOG table is being queried periodically to retrieve the latest registry actions. The indexing task cannot be stopped.
However, one can configure the frequency of the indexing task through the following parameter that is in the registry.xml.
<indexingFrequencyInSeconds>60</indexingFrequencyInSeconds>
If this table is filled up, one can clean the data using a simple SQL query. However, when deleting the records, one must be careful not to delete all the data. The latest records of each resource path should be left in the REG_LOG table since reindexing of data requires at least one reference of each resource path. So we do recommend not to delete the records! Need a script to clean it up for you? Just contact the Yenlo WSO2 Guru team, more then happy to help you.
