View Issue Details

IDProjectCategoryView StatusLast Update
0032131mantisbtperformancepublic2023-04-11 19:07
Reporterpolzin Assigned Todregad  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version2.25.6 
Target Version2.25.7Fixed in Version2.25.7 
Summary0032131: access_project_array_filter can lead to many SQL requests
Description

In access_project_array_filter (which can be used to determine the menu, so very early in the page execution) all projects may be are individually checked for access_has_project_level( $t_check_level, $t_project_id, $p_user_id ). This can lead to one SQL request per project.

In our setup it reduced the SQL requests per page from 300 to 50 when inserting:
project_cache_array_rows( $p_project_ids );
at before
https://github.com/mantisbt/mantisbt/blob/7cf0e76b5e214628140bf36fe32b34eb9e772bb2/core/access_api.php#L381

TagsNo tags attached.

Relationships

related to 0020054 closedcproensa Cant modify configuration for All projects if only one project exists 

Activities

dregad

dregad

2023-03-13 12:30

developer   ~0067476

Thanks for the report, it seems a good optimization and should not cause much side effects. How may projects/subprojects do you have in your system ?

Would you be able to submit a pull request with the proposed change ?

polzin

polzin

2023-03-13 14:03

reporter   ~0067481

@dregad. Not now. Could you do it for me? It´s just one line... :-)

dregad

dregad

2023-03-14 08:23

developer   ~0067483

Maybe I'm missing something here, but after a quick check I am not seeing any difference in the number of executed queries or page load time, with or without the project_cache_array_rows() call added in access_project_array_filter() as suggested.

Stack trace with unmodified 2.25.6 source -> 12 queries

project_api.php:138, project_cache_array_rows()
layout_api.php:593, layout_navbar_projects_list()
layout_api.php:535, layout_navbar_projects_menu()
layout_api.php:438, layout_navbar()
layout_api.php:153, layout_page_begin()
manage_overview_page.php:52, {main}()

Stack trace with the added project_cache_array_rows() in access_project_array_filter() -> 12 queries

project_api.php:138, project_cache_array_rows()
access_api.php:378, access_project_array_filter()
access_api.php:414, access_has_any_project_level()
layout_api.php:549, layout_navbar_button_bar()
layout_api.php:436, layout_navbar()
layout_api.php:153, layout_page_begin()
manage_overview_page.php:52, {main}()

Note that project_cache_array_rows() is already called for all user-accessible projects in layout_navbar_projects_list(), per the following stack trace (which actually occurs slightly later than with your proposed change.

project_api.php:138, project_cache_array_rows()
layout_api.php:593, layout_navbar_projects_list()
layout_api.php:535, layout_navbar_projects_menu()
layout_api.php:438, layout_navbar()
layout_api.php:153, layout_page_begin()
manage_overview_page.php:52, {main}()

My test DB has 103 projects. I tested from manage_overview_page.php, but results are the same from heavier pages as well.

Some more information about your setup (number of projects, visibility, subprojects...) would be useful to understand in what situation the performance improvement occurs.

polzin

polzin

2023-03-14 10:33

reporter   ~0067486

@dregad
Thanks for investigating.
I did some research, but could not find identify the reason. It happend only for some users. But for them, it could be reproduced that the change reduced the query count $g_show_queries_count = ON; from 300 to 50. For other users, the project_cache_array_rows was called before and the change had no impact.
I don´t know how I can analyse the cause with an reasonable effort. Do you have any suggestion?
It think, the change will cause no harm, and will improves the query count in some situations.

It happend on view.php pages. I have no reports for other pages. If it is really relevant for you, I delete the fix from our installations, and ask people to try other pages.

dregad

dregad

2023-03-14 11:45

developer   ~0067487

It happend only for some users.

Then maybe it's caused by a combination of limited access levels and/or visibility on private projects ?

how I can analyse the cause with an reasonable effort

As mentioned before,
Some more information about your setup (number of projects, visibility, subprojects...) would be useful to understand in what situation the performance improvement occurs.

Also check for these users, which access level they have, and which project(s) they are member of (how many of the total).

Maybe verify if you have any plugins that could affect this as well.

It think, the change will cause no harm, and will improves the query count in some situations.

Probably safe yes, but I like to understand the reason and consequence of changing something ;-)

If it is really relevant for you, I delete the fix from our installations, and ask people to try other pages.

I don't think that's needed. I can test with view.php too

polzin

polzin

2023-03-14 12:05

reporter   ~0067488

We have 322 projects in a project hierachy, all internal, so users get individual access rights to all enabled objects. I can´t count how many of them are enabled, but most are. The users had "developer" level (the affected users and the others, too).
Active Plugins:
Mantis Graphs
MantisBT Core
MantisBT Formatting
MantisBT Source Control Integration (+gitlab + subversion/ viewvcs + Subversion)

dregad

dregad

2023-03-14 12:33

developer   ~0067490

322 projects in a project hierachy, all internal

Are the projects/subprojects public or private?

The users had "developer" level

Is this their global access level ?
Do they also have specific and different access in the project(s) they are working on ?

polzin

polzin

2023-03-14 15:06

reporter   ~0067493

All projects/subproject are private.
For the users "developer" is their global access level.
They have project dependend access levels, but they are more or less the same (developer for all private projects), so I could not find the reason for the difference in queries here.

dregad

dregad

2023-03-15 07:50

developer   ~0067496

OK I got it :-)

It turns out the problem is not driven by the project hierarchy as I thought initially, but by the user's access level. I did not catch it in my initial repro tests (0032131:0067483) because I was logged in with an admin user.

The logic that determines whether the Project selector is shown depends on the user having access to more than one project, or having access to manage functions. This is handled by layout_navbar_can_show_projects_menu(), which calls access_has_any_project_level() to see if user has manage_project_threshold level.

So the access_has_any_project_level() call occurs earlier for non-admin users, as shown in this stack trace (with DEVELOPER user, master branch 7cf0e76b5):

access_api.php:380, access_project_array_filter()
access_api.php:415, access_has_any_project_level()
layout_api.php:1320, layout_manage_menu_link()
layout_api.php:1353, layout_navbar_can_show_projects_menu()
current_user_api.php:281, current_user_modify_single_project_default()
==> layout_api.php:155, layout_page_begin()
bug_view_inc.php:121, include()
view.php:36, {main}()

Compare with ADMIN stack trace:

access_api.php:380, access_project_array_filter()
access_api.php:415, access_has_any_project_level()
layout_api.php:555, layout_navbar_button_bar()
layout_api.php:442, layout_navbar()
==> layout_api.php:157, layout_page_begin()
bug_view_inc.php:121, include()
view.php:36, {main}()

Bottomline is, your fix is good... And now I understand why ;-)

polzin

polzin

2023-03-15 10:06

reporter   ~0067499

Last edited: 2023-03-15 10:06

I still don´t get, why for other non-admin users there was no problem. But I am fine with the fix. :-)

Thanks!!

dregad

dregad

2023-03-15 11:47

developer   ~0067503

why for other non-admin users there was no problem

Do these users have access to any of the manage pages (e.g. projects, users, custom fields - see layout_manage_menu_link() function) ?
If so they follow the same code path as admins, so projects were already cached by the time access_has_any_project_level() was called.

Related Changesets

MantisBT: master-2.25 8ccd29c5

2023-03-15 08:05

dregad


Details Diff
Cache Projects in access_project_array_filter()

This performance optimization avoids execution of one SQL query per
Project in the loop as we check the access level for each one.

Fixes 0032131
Affected Issues
0032131
mod - core/access_api.php Diff File