[PATCH] Speed optimization

If you have coded modifications, additions or language files for TeamCal Pro share it with others here.
Franz
Fresh Boarder
Fresh Boarder
Posts: 13
Joined: 2012-04-05, 16:34
Are you a spam bot?: No
Location: Dresden
Contact:

[PATCH] Speed optimization

Postby Franz » 2012-05-14, 16:25

Hi there,

as others http://www.lewe.com/fo/viewtopic.php?f=17&t=3194 we experienced that TeamCal gets slow when you have a somewhere around 25/30 people in it.

To improve this situation we initialised a project with some high-school students from the Schüler Rechenzentrum of the TU Dresden (http://www.srz.tu-dresden.de/). They investigated the situation and came up with three simple solutions. Altogether we realised a speedup of 5.3. Which lowers the waiting time for a 12 month, 21 people TeamCal calendar view from 7.5 to 1.4 secs.

We publish theses patches in the hope that they will lower your waiting times, too. We get feedback whether they work for you as expected and they become integrated into the main TCpro.

1. require once:
Nearly every tcpro php file includes the config.tcpro.php file which holds the preferences as database user, password and so on. Most classes do that even on their creation time. So that in the end config.tcpro.php becomes 9570 times included.
The first patch uses require_once instead of require to include config.tcpro.php. This function only includes the file if it wasn't yet included. Therefore now only one real include and 9569 oh it's already here includes happen.
We removed a statement that deletes the intern representation of the configuration before every include, too. Even though we checked that it is okay to remove this statement and we don't experience errors: This could be a source for errors!
@George: What were the unset($CONF) statements good for? Were they just for historic reasons there?
In the end this patch results in a speedup of circa 2.5.
2. db connection
In the process of one calendar view generation a lot of database connections are established and used. In numbers these are 9568 connections and around 7 % of the request time is used only to establish these connections and select the database.
There is a persistent flag for the database connection. If you set this teamcal will use mysql_pconnect instead of the normal mysql_connect. pconnect reuses already created connections and therefore should solve this problem. But somehow it doesn't work. Setting this flag results in a even slower request (20% vs. 15% of request time (with patch 1 applied)).
So we do it one our own and use one db connection for every myDB object.
3. query cache
Last but not least TeamCal is slow due to the huge amount of sql queries. 33925 in this case. The key for this optimation is that a lot of this queries are asked more than once. So we simply cache the answer of the database inside TeamCal and give this cache answer back if we are asked the same query more than once. This results in 728 unique queries.

Optimization 2 & 3 together give a speedup of circa 2.5, too.

All figures are based on the 12 month, 21 people calendar view request. We used the xdebug profiler and the siege command line utility to maesure them.


Pay attention this patches are not fully tested. Due to the huge impact on TeamCal... They work for us but may trash your db or explode or whatsoever. Please be sure to have a backup if you try them and to give feedback ;).

Patch 1 (require_once): https://gist.github.com/2694298
Patch 2 (db connection & query cache): https://gist.github.com/2694303

Best regards,
Franz

vknecht
Fresh Boarder
Fresh Boarder
Posts: 1
Joined: 2012-06-19, 09:32
Are you a spam bot?: No

Re: [PATCH] Speedoptimization

Postby vknecht » 2012-06-19, 13:04

Hello,

I tried Patch 1 (require_once) and it indeed halved the waiting time, from 10s to 5s.
Had to retype it, tho, couldn't find a suitable patch command invokation.

Couldn't try Patch 2 (db connection & query cache) yet since it's not compatible with PHP 4.x

Thank you !

Heart1010
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 2012-05-07, 16:38
Are you a spam bot?: No

Re: [PATCH] Speedoptimization

Postby Heart1010 » 2012-06-20, 09:21

Great Franz!

Stumbled over this and applied your patches to the files and it really made a huge difference in speed!!!!!! :!: :wink: (we have ~40 active users)

Question to patch 2: there is in line 38

Code: Select all

