View Issue Details

IDProjectCategoryView StatusLast Update
0009876mantisbtperformancepublic2014-02-07 18:24
Reportertmatijas Assigned Todregad  
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
PlatformIBMOSWindowsOS VersionVista
Product Version1.1.4 
Target Version1.2.16Fixed in Version1.2.16 
Summary0009876: Performance problem with a lot of projects
Description

GOOD NEWS FIRST:

Amazeing.. I am running a test which has about 2000 projects and organized into 8 SUBFOLDERS.. Performance time dropped from about 2 seconds load time to 30 seconds when I put all the projects into subfolders.. I actually expected an improvment and NOT a loss. After that, nothing in this forum helped improve. However, get this. I changed the mantis_project_hierarchy_table type from myISAM to innoDB in phpmyadmin just for shits and gigles after optimize did nothing.. Performance is down to about 3 seconds now. Bet I can make better if I optimize my.ini for innoDB. Warning. No idea what effect this will have in database. Use with causion.

BAD NEWS AND NEED OF MAJOR CHANGE IN MANTIS

Mantis is not very scalable to lots of projects.. needs major improvement.

  1. Needs to break down projects alphabetically like user managemnt does. This is obvious. However, read on.
  2. The logic that creats subprojects needs to be seriously reviewed and modified. Take a look:

If I drop supprojects and empty project hierarchy table all togther on my 2000 project build, page load time goes down to about 1.20 seconds from about 2.5 to 3 with innoDB enabled in mantis_project_hierarchy_table (NOT myISAM) .. However, with only 200 projects, I was getting .2 seconds which is MUCH FASTER using the stock myISAM setting... So ON MY SETUP - 6% longer load time for every 200 projects if you DONT use subprojects. If you do use subprojects. 12% longer for every 200 projects IF AND ONLY IF you convert hierarchy table to innoDB from myISAM... IF YOU DO NOT CONVERT TO INNODB, forget it. GOes up to like 90% longer for ever 200 projects and TAKES 30 SECONDS!! to load news page and a whopping 180 seconds to load project manager.

Is this becuase the subprojects draw array is somehow different than just projects? I dont understand. I was thinking subprojects would actually reduce load time.

Steps To Reproduce

Add 2000 projects then messure performance. Then break 2000 projects down into 8 subprojects.. This can be done with mysql insert files made from like excell and a text editor. You will see the dramtic drop in performance.. Then change mantis_project_hierarchy_table to innoDB.. You will see insane improvment.

TagsNo tags attached.

Relationships

related to 0016187 closeddregad Application error on fresh install 
related to 0015796 closeddregad very slow response times 

Activities

JanHegewald

JanHegewald

2012-03-22 06:01

reporter   ~0031507

Has this been tested by the developers and are there any considerations concerning this point?

dregad

dregad

2013-05-01 08:38

developer   ~0036758

I did a quick test on my dev system, with a fresh install of Mantis 1.2.15.

Time to load main_page.php (average on 5 loads, in seconds)

  • with 100 projects: 0.35
  • with 500 projects: 0.67
  • with 1000 projects: 1.33
  • with 2000 projects: 4.18
  • with 5000 projects: 24.5 (!)

That's without any project dependencies (i.e. no records in project_hierarchy table).

Test records generated with <pre>
seq -w 1000 |xargs -n 1 -I num echo "insert into mantis_project_table set name='_test_num';" |mysql buggracker
</pre>

It appears that the problem is not caused by the DB back-end ($g_show_queries_count = ON; $g_show_queries_list = ON; for 2000 projects)

0.017 SQL Queries Total Time
4.127 Page Request Total Time

Code profiling shows that the time is spent in 4014 array_merge() calls from user_get_all_accessible_projects().

dregad

dregad

2013-05-01 11:00

developer   ~0036759

Replaced the array_merge() call in user_get_all_accessible_projects() by a foreach loop working on an associative array.

The main_page.php now loads under 1 second for 5'000 projects (without subprojects).

Note: changed summary, original was:
MAJOR PERFORMANCE PROBLEM using subprojects with a lot of projects BUT found a mysql tweak that makes HUGE difference

Related Changesets

MantisBT: master-1.2.x cc7703ac

2013-05-01 06:49

dregad


Details Diff
Optimize user_get_all_accessible_projects()

On instances having a large number of projects, this function would
consume significant resources while processing all the subprojects to
determine if one is accessible to the user (about 25 seconds to load
main_page.php for 5'000 projects, without subprojects).

The performance bottleneck was the array_merge() call in the loop. This
has been replaced by a foreach working on an associative array. The same
page now loads under 1 second.

Fixes 0009876
Affected Issues
0009876
mod - core/user_api.php Diff File

MantisBT: master 3b392f71

2013-05-01 06:49

dregad


Details Diff
Optimize user_get_all_accessible_projects()

On instances having a large number of projects, this function would
consume significant resources while processing all the subprojects to
determine if one is accessible to the user (about 25 seconds to load
main_page.php for 5'000 projects, without subprojects).

The performance bottleneck was the array_merge() call in the loop. This
has been replaced by a foreach working on an associative array. The same
page now loads under 1 second.

Fixes 0009876
Affected Issues
0009876
mod - core/user_api.php Diff File

MantisBT: master-2.0.x 6ec8cc63

2013-05-01 06:49

dregadmin


Details Diff
Optimize user_get_all_accessible_projects()

On instances having a large number of projects, this function would
consume significant resources while processing all the subprojects to
determine if one is accessible to the user (about 25 seconds to load
main_page.php for 5'000 projects, without subprojects).

The performance bottleneck was the array_merge() call in the loop. This
has been replaced by a foreach working on an associative array. The same
page now loads under 1 second.

Fixes 0009876
Affected Issues
0009876
mod - core/user_api.php Diff File