Bitbucket Data Center deleting bulk entries in sta_remember_me_token table leading to excessive load in database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary:
Every 5 hours, the Bitbucket application deletes bulk records in the sta_remember_me_token table, which results in excessive CPU utilization on the database node.
Environment:
- Bitbucket Data Center or Server 7.21.8.
- MiniOrange Single Sign-On - 2.0.13
Diagnosis:
On enabling the Database debug log, the atlassian-bitbucket.log will have the following events. In this particular example, the deletion of 1621595 records created a high load in the database node.
2023-11-04 11:40:19,053 DEBUG [Caesium-1-1] org.hibernate.SQL delete from sta_remember_me_token where expiry_timestamp<?
2023-11-04 11:40:19,053 TRACE [Caesium-1-1] o.h.type.descriptor.sql.BasicBinder binding parameter [1] as [TIMESTAMP] - [Wed Nov 04 06:40:19 UTC 2023]
2023-11-04 11:56:32,537 DEBUG [Caesium-1-1] c.a.s.i.a.DefaultRememberMeService Cleaned up 1621595 expired remember-me tokensRefer to the Enable SQL Query Logging KB article to know more details on enabling and disabling the database debug logs.
Cause:
- The plugin "MiniOrange Single Sign-On" has a setting "Set Remember Me-Cookie" and if enabled it creates a remember_me token when the users log in from the GUI but also creates for GIT operations as well as HTTP API execution.
- In an environment where setting "Set Remember Me-Cookie" is enabled and API or GIT calls are made often to the Bitbucket server, this triggers token creation (against each API or GIT call), which leads to frequent insert query execution in the sta_remember_me_token table and may affect database performance if there are already large records in the table.
Ideally, remember-me tokens should not be created for the GIT and HTTP API calls because these are not generally utilized in further sessions, and since these tokens are created with an expiry of 1 month(default expiry time) so once created they remain in the sta_remember_me_token table if not explicitly deleted by Bitbucket/plugin. - Bitbucket has a CleanupExpiredRememberMeTokensJob job that by default executes every five hours and removes all tokens whose expiration timestamp is smaller than the previous execution time of the CleanupExpiredRememberMeTokensJob job. So, if there was bulk insertion a month ago, there would be bulk deletion, which would add to the database load and affect the performance.
Solution:
- Disable the "Set Remember Me-Cookie" setting in the "MiniOrange Single Sign-On" plugin.
- Seek help from the plugin vendor to find a way to invalidate and delete the tokens that are already generated by the plugin.
- Alternatively, Bitbucket's CleanupExpiredRememberMeTokensJob job will keep deleting the expired tokens from the table and no manual intervention is required in this process. Since the tokens are generated with an expiry of 1 month so starting from the day the "Remember Me-Cookie" setting is disabled in the GUI, the job will keep deleting the expired tokens(in bulk) for the next one month and the load on the DB would decrease slowly.