Details
-
Bug
-
Resolution: Fixed
-
Low
-
None
-
None
-
None
-
Severity 3 - Minor
-
Description
To reproduce
- Add 15,000 projects (the number comes from here)
- Enable "Public Access" for one project only
- Navigate to http://localhost:7990/bitbucket/projects
On my test instance it takes about 12 seconds for this page to load.
Also, ignoring public access, if an unauthenticated user navigated to the same URL it will take a long time before they are redirected to the login url in the case there are no public projects.
Environment
- Ubuntu 15.04 (probably not important)
- Postgres 9.4.5
Root cause
The cause of this is that ProjectAdminController.getProjects() will attempt to build a page of 100 projects the user has access to. It may have to check a large number, or all, projects to build a page.
The queries being executed look like this:
2016-07-08 17:29:26 AEST [2146-157] bbuser@bitbucket LOG: duration: 148.669 ms execute S_8: select this_.project_id as id1_31_0_, this_1_.description as descript2_31_0_, this_1_.project_key as project_3_31_0_, this_1_.name as name4_31_0_, this_1_.namespace as namespac5_31_0_, this_1_.project_type as project_6_31_0_, this_.is_public as is_publi1_45_0_ from sta_normal_project this_ inner join project this_1_ on this_.project_id=this_1_.id order by lower(this_1_.name) asc limit $1 offset $2 2016-07-08 17:29:26 AEST [2146-158] bbuser@bitbucket DETAIL: parameters: $1 = '201', $2 = '11600'
For all 15000 projects this requires 75 queries, each taking on the order of 100ms.
There is a chance that this query is actually not efficient due to the combination of "order by" and "lower".