Issue Summary

When a Confluence space is favourited by a large number of users, this causes severe latency issues when loading any Confluence page within that space and likely other pages related to that space.

The exact number of favouritings required to trigger a noticeable increase in latency is not reliably known. It is likely environment and instance activity dependent. It also likely requires the surpassing of the Hibernate L2 cache limits. One large customer observed that a space with roughly 4000 user favourites was seeing Confluence page load times upwards of 10 seconds.

The root cause for this issue is the lack of usage of specialised label SQL queries. Whenever a label related operation is performed, all labellings for that entity are retrieved, including all personal labels for all users on that instance. This issue is not limited to just space entities, but this is where it may be more apparent due to the increased likelihood of a large number of user favouritings on a popular space. The current query approach is not scalable with instance user quantity.

The retrieval of all space labels on the view of any Confluence page in that space is currently triggered by the code com.atlassian.confluence.userstatus.DefaultFavouriteManager#isUserFavourite(com.atlassian.user.User, com.atlassian.confluence.spaces.Space). Removing or optimising this specific call should provide immediate relief to affected instances.

To rectify and prevent potential related issues, and ensure Confluence remains scalable into the future, the com.atlassian.confluence.core.AbstractLabelableEntityObject should be reworked so that personal labels are not fetched from the database by default.

Steps to Reproduce

To more easily reproduce this without needing to surpass caching limits, you may disable Hibernate L2 caching by making the following edits in databaseSubsystemContext.xml (Confluence <8.6) or productionDatabaseContext.xml otherwise.

<prop key="hibernate.cache.use_query_cache">false</prop>
<prop key="hibernate.cache.use_second_level_cache">false</prop> 

1. Create a Space and a page within that Space
2. Have multiple users log in and favourite that Space
3. Log in as an administrator
4. Enable SQL logging at /admin/viewlog4j.action
5. Browse to the page within that Space
6. Observe the SQL log files

Expected Results

Personal labels for users other than the authenticated user are not retrieved from the database.

Actual Results

Excessive number of label SQL queries, such as the following.

2024-09-04 13:02:14,222 DEBUG [org.hibernate.SQL] logStatement select labelling0_.ID as id1_19_0_, labelling0_.LABELID as labelid2_19_0_, labelling0_.CONTENTID as contenti3_19_0_, labelling0_.PAGETEMPLATEID as pagetemp4_19_0_, labelling0_.OWNER as owner5_19_0_, labelling0_.CREATIONDATE as creation6_19_0_, labelling0_.LASTMODDATE as lastmodd7_19_0_, labelling0_.LABELABLEID as labelabl8_19_0_, labelling0_.LABELABLETYPE as labelabl9_19_0_ from CONTENT_LABEL labelling0_ where labelling0_.ID=?
2024-09-04 13:02:14,223 TRACE [type.descriptor.sql.BasicBinder] bind binding parameter [1] as [BIGINT] - [23]
2024-09-04 13:02:14,223 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([labelid2_19_0_] : [BIGINT]) - [6]
2024-09-04 13:02:14,224 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([contenti3_19_0_] : [BIGINT]) - [5]
2024-09-04 13:02:14,224 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([pagetemp4_19_0_] : [BIGINT]) - [null]
2024-09-04 13:02:14,224 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([creation6_19_0_] : [TIMESTAMP]) - [2006-04-24 09:12:49.0]
2024-09-04 13:02:14,224 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([lastmodd7_19_0_] : [TIMESTAMP]) - [2006-04-24 09:12:49.0]
2024-09-04 13:02:14,224 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([labelabl8_19_0_] : [BIGINT]) - [5]
2024-09-04 13:02:14,224 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([labelabl9_19_0_] : [VARCHAR]) - [null]
2024-09-04 13:02:14,224 DEBUG [org.hibernate.SQL] logStatement select label0_.LABELID as labelid1_58_0_, label0_.NAME as name2_58_0_, label0_.OWNER as owner3_58_0_, label0_.NAMESPACE as namespac4_58_0_, label0_.CREATIONDATE as creation5_58_0_, label0_.LASTMODDATE as lastmodd6_58_0_ from LABEL label0_ where label0_.LABELID=?
2024-09-04 13:02:14,224 TRACE [type.descriptor.sql.BasicBinder] bind binding parameter [1] as [BIGINT] - [6]
2024-09-04 13:02:14,225 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([name2_58_0_] : [VARCHAR]) - [favourite]
2024-09-04 13:02:14,225 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([creation5_58_0_] : [TIMESTAMP]) - [2006-04-24 09:12:49.0]
2024-09-04 13:02:14,225 TRACE [type.descriptor.sql.BasicExtractor] extract extracted value ([lastmodd6_58_0_] : [TIMESTAMP]) - [2010-01-13 05:51:41.0]

Workaround

First disable/remove any automations for favouriting spaces.

Delete user favouritings of the affected space.

User may choose to bookmark their favourite spaces instead until a time this issue is fixed.