Building a better mousetrap

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

I happened on a very interesting thread at Nuke Cops. Steven111 and Djmaze were/are discussing improvements in efficiency in phpnuke, which is a wide open area for improvement. Djmaze has embarked down the path of using DEFINES to save the state of certain variables to avoid useless lookups, etc., while building a page. Steven, on the other hand, has taken a very astute approach, in my opinion, by utilizing the STATIC declaration of variables within a function, thereby maintaining state while building a page. For more on STATIC variables, consult http://php.net as I won't go into it all here. Anyway, I have tweaked and honed Steven's code even more. These four functions in mainfile.php have been rewritten by Steven and then tweaked by me. I'd like you to run some benchmarks on your own site. Repeatedly call your home page, for instance, as it is now written. Maybe 10 times and record the build times. Then, replace the 4 functions with these and run the same test, discarding the first one because if you are using an optimizer, it will recache it the first time when it detects a change. I haven't tweaked the get_theme() function yet but Steven has his code in it. Please post back your results, comments, critiques, etc. BTW, this is written for v7.0 but I believe it should work on 6.9 also.

Code:

function is_admin($admin) {


    static $adminSave; //save from one call to the other


    if (isset($adminSave)) return ($adminSave);


    if ($admin) {


       global $prefix, $db, $user_prefix;


       if(!is_array($admin)) {


           $admin = base64_decode($admin);


           $admin = explode(":", $admin);


       }


        $aid = "$admin[0]";


        $pwd = "$admin[1]";


       $aid = addslashes($aid);


       if (isset($aid) AND isset($pwd)) {


           $sql = "SELECT pwd FROM ${user_prefix}_authors WHERE aid='$aid'";


         $result = $db->sql_query($sql);


         $row = $db->sql_fetchrow($result);


         $pass = $row[pwd];


         if(isset($pass) AND $pass == $pwd) return $adminSave = 1;


       }


       return $adminSave = 0;


   }


    return $adminSave = 0;


}





function is_user($user) {


    static $userSave; //save from one call to the other


    if (isset($userSave)) return ($userSave);


    if ($user) {


       global $prefix, $db, $user_prefix;


       if(!is_array($user)) {


           $user = base64_decode($user);


           $user = explode(":", $user);


       }


        $uid = "$user[0]";


        $pwd = "$user[2]";


       $uid = intval(addslashes($uid));


       if (isset($uid) AND isset($pwd)) {


           $sql = "SELECT user_password FROM ${user_prefix}_users WHERE user_id='$uid'";


         $result = $db->sql_query($sql);


         $row = $db->sql_fetchrow($result);


         $pass = $row[user_password];


         if(isset($pass) AND $pass == $pwd) return $userSave = 1;


       }


       return $userSave = 0;


   }


    return $userSave = 0;


}





function cookiedecode($user) {


    global $cookie, $prefix, $db, $user_prefix;


    static $cookieSave;


    $user = base64_decode($user);


    $cookie = explode(":", $user);


    if (!isset($cookieSave)) {


       $sql = "SELECT user_password FROM ${user_prefix}_users WHERE username='$cookie[1]'";


       $result = $db->sql_query($sql);


       $row = $db->sql_fetchrow($result);


       $cookieSave = $row;


    }


    else $row = $cookieSave;


    $pass = $row[user_password];


    if (isset($pass) AND $cookie[2] == $pass) return $cookie;


   unset($user);


   unset($cookie);


}





function get_theme() {


    global $user, $cookie, $Default_Theme;


    static $ThemeSelSave;  //save from one call to another


    if (isset($ThemeSelSave)) return ($ThemeSelSave);


    if(is_user($user)) {


      $user2 = base64_decode($user);


      $t_cookie = explode(":", $user2);


      if($t_cookie[9]=="") $t_cookie[9]=$Default_Theme;


      if(isset($theme)) $t_cookie[9]=$theme;


      if(!$tfile=@opendir("themes/$t_cookie[9]")) {


         $ThemeSel = $Default_Theme;


      }


      else {


         $ThemeSel = $t_cookie[9];


      }


   }


   else {


      $ThemeSel = $Default_Theme;


   }


   $ThemeSelSave = $ThemeSel;


   return($ThemeSel);


}

Regular Joined: Dec 26, 2003 Posts: 50

Hi Raven,

Thank you for your kind words Very Happy

djmaze did a great job to get this going, and I am just adding to it. I am sure it is going to get better from here on.

I would be intrested in some benchmarks using the above. The above REALLY works if you have a BIG USER TABLE. So big table benchmarkers are welcome.

