Very high response time for setup licence key api with existing data

[edubacco]

trafficstars

🐛 Bug Report

Hi folks, I'm encountering response times of around 1 minute when calling the API to set up the license key in my self-hosted SuperTokens instance. This delay coincides with a noticeable increase in CPU usage by the database, the query consuming many resources seems to be

SELECT count(?) as c FROM ( SELECT count(user_id) as num_login_methods, app_id, primary_or_recipe_user_id FROM app_id_to_user_id WHERE primary_or_recipe_user_id IN ( SELECT user_id FROM user_last_active WHERE app_id = $1 AND last_active_time >= $2 ) GROUP BY app_id, primary_or_recipe_user_id) uc WHERE num_login_methods > ?

Useful informations

I'm using this docker image: registry.supertokens.io/supertokens/supertokens-postgresql:9.0

I run the apps inside kubernetes, and AWS RDS aurora postgres database. I have a paid licence, I use two different tenants connected to two different database instance. I activated roles and account linking. Every tenant has some thousand users.

My setup consists of three scaled instances of SuperTokens core, and three backend instances communicating with the core. I use different databases per tenant, with one main tenant in one database instance and another tenant in a separate database instance. Following guide, I configure SuperTokens core via API calls made by the backend application: at every backend startup, I do api calls to the core to inject configs (licence key, tenant configs, and so on). The set licence call lasts minutes, while other setup api calls answer fast.

It seems a ST issue, can you have a look?

Thanks 🙏

link to discord chat we had: https://discord.com/channels/603466164219281420/1239606145195184178 cc @rishabhpoddar, @sattvikc

[sattvikc]

@edubacco Thank you for pointing out this issue. The fix is a bit complex and it might take a while to implement. Meanwhile, would request you to not have add license key call in the infra code for now. Once the fix is in, you would be notified from this issue and then you can re-add.

[edubacco]

Thanks for the update @sattvikc, I've already deployed that workaround, I wait for the fix.

[kaustubhbabar5]

we're facing the same issue on our self-hosted SuperTokens instance but we're not using any license. would Telemetry also cause this ?

[sattvikc]

@kaustubhbabar5 Yes, the telemetry also runs the same query, but that shouldn't affect any API response as such. What issue are you facing exactly?

[kaustubhbabar5]

Supertokens frequently runs following query on the postgres database

 SELECT COUNT(*) AS total FROM user_last_active WHERE app_id = $1 AND last_active_time >= $2 

• this query performs an sequential scan on user_last_active table
• sometimes the the block read count/s and rows returned for this query spikes up which causes a spike in CPU utilization as well
• we've seen high request duration for signup API around the same time

[sattvikc]

This is now fixed in Core version v9.2.2