Would it be possible to provide a description of some of the longer queries? (Temporarily setting $g_show_queries = ON) will gather this information.

Sure thing. Here's part of a note one of my co-workers sent to Ken Ito some time ago. This probably isn't a problem for many other users because they haven't set things up as we have. Perhaps they don't have as many users either. We now have 800 users.

Background:
The performance of Mantis was getting really bad (> 1 minute to display the view_all_bugs page when you selected ALL PROJECTS).
The way we use Mantis is slightly different. Basically we have 83 projects and 580+ users. ALL our projects are private but we have individually added every user to every project. In essense we have made them all public projects without actually making them public.

Status:
In order to identify exactly where the database problems were I have updated the debugging features of Mantis. I have added a timer_start and timer_end function around the db_query function in Mantis. This allows me to record exactly how long each and every sql query takes. I have added this time information into the $g_queries_array so that each query string and the time to execute is displayed at the bottom of the page when debugging is switched on.
After running this I found that out of the 173 queries that are executed when you select ALL PROJECTS the bulk of the time is spent running 2 particular SQL queries

29.553508043289----SELECT DISTINCT u.id, u.username FROM mantis_user_table u, mantis_project_user_list_table l, mantis_project_table p WHERE ((p.view_state='10' AND u.access_level>='25') OR (l.access_level>='25' AND l.user_id=u.id) OR u.access_level>='90') AND p.id=l.project_id ORDER BY u.username

27.947575092316----SELECT DISTINCT u.id, u.username FROM mantis_user_table u, mantis_project_user_list_table l, mantis_project_table p WHERE ((p.view_state='10' AND u.access_level>='55') OR (l.access_level>='55' AND l.user_id=u.id) OR u.access_level>='90') AND p.id = l.project_id ORDER BY u.username

basically running these 2 queries takes approx 29.5 + 27.9 = 57.4 seconds!!

And what do these 2 queries represent? They are

print_reporter_option_list (print_api.php)
print_assign_to_rel_option_list (print_api.php)

ie the functions that create the 2 drop downs at the top of the page listing all the reporters and all the assignees. Querying the database for the actual list of bugs, bugnotes, etc was taking less than 1 second. But querying the database for a unique list of reporters and assignees was taking 57.4 seconds.

The reason for this is the slightly nasty sql shown above - and also the way that Personal Markets uses private projects and then assigns EVERYONE to EVERY private project. Specifically assigning 580+ users to 50+ projects means that the mantis_project_user_list table has over 29,000 records in it. Now this isnt large for a table, but if you look at the SQL above - because it is linking 3 tables together and then using a series or OR and AND statements - this kills us.

We changed these queries to IN statements in the v19.

Here are a couple of specific changes we made.

filter_api.php - line 82

for ( $i=0 ; $i < count( $t_projects ) ; $i++) {
array_push( $t_clauses, "$t_projects[$i]" );
}
array_push( $t_where_clauses, "$t_bug_table.project_id IN(" . implode( ',', $t_clauses ) .")" );

user_api.php - line 597

Modified to remove Distinct and to filter for User ID on the Outer Join

if ( user_is_administrator( $p_user_id ) ) {
$query = "SELECT id, name
FROM $t_project_table
WHERE enabled=1
ORDER BY name";
} else {
$query = "SELECT p.id, p.name
FROM $t_project_table p
LEFT OUTER JOIN $t_project_user_list_table u
ON p.id=u.project_id
AND u.user_id='$c_user_id'
WHERE ( p.enabled = 1 ) AND
( p.view_state='$t_public' OR (p.view_state='$t_private'
AND u.user_id='$c_user_id' ))
ORDER BY p.name";
}

These don't take care of the number of queries written per page but it did speed things up slightly. I think the number of queries run per page is what's killing us. The database is handling ~1000 queries per second when load testing. mySQL was able to keep up but as mentioned before, the web server was getting crushed.

Here's another query that was causing us trouble from print_api

function print_reporter_option_list

if ( ALL_PROJECTS == $p_project_id ) {

#$query = "SELECT DISTINCT u.id, u.username, u.realname

FROM $t_user_table u,

$t_project_user_list_table l,

$t_project_table p

WHERE ((p.view_state='$t_pub'

AND u.access_level>='$t_rep') OR

(l.access_level>='$t_rep' AND

l.user_id=u.id) OR

u.access_level>='$t_adm') AND

p.id=l.project_id

ORDER BY u.realname, u.username";

---------------------------------------------------------------

As you can see, our solution wasn't all that elegant. We just grabbed everybody when filtering on ALL_PROJECTS

$query = "SELECT DISTINCT u.id, u.username, u.realname
FROM $t_user_table u
ORDER BY u.realname, u.username";

By way of an update, several of these changes have been made in 1.0.0a1.

First, the filter page allows DHTML based filter selections. That way the lists of items are not fetched until you click the title of a filter item. The filters themselves have also been rewritten to improve performance.

I've added the user/project filtering in joins that you have suggested.

Unfortunately, I haven't changed the query that determines the user list yet.

You might look at a CVS version, or 1.0.0a1 should be out in the next few weeks.

We are resolving this issue as "no change required", because it was reported against an old version of MantisBT which is no longer supported.

We recommend that you upgrade to the latest stable version [1]; if after doing so the problem still exists, do not hesitate to reopen the issue.

[1] https://sourceforge.net/projects/mantisbt/files/mantis-stable/