I break down performance improvements into these areas:
A. Same User: on one page (like above)
B. Same User: page to page
C. All Users: one page
D. All Users: All Pages

There are also huge performance gains possible on #D which I happen to have done some work on (sorry for getting off-topic a bit). I currently use caching (cache-lite) to do:
-cache moderator info from page to page..really not changing, but huge table joins
-cache number of users online, last user, etc. ...really intensive
-cache scrolling forum posts
-cache forum pick list that shows up on 80% of pages

Ok, [off topic] enough said. I am attending this forum, and love to hear from all.

Raven, this forum is FAST Very Happy

steven

Posted: Fri Feb 20, 2004 10:44 pm

Staying on the subject of performance, I have NEVER seen QUERY CACHE mentioned on nuke or bb forums. If we use MySQL's QUERY CACHE, everything transparently, magically, and without any programming, will run hugely faster.

So for example, what we did above would give us some, but not drastic, performance improvement (if QUERY CACHE is on).

Has anyone done it? Like to know, and figure how to set it up (you probably need root access, I do Very Happy

).

steve

Only registered users can see links on this board! Get registered or login!

steve

Posted: Fri Feb 20, 2004 11:17 pm

I have used the query cache in Oracle for several years and we have found it to be a great asset. I know MySQL 4.x now has it and I am going to look into it. Right off the top of my head though, because nuke is so dynamic, I don't expect much help there, but then it would be very site dependent.

Posted: Fri Feb 20, 2004 11:39 pm

Raven wrote:

I have used the query cache in Oracle for several years and we have found it to be a great asset. I know MySQL 4.x now has it and I am going to look into it. Right off the top of my head though, because nuke is so dynamic, I don't expect much help there, but then it would be very site dependent.

Raven, that's a very good point.

Let's look at Query Cache performance. I checked my server, and cache is on by default. Here are some results of running the same query repeatedly.

Code:

mysql> select * from xyz_table where reference =1;


Empty set (12.33 sec)





mysql> select * from xyz_table where reference =1;


Empty set (1.59 sec)





mysql> select * from xyz_table where reference =1;


Empty set (1.58 sec)





mysql> select * from xyz_table where reference =1;


Empty set (1.59 sec)

As we can see, there is huge performance gain for this table, which is static.. Now looking at user table in Nuke, there are only two variables that change somewhat: user_session_time and user_lastvist. New registrations don't happen so frequently to be of concern. So we should still see caching in action, one on page, and even from page to page... I just don't know how much of a gain it is.

steve

Posted: Fri Feb 20, 2004 11:48 pm

Run a show status command and see how many queries are being cached on your site.

Posted: Fri Feb 20, 2004 11:55 pm

Would you share your technique for caching the scrolling forums block?

Posted: Fri Feb 20, 2004 11:58 pm

It's been a while, but Oracle allows you to cache your queries with dynamic variables. I don't seem to think that MySQL allows that from what I've read. In other words, if the only thing that changes in your query is the user-id value, Oracle caches the query and then dynamically substitutes the value - the best of both worlds!

Posted: Sat Feb 21, 2004 12:09 am

ok, I use cache-lite, which I have already set up (basically copying one file, and that is it, there is documentation on [edit: go to url two posts down from here]).

At the beginning of my mainfile.php, I do:

Code:

if (NUKER == 1) {  //added so forum admin would not give an error


   require_once("./includes/Cache/Lite.php");


   $options = array( 'cacheDir' => './includes/Cache/temp/','lifeTime' => 600 );


   $Cache_Lite = new Cache_Lite($options);   


}

The NUKER line is there, since I was getting errors when going to forum admin, so I disabled this code for forum admin that way.
I am caching for 600 seconds. Why? because I want to use this object in multiple places, and figured that is a good number.

Here is the code for block-Forums, with caching:

Code:




if (eregi("block-ForumsScroll.php", $_SERVER['PHP_SELF'])) {


    Header("Location: index.php");


    die();


}





//add caching- steve


global $Cache_Lite;


if (isset($Cache_Lite) && $content = $Cache_Lite->get($_SERVER['SERVER_NAME'] . 'scrollforum')) {  //make it unique to each webserver address, to not interfere


     //$content = $$temp; already got content, do nothing else


} 