+ if (!@ mysql_select_db($this->db_name, myDB::$db_handle)) {
+ $errtxt = "s
Is this $errtxt = "s correct or a typo!?

Heart1010
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 2012-05-07, 16:38
Are you a spam bot?: No

Re: [PATCH] Speedoptimization

Postby Heart1010 » 2012-06-26, 16:16

Another thing I mentioned. After updating a profile of an user (e.g. change the name) I get the following warning at top

Code: Select all

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/virtual/tsvmn.m25s11.vlinux.de/htdocs/teamcalc/includes/db.class.php on line 116
line 116 is here

Code: Select all

if (mysql_num_rows($result) > 0){

Heart1010
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 2012-05-07, 16:38
Are you a spam bot?: No

Re: [PATCH] Speedoptimization

Postby Heart1010 » 2012-06-27, 08:43

Same after updating group assign (groupassign.php), about ~6 times the mentioned error appears at top after apply

Code: Select all

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/virtual/tsvmn.m25s11.vlinux.de/htdocs/teamcalc/includes/db.class.php on line 116

User avatar
George
Platinum Boarder
Platinum Boarder
Posts: 365
Joined: 2004-11-29, 21:20
Are you a spam bot?: No
Contact:

Re: [PATCH] Speedoptimization

Postby George » 2012-06-29, 22:49

Hi Franz
thank you for a long overdue analysis of speed optimization. I simply had no time doing it being permamnently swamped with so many stuff.

I also recognized that litte 's' in line 39 of your DB patch. I guess it is a typo.

The unset command was added based on a security patch. It was the easiest way to prevent code insertion in the $CONF array. I would not take it out. The program will still work fine but it represents a security threat.

Best regards,
George

User avatar
George
Platinum Boarder
Platinum Boarder
Posts: 365
Joined: 2004-11-29, 21:20
Are you a spam bot?: No
Contact:

Re: [PATCH] Speedoptimization

Postby George » 2012-07-30, 21:05

Hi all,
I will incorporate this DB optimization code into the next release. However, there is in fact the error mentioned above that at least for UPDATE queries $result is not a valid SQL result resource. In order to avoid that I suggest:

Code: Select all

if (is_resource($result)) {
   if (mysql_num_rows($result) > 0) {
      mysql_data_seek($result, 0);
   }
}
Best regards,
Geogre

Heart1010
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 2012-05-07, 16:38
Are you a spam bot?: No

Re: [PATCH] Speedoptimization

Postby Heart1010 » 2012-07-31, 14:32

Thanks... error gone with that modification :wink:

mark_burge@hotmail.com
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 2011-09-30, 09:49

Re: [PATCH] Speedoptimization

Postby mark_burge@hotmail.com » 2012-10-24, 07:14

We're running nearly 400 users, so I had to do significant work to improve the speed.
I'm going to past my code for changed files. Do with it as you please.

OK, well files with php or txt extension aren't allowed. WHAT CAN I ATTACH???

User avatar
George
Platinum Boarder
Platinum Boarder
Posts: 365
Joined: 2004-11-29, 21:20
Are you a spam bot?: No
Contact:

Re: [PATCH] Speedoptimization

Postby George » 2012-10-24, 19:31

Hi Mark
thanks for your contribution. Can you ZIP the files and attach the archive?
Best regards,
George

mark_burge@hotmail.com
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 2011-09-30, 09:49

Re: [PATCH] Speedoptimization

Postby mark_burge@hotmail.com » 2012-10-25, 01:40

OK, here we go.
I need to clean these up a little bit, and maybe look through for any dodgy stuff I might have done as I was hacking away trialing things, but it's one of those things I may never get to so I will provide it now so at least its out there.

The main work is in the showmonth calendar display (for obvious reasons) and the groupassign page, which was taking > 6mins to submit all the changes (it now only updates records that were changed).

Not that I've changed some stuff that removes some features, like the director permissions and the avatars and remainders stuff is just plain commented out in showmonth... I was tailoring with our needs in mind and speed.

~400 users can display 1 month in 5 seconds now.
Attachments
teamcal changes.zip
(30.84 KiB) Downloaded 152 times

User avatar
George
Platinum Boarder
Platinum Boarder
Posts: 365
Joined: 2004-11-29, 21:20
Are you a spam bot?: No
Contact:

Re: [PATCH] Speedoptimization

Postby George » 2012-10-25, 11:17

Thanks again, Mark. Will definitely have a look at it...

User avatar
George
Platinum Boarder
Platinum Boarder
Posts: 365
Joined: 2004-11-29, 21:20
Are you a spam bot?: No
Contact:

Re: [PATCH] Speedoptimization

Postby George » 2012-10-31, 21:24

Hi Mark,
again, thanks for the great contribution in regards to speed optimization. I looked at all your changes and integrated most of it in the upcoming release. Here are some things that I noticed:

groupassign.php
Works great and is really fast now. A minor drawback is that the HTML code does not validate anymore because '#' is not an allowed character in the ID and NAME attribute. However, it does exactly what it is supposed to do and in fact it is important to use a character that is not used in the user names. I do not see any reason why W3C does not allow it in the attributes. One could use a '-' but that might also be used in a user name and would be replaced by the code then. So for now I decided to bite the bullit and use the #.

showmonth.function.php
As you announced, most of the changes you did in that routine. And your changes really kick in. However, there are a few things that don't work anymore.

First of all, you probably used an older release as the basis for your changes. I recently fixed a minor flaw in the repeat header count and that change was obviously not yet done in the file you used. The problem was that when your repeat header count (as set on the admin page) is equal to the amount of displayed users in the calendar, there is a trailing header printed out. I moved the header count from the end of the loop to the beginning.

To gain even more speed you disabled the birthday notes and the remainder counts. That might be just what you need in your environment because you don't use those features, but from a product point of view I cannot do that f course. Other users might still need it. In fact, when you enable the remainder section on your admin page and you expand it you will have an incorrect table cause a couple of <td> are missing. So I enabled those two sections again.

I can't wrap my head around your disabling of the checkAuth check in line 704 of your file. Did u really just disable it or do you achieve that in a different way? I can't see it. Maybe you can give your view on it.


So, thanks again for your work on this. I have attached my current merged working version of showmonth.function.php to this post. Maybe you can look at it and see what I have done.

Best regards,
George
Attachments
showmonth.function.zip
(10.23 KiB) Downloaded 150 times

User avatar
George
Platinum Boarder
Platinum Boarder
Posts: 365
Joined: 2004-11-29, 21:20
Are you a spam bot?: No
Contact:

Re: [PATCH] Speedoptimization

Postby George » 2012-11-26, 18:43

Implemented in 3.4.002


Who is online

Users browsing this forum: No registered users and 1 guest