MantisBT - v1.1 Release (Closed)
View Issue Details
0000232v1.1 Release (Closed)[All Projects] Generalpublic2009-08-12 22:062009-09-09 22:44
ReporterLex 
Assigned Tocaseydk 
PrioritynormalSeveritymajorReproducibilityalways
StatusclosedResolutionfixed 
Platformx86 OSWindows XPOS VersionXP SP2
Product Version 
Target VersionFixed in Version1.1 
Summary0000232: w2p slow loading complex projects - or - slow database queries
DescriptionWhen you have complex projects with lots of tasks and multiple dependencies w2p becomes very slow loading those projects in projects view - tasks tab. The exact URL is:

http://XX.X.XX.XXX/web2project/index.php?m=projects&a=view&project_id=XX

This happens due to some slow database queries - the query which takes a lot of time is:

# Time: 090730 13:26:43
# User@Host: web2project[web2project] @ localhost [127.0.0.1]
# Query_time: 38.328861 Lock_time: 0.000000 Rows_sent: 64 Rows_examined: 54310022
use web2project_test;
SET timestamp=1248953203;
SELECT tasks.task_id, task_parent, task_name,task_start_date, task_end_date, task_dynamic,count(tasks.task_parent) as children,task_pinned, pin.user_id as pin_user,task_priority, task_percent_complete,task_duration, task_duration_type,task_project,task_description, task_owner, task_status,usernames.user_username, usernames.user_id,assignees.user_username as assignee_username,count(distinct assignees.user_id) as assignee_count,co.contact_first_name, co.contact_last_name,CONCAT(co.contact_first_name,' ', co.contact_last_name) AS owner,task_milestone,count(distinct f.file_task) as file_count,tlog.task_log_problem,(SELECT COUNT(task_id) FROM ((`tasks` AS stasks)) WHERE stasks.task_id <tasks> 0 LEFT JOIN `files` AS f ON tasks.task_id = f.file_task LEFT JOIN `project_departments` AS project_departments ON p.project_id = project_departments.project_id OR project_departments.project_id IS NULL LEFT JOIN `departments` AS departments ON departments.dept_id = project_departments.department_id OR dept_id IS NULL LEFT JOIN `user_task_pin` AS pin ON tasks.task_id = pin.task_id AND pin.user_id = 3 WHERE task_project = 95 AND task_project = 95 AND task_status = 0 GROUP BY tasks.task_id ORDER BY task_start_date;

DotProject seemed to solve the problem by doing some gacl fixing and adding some indexing to the database.

I tried both patches on my dP installation and 35 seconds of loading the page became 4 seconds of loading.
 
the phpgacl thing:
http://forums.dotproject.net/showpost.php?p=40656&postcount=44

the indexing thing:
http://forums.dotproject.net/showpost.php?p=41176&postcount=50

the phpgacl patch:
http://sourceforge.net/tracker/index.php?func=detail&aid=2376351&group_id=70930&atid=529515


Steps To Reproduce1) log in w2p
2) go to project view http://XX.X.XX.XXX/web2project/index.php?m=projects
3) look for a project with lots of tasks an complex dependencies
4) click on the project name
5) wait :o)
Additional InformationThe project also exists in dotProject and there is
TagsNo tags attached.
child of 0000142closed caseydk Pending Requests System Performance Improvements 
Attached Files

Notes
(0000386)
caseydk   
2009-08-13 12:21   
In reference to the database index post (2nd link), we've already made all of those changes *except* for the my.cnf change which is not a part of the web2project installation.

The phpgacl hack (1st link), I'm nervous about hacking phpgacl too much because then it's something else we have to maintain. Longer term, according to SourceForge - https://sourceforge.net/projects/phpgacl/ - their last commit was almost 3 years ago, so it may be time to look at alternatives.

I haven't investigated the 3rd link.
(0000387)
Lex   
2009-08-13 21:50   
As far as I can see only those two things together - the phpgacl patch and the database patch - are speeding up dotproject so much.

Adding only the phpgacl patch to my dP installation did not add that much speed. But adding also the database indexing patch after adding the phpgacl patch speeded it up a lot.
(0000405)
caseydk   
2009-08-30 21:04   
Lex, could you turn on debugging and copy/paste the results of that info?

Go into ./base.php and edit this line:

define('W2P_PERFORMANCE_DEBUG', false);

to read this:

define('W2P_PERFORMANCE_DEBUG', true);


And then go to that page and copy/paste the results. The information should look something like this:

web2Project 1.0.0
PHP version nr: 5.2.4-2ubuntu5.6
DB provider and version nr: mysql 5.0.51
DB Table Prefix: ""
PHP Max. Execution Time: 120 seconds
Memory Limit: 512 Mb
Memory Used: 7.73 Mb
Memory Unused: 7741 Kb
Memory Peak: 8945 Kb
Setup in 0.183 seconds
ACLs checked in 0.060 seconds
ACLs nr of checks: 97
w2P Data checked in 0.400 seconds
w2P DBQueries executed: 244 queries
w2P Old Queries executed: 0 queries
w2P Total Queries executed: 244 queries
Page generated in 1.228 seconds
(0000411)
Lex   
2009-09-01 23:31   
... already done this and posted the results in the forum - here is a new one.