else {


   


   //include_once ('blocks/smileys.php');





   global $prefix, $dbi, $sitename, $user, $cookie, $group_id;


   $count = 1;


   $amount = 15;


   $content = "<A name= \"scrollingCode\"></A>";


   $content .="<MARQUEE behavior= \"scroll\" align= \"center\" direction= \"up\" height=\"220\" scrollamount= \"2\" scrolldelay= \"25\" onmouseover='this.stop()' onmouseout='this.start()'>";


   $content .="<center> <STYLE=\"text-decoration: none\"><font color=\"#666666\"><b>Last $amount Forum Messages</b></center>";


   $result1 = sql_query("SELECT topic_id, topic_last_post_id, topic_title FROM ".$prefix."_bbtopics ORDER BY topic_last_post_id DESC LIMIT $amount", $dbi);


   $content .= "<br>";


   while(list($topic_id, $topic_last_post_id, $topic_title) = sql_fetch_row($result1, $dbi)) {


   $result2 = sql_query("SELECT topic_id, poster_id, FROM_UNIXTIME(post_time,'%b %d, %Y at %T') as post_time FROM ".$prefix."_bbposts where post_id='$topic_last_post_id'", $dbi);


   list($topic_id, $poster_id, $post_time)=sql_fetch_row($result2, $dbi);


   


   $result3 = sql_query("SELECT username, user_id FROM ".$prefix."_users where user_id='$poster_id'", $dbi);


   list($username, $user_id)=sql_fetch_row($result3, $dbi);


   


   //$topic_title = substr("$topic_title", 0,17);


   //$topic_title=parseEmoticons($topic_title);


   // Remove the comment below to add the counter


   //$content .="<STYLE=\"text-decoration: none\"><font color=\"#666666\"><b>Message: $count<br></b>";


   


   $content .= "<img src=\"modules/Forums/templates/subSilver/images/icon_mini_message.gif\" border=\"0\"alt=\"\"><a href=\"forums.html?amp;file=viewtopic&amp;p=$topic_last_post_id#$topic_last_post_id\"STYLE=\"text-decoration: none\"><b> $topic_title </b></a><br><font color=\"#666666\"><i>Last post by <A HREF=\"forums.html?file=profile&mode=viewprofile&u=$user_id\"STYLE=\"text-decoration: none\"> $username </a> on $post_time</i></font><br><br>";


   $count = $count + 1;


   }


   $content .= "<br><center>[ <a href=\"forums.html\"STYLE=\"text-decoration: none\">$sitename ]</center>";


   //$content .= "<center><img src=\"images/banners/fatalexception-logo-88x31.gif\" border=\"0\"></center>";


   $content .= "</a>";


   


   if (isset($Cache_Lite)) $Cache_Lite->save($content);  //steve


}

Basically, if there is cache (at the beginning) I read from cache, otherwise if cache expired, I go down the code, and save into cache for next time.

This would provide a real performance boost for your forum. Again, due to mySQL Cache, you would see the boost if your forum table is frequently updated.

steve

Posted: Sat Feb 21, 2004 12:10 am

Once you get going with it, you can just as easily apply it to your "Site Info" which is also resource intensive.

As I mentioned before, reading moderator info for forums is another area which changes very very slowly, and we don't have to hit mySQL with join queries on every page!!

Posted: Sat Feb 21, 2004 12:19 am

..and this is one place were serious Cachers go:

Only registered users can see links on this board! Get registered or login!

P.S. I am not for full page caching (like that thread talks about), I think too many thinks change on a page, but I like selective caching, like we have been talking about, although you could easily cache ob_get_contents() which would be full page caching. The one drawback of cache-lite is that it saves info to disk Confused

, but then I hear that Linux caches reads from disk too Razz

There are other solutions that do "in-memory" caching, which I have not looked at.

Posted: Sat Feb 21, 2004 1:17 am

As discussed before, you would want to cache users info:::

open html\modules\forums\index.php

Replace the code (you can figure out what I am talking about) Very Happy

Code:

//steve - use cache to increase performance


if ($CacheData = $Cache_Lite->get($_SERVER['SERVER_NAME'] . 'stats')) {  //make it unique to each webserver address, to not interfere


     $CacheData = unserialize($CacheData);


     $total_posts = $CacheData[0];


     $total_users = $CacheData[1];


     $newest_userdata = $CacheData[2];


} 


else {


   $total_posts = get_db_stat('postcount');


   $total_users = get_db_stat('usercount');


   $newest_userdata = get_db_stat('newestuser');


   


   $CacheData = array($total_posts, $total_users, $newest_userdata);


   $Cache_Lite->save(serialize($CacheData));


}

Posted: Sat Feb 21, 2004 1:19 am

steve1 wrote:

..and this is one place were serious Cachers go:

Only registered users can see links on this board! Get registered or login!

That's a pretty intense thread! I will catch up on this later today. 1:18am here and I'm off to bed. If you wouldn't mind, please send me a pm or email with a bio on yourself and/or a resume. Thanks.

Posted: Sat Feb 21, 2004 1:21 am

