Checklist
I have read intro post: https://community.passbolt.com/t/about-the-installation-issues-category/12
I have read the tutorials, help and searched for similar issues
I provide relevant information about my server (component names and versions, etc.)
I provide a copy of my logs and healthcheck
I describe the steps I have taken to trouble shoot the problem
I describe the steps on how to reproduce the issue
I run a Passbolt CE instance on docker for about a dozen users, couple of hundred resources. Currently we’re on 5.4.1 (as of last week), but the instance started out in life as 4.something. Been running it for almost 2 years. Basic docker compose setup using MariaDB in a separate container.
For a while we’ve been having an issue with logins being horrendously slow. Both for browser logins (various browsers, chrome and firefox usually) and with API logins. Same problem for both GPG and JWT authentication. Boils down to the same issue: Actually performing the login (IE; posting to /auth/jwt/login.json or the phase2 request to /auth/login.json) takes 10-30 seconds to perform.
I’ve tried most common performance tweaks such as using skip-name-resolve and connecting to MariaDB using IP, changing body size limits and other nginx settings, resource limits for php and so on. Nothing made a noticeable dent. Neither did cleaning up the activity logs.
What did work, however, was cleaning up the authentication_tokens table in the database. I found that this table contained several millions of rows, going back when I started running the instance. I copied the whole instance to a test environment and did a simple delete of all rows in this table that had active=0. Logins requests immediately went from 10-30 seconds to completing successfully in about 70ms.
I’ll point out that this behaviour is exacerbated by our setup relying heavily on 2 API integrations in the form of a powershell and a python module that I’ve written. We’re constantly making large amounts of API calls through these and consequently the API user’s ids where about 99% of the total rows in authentication_tokens.
Is this a misconfiguration on my end causing this idefinite growth of the authentication_tokens table? Are there server side settings I should use that will keep them from being retained forever? Are there things I should be doing in my client side code to keep it from happening (I tried logging out, but that didn’t immediately seem to change anything)?
And how do I go about safely cleaning up this table going forward? Can I just delete whatever is inactive and looks old enough without breaking anything?