| View Issue Details [ Jump to Notes ] | [ Issue History ] [ Print ] | ||||||||
| ID | Project | Category | View Status | Date Submitted | Last Update | ||||
|---|---|---|---|---|---|---|---|---|---|
| 0000232 | v1.1 Release (Closed) | [All Projects] General | public | 2009-08-12 22:06 | 2009-09-09 22:44 | ||||
| Reporter | Lex | ||||||||
| Assigned To | caseydk | ||||||||
| Priority | normal | Severity | major | Reproducibility | always | ||||
| Status | closed | Resolution | fixed | ||||||
| Platform | x86 | OS | Windows XP | OS Version | XP SP2 | ||||
| Product Version | |||||||||
| Target Version | Fixed in Version | 1.1 | |||||||
| Summary | 0000232: w2p slow loading complex projects - or - slow database queries | ||||||||
| Description | When 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 Reproduce | 1) 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 Information | The project also exists in dotProject and there is | ||||||||
| Tags | No tags attached. | ||||||||
| Attached Files |
| ||||||||
 Relationships |
 Notes |
|
|
caseydk (administrator) 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. |
|
Lex (reporter) 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. |
|
caseydk (administrator) 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 |
|
Lex (reporter) 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 |
|
caseydk (administrator) 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? |
|
Lex (reporter) 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 ... |
|
caseydk (administrator) 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. :) |
|
Lex (reporter) 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 |
|
casn (reporter) 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. |
|
caseydk (administrator) 2009-09-09 11:57 |
In r615, I rebuilt all of the indexes from the ground up. This is database update 0000012. |
|
caseydk (administrator) 2009-09-09 22:44 |
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 |