2017-11-20 05:29 PST

View Issue Details Jump to Notes ]
IDProjectCategoryView StatusLast Update
0000232v1.1 Release (Closed)[All Projects] Generalpublic2009-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.
Attached Files

-Relationships
child of 0000142closedcaseydk Pending Requests System Performance Improvements 
+Relationships

-Notes

~0000386

caseydk (administrator)

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 (reporter)

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 (administrator)

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 (reporter)

... 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 (administrator)

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 (reporter)

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 (administrator)

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 (reporter)

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 (reporter)

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 (administrator)

In r615, I rebuilt all of the indexes from the ground up. This is database update 0000012.

~0000463

caseydk (administrator)

Closed for release.
+Notes

-Issue History
Date Modified Username Field Change
2009-08-12 22:06 Lex New Issue
2009-08-12 22:24 caseydk Status new => assigned
2009-08-12 22:24 caseydk Assigned To => caseydk
2009-08-12 22:24 caseydk Project v1.0 Release (Closed) => v1.1 Release (Closed)
2009-08-12 22:24 caseydk Relationship added child of 0000142
2009-08-13 12:21 caseydk Note Added: 0000386
2009-08-13 21:50 Lex Note Added: 0000387
2009-08-30 21:04 caseydk Note Added: 0000405
2009-09-01 23:31 Lex Note Added: 0000411
2009-09-02 06:23 caseydk Note Added: 0000412
2009-09-02 22:24 Lex Note Added: 0000417
2009-09-03 05:07 caseydk Note Added: 0000418
2009-09-03 05:25 Lex Note Added: 0000419
2009-09-04 02:12 casn Note Added: 0000420
2009-09-09 11:57 caseydk Status assigned => resolved
2009-09-09 11:57 caseydk Fixed in Version => 1.1
2009-09-09 11:57 caseydk Resolution open => fixed
2009-09-09 11:57 caseydk Note Added: 0000428
2009-09-09 22:44 caseydk Status resolved => closed
2009-09-09 22:44 caseydk Note Added: 0000463
+Issue History