This is probably the most important cache, since there is some serious table joins going on. Enjoy. Steve Razz

[edit: in html\modules\forums\index.php]

Code:




        //**********************cache the following, does not change very often!!  steve***************


        if ($temp = $Cache_Lite->get($_SERVER['SERVER_NAME'] . 'moderators')) {  //make it unique to each webserver address, to not interfere


           $forum_moderators = unserialize($temp);


      }


      else { 





           //


           // Obtain list of moderators of each forum


           // First users, then groups ... broken into two queries


           //


           $sql = "SELECT aa.forum_id, u.user_id, u.username


                   FROM " . AUTH_ACCESS_TABLE . " aa, " . USER_GROUP_TABLE . " ug, " . GROUPS_TABLE . " g, " . USERS_TABLE . " u


                   WHERE aa.auth_mod = " . TRUE . "


                           AND g.group_single_user = 1


                           AND ug.group_id = aa.group_id


                           AND g.group_id = aa.group_id


                           AND u.user_id = ug.user_id


                   GROUP BY u.user_id, u.username, aa.forum_id


                   ORDER BY aa.forum_id, u.user_id";


           if ( !($result = $db->sql_query($sql)) )


           {


                   message_die(GENERAL_ERROR, 'Could not query forum moderator information', '', __LINE__, __FILE__, $sql);


           }


   


           $forum_moderators = array();


           while( $row = $db->sql_fetchrow($result) )


           {


                   $forum_moderators[$row['forum_id']][] = '<a href="' . append_sid("profile.$phpEx?mode=viewprofile&amp;" . POST_USERS_URL . "=" . $row['user_id']) . '">' . $row['username'] . '</a>';


           }


   


           $sql = "SELECT aa.forum_id, g.group_id, g.group_name


                   FROM " . AUTH_ACCESS_TABLE . " aa, " . USER_GROUP_TABLE . " ug, " . GROUPS_TABLE . " g


                   WHERE aa.auth_mod = " . TRUE . "


                           AND g.group_single_user = 0


                           AND g.group_type <> " . GROUP_HIDDEN . "


                           AND ug.group_id = aa.group_id


                           AND g.group_id = aa.group_id


                   GROUP BY g.group_id, g.group_name, aa.forum_id


                   ORDER BY aa.forum_id, g.group_id";


           if ( !($result = $db->sql_query($sql)) )


           {


                   message_die(GENERAL_ERROR, 'Could not query forum moderator information', '', __LINE__, __FILE__, $sql);


           }


   


           while( $row = $db->sql_fetchrow($result) )


           {


                   $forum_moderators[$row['forum_id']][] = '<a href="' . append_sid("groupcp.$phpEx?" . POST_GROUPS_URL . "=" . $row['group_id']) . '">' . $row['group_name'] . '</a>';


           }


           


           $Cache_Lite->save(serialize($forum_moderators));  //steve   


      }        


       //**********************************end of forum moderators cache: steve**************

Posted: Sat Feb 21, 2004 1:30 am

Quote:

I haven't tweaked the get_theme()

