Normal user experience slow page load because of many find-by-id issue queries (OD-3021)

Normal user experience slow page load because of many find-by-id issue queries #3021

jbauer opened 5 days ago

For quite some time now normal users have a 2-3 second delay before the page is loaded. Query statistics in pgAdmin4 show that a single click of a normal user causes roughly 500 transactions per seconds which in turn causes the 2-3 second delay.

After enabling pg_stat_statements I found that the query below will be executed a lot while navigating OneDev, even if the pages do not show any issues (e.g. switching between SSH / GPG keys in user settings).

select
    issue0_.o_id as o_id1_34_0_,
    issue0_.o_boardPosition as o_boardp2_34_0_,
    issue0_.o_commentCount as o_commen3_34_0_,
    issue0_.o_confidential as o_confid4_34_0_,
    issue0_.o_confusedCount as o_confus5_34_0_,
    issue0_.o_description as o_descri6_34_0_,
    issue0_.o_descriptionRevisionCount as o_descri7_34_0_,
    issue0_.o_externalParticipants as o_extern8_34_0_,
    issue0_.o_eyesCount as o_eyesco9_34_0_,
    issue0_.o_heartCount as o_heart10_34_0_,
    issue0_.o_LAST_ACTIVITY_DATE as o_last_11_34_0_,
    issue0_.o_LAST_ACTIVITY_DESCRIPTION as o_last_12_34_0_,
    issue0_.o_LAST_ACTIVITY_USER as o_last_33_34_0_,
    issue0_.o_messageId as o_messa13_34_0_,
    issue0_.o_movedTo_id as o_moved34_34_0_,
    issue0_.o_number as o_numbe14_34_0_,
    issue0_.o_numberScope_id as o_numbe35_34_0_,
    issue0_.o_onBehalfOf as o_onbeh15_34_0_,
    issue0_.o_ownEstimatedTime as o_ownes16_34_0_,
    issue0_.o_ownSpentTime as o_ownsp17_34_0_,
    issue0_.o_pinDate as o_pinda18_34_0_,
    issue0_.o_progress as o_progr19_34_0_,
    issue0_.o_project_id as o_proje36_34_0_,
    issue0_.o_smileCount as o_smile20_34_0_,
    issue0_.o_state as o_state21_34_0_,
    issue0_.o_stateOrdinal as o_state22_34_0_,
    issue0_.o_submitDate as o_submi23_34_0_,
    issue0_.o_submitter_id as o_submi37_34_0_,
    issue0_.o_tadaCount as o_tadac24_34_0_,
    issue0_.o_thumbsDownCount as o_thumb25_34_0_,
    issue0_.o_thumbsUpCount as o_thumb26_34_0_,
    issue0_.o_tickCount as o_tickc27_34_0_,
    issue0_.o_title as o_title28_34_0_,
    issue0_.o_totalEstimatedTime as o_total29_34_0_,
    issue0_.o_totalSpentTime as o_total30_34_0_,
    issue0_.o_uuid as o_uuid31_34_0_,
    issue0_.o_voteCount as o_votec32_34_0_
from
    o_Issue issue0_
where
    issue0_.o_id = $1

I suspect it is classic JPA N+1 query issue and further investigation showed:

Site-Admin does not cause these huge amount of queries
New user account does not cause these huge amount of queries (does not have any issues linked)
Existing user account with many linked issues (submitted, commented, mentioned, ...) causes these queries
Existing user account with many linked issues but marked as admin does not cause these huge amount of queries

Seems like the query is used for permission calculation (for side menu maybe?) as administrative users are not affected.

Please fix the N+1 queries issue.

Copy

OneDev commented 5 days ago

State changed as build OD-8183 is successful

Copy
OneDev changed state to 'Closed' 5 days ago
Previous Value Current Value
Open

Closed
jbauer commented 4 days ago

Thank you! Our OneDev is now so much faster 🥳 Even git fetch/pull/push is way faster now for the affected users.

Copy
Robin Shen commented 4 days ago

No problem. Thx for all the great feedbacks to help making OneDev better.

Copy
Robin Shen commented 4 days ago

@jbauer there is a bug introduced in 16.5.4 that normal user no longer able to access confidential issues even if authorized. This has been fixed in 16.5.5.

Copy
Login to comment

Previous Value	Current Value
Open	Closed

Type	Bug
Priority	Normal
Assignee	Robin Shen
Affected Versions	16.5.3 and earlier
Labels	No labels

Issue Votes (0)

Watchers (2)

Reference

OD-3021