Open Closed

Bad performance due to AbpUserTokens table #10764


0
ricavir created
  • Abp 6.6.1
  • Angular
  • .NET 5

Hi support team,

I'm facing performance issues on Azure. While investigating requests performances, I noticed that a lot of requests where slow due to token validation. The AbpUserTokens table has arround 50k records which is a lot and very bad for performance. All records are still valid : so that means that the job in charge of deleting expired tokens is working.

I had a look to similar issues in the past : https://support.aspnetzero.com/QA/Questions/9009/Bad-performance-on-Azure-App-Service https://support.aspnetzero.com/QA/Questions/10545/Token-Authenticate-time-out-after-long-time-usage-with-same-user https://github.com/aspnetzero/aspnet-zero-core/issues/2925 https://github.com/aspnetzero/aspnet-zero-core/issues/2926

For you information, my refreshtoken expriration is fixed to one year... the angular web app AND the mobile app are not using it (these apps have been updated from an old Zero version that was not using refresh tokens at this time). Therefore, only few API clients are using it.

Right now, I plan to delete the whole table and get some performance back. What do you think ? Is it bad to do it ?

I plan also to change refresh tokens expiration to one month.

Even so, if I let it like that, I will have the same issue after some time... what is your advice to avoid this issue ? I think that a lot of Abp apps are facing the same.


6 Answer(s)
  • 0
    ismcagdas created
    Support Team

    Hi @ricavir,

    Normally 50K is not much to query. Could you run the query on https://blog.sqlauthority.com/2018/10/14/how-to-find-queries-using-an-index-from-sql-server-plan-cache-interview-question-of-the-week-195/ and see if this table has some missing indexes ?

  • 0
    ricavir created

    Hi @ismcagdas,

    Since my first message I decided to delete all records and the app performance is better. Azure was suggesting to create an index for AbpUserTokens table. I will try to add it and see differences.

    But the main question is : don't you think that this table could be really big with time ? For example, if you have 500 users that connects every day, this means arround 200k records in that table at the end of one year... something is wrong here.

  • 0
    ismcagdas created
    Support Team

    Hi,

    As far as I remember, this table should be cleared in time. I will check it again and inform you about this.

  • 0
    ricavir created

    Yes, this table is cleared every day by a background job. If this is what you want to check I confirm that it is working fine on my side. My concern is more about the design, taking into account that a refreshoken as an expiration time of 1 year, it means that this table can grow in a exponential way

  • 0
    ismcagdas created
    Support Team

    @ricavir

    Thanks, that was what I was going to check. So, as you suggested, decreasing the lifetime of tokens might help in your case. You can also consider checking indexes on this table.

  • 0
    ricavir created

    Tks @ismcagdas, I will do it