mine is always DeepBlue, so I hard-coded the function to just always return that.... i.e. I put return("DeepBlue") as the first line in the function. If you allow your users to change themes, your situation would be different (most sites don't, I believe). Razz

Posted: Sat Feb 21, 2004 8:45 am

Actually, most site do allow their users to change themes. That's why, if you're going to optimize, you probably need to to do that at the source.

Posted: Sun Feb 22, 2004 3:44 pm

Hi,

Ok, I found this thread over at Nukecops and performed the mainfile.php changes. I was more than impressed with the two second reduction in page generation time and after being pointed here by steve1 (cheers for that Smile

) I decided I'd have a go with Cache_Lite

Downloaded the archive and uploaded to my server, I then made the change to mainfile.php from steve1's post and then added his same modifications to my scrolling forums block. Nothing happens Sad

All my path's seem correct and I don't get any errors?? No temp files appear in the temp direectory I created.

I'm guessing I missed something obvious though, anyone got any pointers for me?

Cheers, Paul K

Posted: Sun Feb 22, 2004 3:52 pm

Hi Paul,

Make sure you have the file Lite.php installed, and make sure your temp directory has proper access rights so the script can write to it. Also search on your disk for PEAR.php, and save it to your root directory as well.

This should do it. You may want to read on of the post from me on this thread, with the "karakas" url. There is more about cache-lite there, but this should get you going.

steve

Posted: Sun Feb 22, 2004 11:09 pm

Paul,
Also just before this code

Code:

if (NUKER == 1) {  //added so forum admin would not give an error 


   require_once("./includes/Cache/Lite.php"); 


   $options = array( 'cacheDir' => './includes/Cache/temp/','lifeTime' => 600 ); 


   $Cache_Lite = new Cache_Lite($options);    


}

insert another line:

Code:

define ('NUKER',1);

Once your code works, move the above line to your root's index.php instead, but you can test it this way for now.

steve

New Member Joined: Sep 24, 2003 Posts: 21

Ok i have been playing with the Cache Lite for some time now and and love the performance I am getting from it. I recently found this post and decided to try steves method of doing. What i currently have are all the blocks cached by using this

Code:

// include the Cache-Lite package 


require_once("includes/Cache_Lite/Lite.php"); 





// set some variables 


$options = array( 


  "cacheDir" => "/tmp/Cache_Lite/", 


  "lifeTime" => 300 


); 





// create a Cache_Lite object 


$objCache = new Cache_Lite($options); 





// test if there exists a valid cache, 


// the ID will be the basename of the block 


$fileid = basename($blockfile,".php"); 


if ($content = $objCache->get($fileid)) { 


  // add a message indicating this is cached output 


  //$content .= "\n[cached with ID=$fileid]"; 


} 


else 


{

Then at the bottom of the block i use this

Code:

$objCache->save($content, $fileid); 


}

My question is can i still use Steves method for the Forums index and other modules without interfering with this code. And how would i insert that code described for the modules/Forums/index.php

I tried several ways and kept getting called to undefined function.

Here is the line in question

Code:

 $total_posts = get_db_stat('postcount'); 


   $total_users = get_db_stat('usercount'); 


   $newest_userdata = get_db_stat('newestuser'); 


   $newest_user = $newest_userdata['username'];


   $newest_uid = $newest_userdata['user_id'];

I tried the following

Code:




//steve - use cache to increase performance 


if ($CacheData = $Cache_Lite->get($_SERVER['SERVER_NAME'] . 'stats')) {  //make it unique to each webserver address, to not interfere 


     $CacheData = unserialize($CacheData); 


     $total_posts = $CacheData[0]; 


     $total_users = $CacheData[1]; 


     $newest_userdata = $CacheData[2]; 


} 


else { 


   $total_posts = get_db_stat('postcount'); 


   $total_users = get_db_stat('usercount'); 


   $newest_userdata = get_db_stat('newestuser'); 


    


   $CacheData = array($total_posts, $total_users, $newest_userdata); 


   $Cache_Lite->save(serialize($CacheData)); 


}


$newest_user = $newest_userdata['username'];


   $newest_uid = $newest_userdata['user_id'];


//.....rest of modules/Forums/index.php

Also for some of us that are not as experianced with coding could you possibbly shed some light on where i can find that moderator sql squery?

Thanks for the great tips and the links!

Posted: Mon Feb 23, 2004 12:14 am

You can create multiple objects, and they would (should?) not interfere. So you can have your:

Code:

// create a Cache_Lite object 


$objCache = new Cache_Lite($options);

..and just rename my cache object like this:

Code:

$objCacheAA = new Cache_Lite($options);

so now we have $objCache and $objCacheAA... to be double safe, you can also have the two objects write to 2 different cache directories.

See my earlier comments about defining "NUKER"... see if that makes it work.

[/code]

Posted: Mon Feb 23, 2004 12:20 am

Quote:

Also for some of us that are not as experianced with coding could you possibbly shed some light on where i can find that moderator sql squery?

I have edited the "moderator" post, to have the path to the file (3 rd line of the post,, it was not obvious Embarassed

).

steve

Posted: Mon Feb 23, 2004 12:23 am

By the way, once you dig deep, you get to a point where you really need a good debugger/IDE to make this stuff work. Cool

Posted: Mon Feb 23, 2004 12:29 am

define ('NUKER',1);

I added that in the mainfile directly above the code as described and It hosed the whole site.

Code:

Warning: main(/includes/Cache_Lite/Lite.php): failed to open stream: No such file or directory in /var/www/html/mainfile.php on line 23





Fatal error: main(): Failed opening required '/includes/Cache_Lite/Lite.php' (include_path='.:/php/includes:/usr/share/php') in /var/www/html/mainfile.php on line 23

And the file is there and functioning i use it on all the blocks.

Posted: Mon Feb 23, 2004 12:41 am

steve1 wrote:

Quote:

Also for some of us that are not as experianced with coding could you possibbly shed some light on where i can find that moderator sql squery?

I have edited the "moderator" post, to have the path to the file (3 rd line of the post,, it was not obvious Embarassed

).

steve

Thats what i thought but i just wanted to make sure before i started "playing".

Telli