Repository navigation
authtoken table performance considerations #42574
Description
How to use GitHub
- Please use the 👍 reaction to show that you are interested into the same feature.
- Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
- Subscribe to receive notifications on status change and new comments.
Is your feature request related to a problem? Please describe
As the admin of a Nextcloud instance I notice lots of queries on the oc_authtoken table.
SELECT * FROM `oc_authtoken` WHERE (`token` = :dcValue1) AND (`version` = :dcValue2)UPDATE oc_authtoken SET last_activity=:dcValue1 WHERE id=:dcValue2 AND last_activity <= :dcValue3
26.0.9 oc_authtoken queries study
| Executions | Query |
|---|---|
| 625507 | SELECT * FROM `oc_authtoken` WHERE (`token` = :dcValue1) AND (`version` = :dcValue2) |
| 28115 | SELECT `name` FROM `oc_authtoken` WHERE (`uid` = :dcValue1) AND (`last_activity` >= :dcValue2) |
| 28002 | UPDATE `oc_authtoken` SET `last_activity` = :dcValue1 WHERE (`id` = :dcValue2) AND (`last_activity` < :dcValue3) |
| 15126 | UPDATE `oc_authtoken` SET `last_check` = :dcValue1 WHERE `id` = :dcValue2 |
| 4492 | SELECT * FROM `oc_authtoken` WHERE (`uid` = :dcValue1) AND (`version` = :dcValue2) LIMIT 1000 |
| 2020 | DELETE FROM `oc_authtoken` WHERE (`token` = :dcValue1) AND (`version` = :dcValue2) |
| 295 | SELECT * FROM `oc_authtoken` WHERE `uid` = :dcValue1 ORDER BY `id` ASC LIMIT 1 |
| 295 | INSERT INTO `oc_authtoken` (`uid`, `login_name`, `public_key`, `private_key`, `password`, `password_hash`, `name`, `token`, `type`, `remember`, `last_activity`, `last_check`, `version`) VALUES(:dcValue1, :dcValue2, :dcValue3, :dcValue4, :dcValue5, :dcValue6, :dcValue7, :dcValue8, :dcValue9, :dcValue10, :dcValue11, :dcValue12, :dcValue13) |
| 48 | DELETE FROM `oc_authtoken` WHERE (`last_activity` < :dcValue1) AND (`type` = :dcValue2) AND (`remember` = :dcValue3) AND (`version` = :dcValue4) |
| 33 | DELETE FROM `oc_authtoken` WHERE `id` = :dcValue1 |
| 9 | SELECT * FROM `oc_authtoken` WHERE (`id` = :dcValue1) AND (`version` = :dcValue2) |
| 6 | UPDATE `oc_authtoken` SET `password` = :dcValue1, `password_hash` = :dcValue2 WHERE `id` = :dcValue3 |
Describe the solution you'd like
Database improvements
- Change LONGTEXT columns name, scope, private_key and public_key to varchar. LONGTEXT is stored outside the table data, VARCHAR is stored inline. Tricky: find reasonable maximum length for VARCHAR to avoid truncation problems.
- New unique index for id+last_activity. The covering index allows the DB to run the last_activity condition without reading table data.
- Drop superfluous indices. Index updates cause database load. Unless an index gives a clear benefit, we can drop it to make INSERT, DELETE and some UPDATE queries cheaper.
- Make last_activity (and last_check) NOT NULL. It has a default value of 0 and we never actually make use of NULL. Yet NULL is theoretically possible and might influence the database's decision on whether to use an index of the nullable column.
- Update last_check and last_activity together
Application improvements
- Cache authtoken lookup by token #42823
- Cache informaton about Talk desktop usage notifications#1769
- [Bug]: Two useless authtoken database queries for every anonymous request #42589
- Set authtoken.private_key and authtoken.public_key NULL for auth.storeCryptedPassword=true #43260
- Do not UPDATE authtoken.last_check if no password check is performed fix(session): Do not update authtoken last_check for passwordless #44670
Describe alternatives you've considered
N/a
Additional context
N/a
Activity
ChristophWurst commentedon Jan 4, 2024
juliusknorr commentedon Jan 12, 2024
Another insight I had noted but not filed or investigated:
it seems that we may run two update queries in one request just after each other
ChristophWurst commentedon Apr 19, 2024
Unverified idea: we could combine private/public key into one database column, either as PEM or our own format. Then there would only be one external blob.
juliusknorr commentedon Apr 26, 2024
#45026 would address this