It is web2project 1.0.0 - how ever - it says 0.9.9 here.

web2Project 0.9.9
PHP version nr: 5.2.9
DB provider and version nr: mysql 5.1.30
DB Table Prefix: ""
PHP Max. Execution Time: 120 seconds
Memory Limit: 32 Mb
Memory Used: 7.84 Mb
Memory Unused: 7826 Kb
Memory Peak: 8776 Kb
Setup in 0.237 seconds
ACLs checked in 0.107 seconds
ACLs nr of checks: 96
w2P Data checked in 40.850 seconds
w2P DBQueries executed: 219 queries
w2P Old Queries executed: 0 queries
w2P Total Queries executed: 219 queries
Page generated in 42.539 seconds
(0000412)
caseydk   
2009-09-02 06:23   
Very interesting...
* Mine is doing 244 queries and yours is doing 219, so that's about the same;
* Our peak memory is about the same - my 7.73 Mb vs your 7.84 Mb;
* Our ACL checks - my 0.060s vs your 0.107 - are different, so you may be onto part of it...

* But the big problem that I see is our query times. Mine is 0.400s while yours is 40.850s... or about 100x more and makes up 96% of your 42.539s loading time.

This makes me think there's a problem with your database indexes. Please check that these indexes exist:

On the tasks table: task_project, task_parent, task_owner
On the files table: file_task, file_project
On the project_departments table: project_id, department_id
On the users table: user_contact
On the user_task_pin table: task_id
On the user_tasks table: task_id
On the contacts table: contact_project, contact_department
On the task_logs table: task_log_task, task_log_creator

If you're using phpMyAdmin, you can click the lightning bolt icon to make them indexes. In the meantime, I'll look into why this might have happened... did you install fresh or convert from dotProject?
(0000417)
Lex   
2009-09-02 22:24   
task_project, task_parent, task_owner -> do exist
file_task, file_project -> do exist
project_id, department_id -> do exist
user_contact -> do exist
task_id -> does exists
task_id -> does exist
contact_project, contact_department -> do exist
task_log_task, task_log_creator -> do exist

So all of them are there. How ever - I clicked the lightning bolt icon for all of them and - you would not believe it - it got faster:

web2Project 0.9.9
PHP version nr: 5.2.9
DB provider and version nr: mysql 5.1.30
DB Table Prefix: ""
PHP Max. Execution Time: 120 seconds
Memory Limit: 32 Mb
Memory Used: 7.84 Mb
Memory Unused: 7826 Kb
Memory Peak: 8833 Kb
Setup in 0.256 seconds
ACLs checked in 0.066 seconds
ACLs nr of checks: 96
w2P Data checked in 0.080 seconds
w2P DBQueries executed: 219 queries
w2P Old Queries executed: 0 queries
w2P Total Queries executed: 219 queries
Page generated in 1.699 seconds

I hope this increase of speed is not due to some caching of the sever...
Does that mean that there was a problem with the indexes in my database?
I converted from dP ...
(0000418)
caseydk   
2009-09-03 05:07   
EXCELLENT.

So the *fields* were there but the *indexes* didn't exist for them. Yes, it's a artifact of converting from dotProject, but since the indexes should have been created, I think the problem is still on our end. What version of dP did you convert *from*?

It looks like I have some SQL updates to write this weekend. Fun times.

Thanks. :)
(0000419)
Lex   
2009-09-03 05:25   
yes, all of the fields where there.

What I can not say is, if the indexes of *every* of those fields where missing or if only a *few* of them where missing. Sorry for that - I was just clicking the Index button in phpMyAdmin for every of those fields and then tested again.

my dotProject Version was dotProject 2.1.1
(0000420)
casn   
2009-09-04 02:12   
I would not worry too much about the first link. It really makes the difference, atleast on DP.
If it would have similar impact on W2P, you would have even more bonus points.
(0000428)
caseydk   
2009-09-09 11:57   
In r615, I rebuilt all of the indexes from the ground up. This is database update 0000012.
(0000463)
caseydk   
2009-09-09 22:44   
Closed for release.

Issue History
2009-08-12 22:06LexNew Issue
2009-08-12 22:24caseydkStatusnew => assigned
2009-08-12 22:24caseydkAssigned To => caseydk
2009-08-12 22:24caseydkProjectv1.0 Release (Closed) => v1.1 Release (Closed)
2009-08-12 22:24caseydkRelationship addedchild of 0000142
2009-08-13 12:21caseydkNote Added: 0000386
2009-08-13 21:50LexNote Added: 0000387
2009-08-30 21:04caseydkNote Added: 0000405
2009-09-01 23:31LexNote Added: 0000411
2009-09-02 06:23caseydkNote Added: 0000412
2009-09-02 22:24LexNote Added: 0000417
2009-09-03 05:07caseydkNote Added: 0000418
2009-09-03 05:25LexNote Added: 0000419
2009-09-04 02:12casnNote Added: 0000420
2009-09-09 11:57caseydkStatusassigned => resolved
2009-09-09 11:57caseydkFixed in Version => 1.1
2009-09-09 11:57caseydkResolutionopen => fixed
2009-09-09 11:57caseydkNote Added: 0000428
2009-09-09 22:44caseydkStatusresolved => closed
2009-09-09 22:44caseydkNote Added: 0000463