phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

Idea on caching some queries to a file - phpbb index page


 
Search this topic... | Search phpBB2 Discussion... | Search Box
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Author Message
JLA
Board Member



Joined: 30 Apr 2009

Posts: 451
Location: U.S.A


flag
PostPosted: Mon Aug 08, 2011 7:29 pm 
Post subject: Idea on caching some queries to a file - phpbb index page

We have been experimenting with caching some large queries to a file instead of running them through the MYSQL server.

1 big one is the "Forums" query on the index page. Since we have 500+ forum sections and also are using the subforums mod, the "Forums" query on the index is pretty big and takes some time to process and transfer all the data between the mysql server and web server.

We tried creating a cache file with the results of this query since the only time the result changes is when we add or delete a forum/subforum section.

Anyone have similar experience doing this? The generated cache file is about 803KB.

Do you think overall the performance will be better pull the query results from a static file or is it better if the query sits in the MYSQL query cache and still has to transit between the MYSQL server and webserver?

_________________
http://www.jlaforums.com
Back to top
Dog Cow
Board Member



Joined: 18 Nov 2008

Posts: 378


flag
PostPosted: Mon Aug 22, 2011 5:12 pm 
Post subject: Re: Idea on caching some queries to a file - phpbb index pag

JLA wrote:

Do you think overall the performance will be better pull the query results from a static file or is it better if the query sits in the MYSQL query cache and still has to transit between the MYSQL server and webserver?
Think locality of reference. A cache file on the web server should be stored in RAM on that machine, thus should be able to be accessed with a lower latency than a MySQL cached query.
_________________
Moof!
Lincoln's Tomb, Oak Ridge Cemetery, Springfield ILMac 512K BlogMac GUI
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 451
Location: U.S.A


flag
PostPosted: Mon Aug 22, 2011 5:21 pm 
Post subject: Re: Idea on caching some queries to a file - phpbb index pag

Dog Cow wrote:
JLA wrote:

Do you think overall the performance will be better pull the query results from a static file or is it better if the query sits in the MYSQL query cache and still has to transit between the MYSQL server and webserver?
Think locality of reference. A cache file on the web server should be stored in RAM on that machine, thus should be able to be accessed with a lower latency than a MySQL cached query.


Good point. ? now is how to get the cache file that has been generated into and accessed in webserver's RAM?

_________________
http://www.jlaforums.com
Back to top
Dog Cow
Board Member



Joined: 18 Nov 2008

Posts: 378


flag
PostPosted: Mon Aug 22, 2011 5:23 pm 
Post subject: Re: Idea on caching some queries to a file - phpbb index pag

JLA wrote:
Dog Cow wrote:
JLA wrote:

Do you think overall the performance will be better pull the query results from a static file or is it better if the query sits in the MYSQL query cache and still has to transit between the MYSQL server and webserver?
Think locality of reference. A cache file on the web server should be stored in RAM on that machine, thus should be able to be accessed with a lower latency than a MySQL cached query.


Good point. ? now is how to get the cache file that has been generated into and accessed in webserver's RAM?

The operating system on the computer should take care of that, as long as it has enough free RAM.

Modern operating systems (Mac OS X, Linux, Windows) will use RAM that is not being used for applications as a file system cache. As your applications use up more RAM, this cache space gets smaller.

_________________
Moof!
Lincoln's Tomb, Oak Ridge Cemetery, Springfield ILMac 512K BlogMac GUI
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 451
Location: U.S.A


flag
PostPosted: Mon Aug 22, 2011 10:45 pm 
Post subject: Re: Idea on caching some queries to a file - phpbb index pag

Dog Cow wrote:
JLA wrote:
Dog Cow wrote:
JLA wrote:

Do you think overall the performance will be better pull the query results from a static file or is it better if the query sits in the MYSQL query cache and still has to transit between the MYSQL server and webserver?
Think locality of reference. A cache file on the web server should be stored in RAM on that machine, thus should be able to be accessed with a lower latency than a MySQL cached query.


Good point. ? now is how to get the cache file that has been generated into and accessed in webserver's RAM?

The operating system on the computer should take care of that, as long as it has enough free RAM.

Modern operating systems (Mac OS X, Linux, Windows) will use RAM that is not being used for applications as a file system cache. As your applications use up more RAM, this cache space gets smaller.


Well our OS has kernel cache and user cache. We are exploring ways to work better with the kernel cache. Fortunately the index page is not one of the main traffic pages (due to header forum links, etc) so we have some flexibility to explore this further. But the caching method I previous mentioned has helped considerably with that page's load time when using the cache file.

_________________
http://www.jlaforums.com
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 729
Location: Texas


flag
PostPosted: Wed Nov 09, 2011 2:10 pm 
Post subject: Re: Idea on caching some queries to a file - phpbb index pag

One of the reasons I have not played with caching the forum index is security. Each user has (potentially) a different security profile, and therefore a potentially different forum index. Have you had to worry about that much?
_________________
phpBBDoctor Blog
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 451
Location: U.S.A


flag
PostPosted: Wed Jan 11, 2012 5:20 pm 
Post subject: Re: Idea on caching some queries to a file - phpbb index pag

drathbun wrote:
One of the reasons I have not played with caching the forum index is security. Each user has (potentially) a different security profile, and therefore a potentially different forum index. Have you had to worry about that much?


Well it really comes down to exactly which items are you wanting to cache.

We have found that with the WINCACHE for PHP5/FastCGI - you can cache several of the general queries that are not user specific. It makes quite a difference.

_________________
http://www.jlaforums.com
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 729
Location: Texas


flag
PostPosted: Wed Jan 11, 2012 5:59 pm 
Post subject: Re: Idea on caching some queries to a file - phpbb index pag

If it's the same result set every time, then caching makes sense. You could even replace the query with an include, and run the query via cron once a day, or at whatever frequency it makes sense. I use that technique for a couple of items on my board. Rather than a query there is an include statement, which brings in php code to be executed. The php code is dynamic; it's written by a cron job. For example, the flags code used here. I don't add new flags every day, so the flag cache is a series of array assignment statements. The flag cache file is updated "on demand" rather than by cron since it's not going to change without me knowing about it. icon_wink.gif

There is file I/O going on since the php code has to request the cache file, but there's I/O going on with a database request as well. What's different is I don't have to process the SQL query.

I thought about it more, and the current process for the board index is to get a list of forums and apply permissions to restrict what I can see. That could be done on a cache array just as easily as a SQL query, so the idea makes sense. Especially since you don't add / remove forums that often.

_________________
phpBBDoctor Blog
Back to top
JLA
Board Member



Joined: 30 Apr 2009

Posts: 451
Location: U.S.A


flag
PostPosted: Wed Feb 01, 2012 4:06 pm 
Post subject: Re: Idea on caching some queries to a file - phpbb index pag

drathbun wrote:
If it's the same result set every time, then caching makes sense. You could even replace the query with an include, and run the query via cron once a day, or at whatever frequency it makes sense. I use that technique for a couple of items on my board. Rather than a query there is an include statement, which brings in php code to be executed. The php code is dynamic; it's written by a cron job. For example, the flags code used here. I don't add new flags every day, so the flag cache is a series of array assignment statements. The flag cache file is updated "on demand" rather than by cron since it's not going to change without me knowing about it. icon_wink.gif

There is file I/O going on since the php code has to request the cache file, but there's I/O going on with a database request as well. What's different is I don't have to process the SQL query.

I thought about it more, and the current process for the board index is to get a list of forums and apply permissions to restrict what I can see. That could be done on a cache array just as easily as a SQL query, so the idea makes sense. Especially since you don't add / remove forums that often.


With the board index, you are right on point. On our site with over 500 forum sections and subforums, you can imagine what is going on there. With Wincache we are able to do something like this

Code:
//WINCACHE FOR MAIN FORUM QUERIES - JLA

$sql = "SELECT c.cat_id, c.cat_title, c.cat_order
               FROM ".CATEGORIES_TABLE." c
                ORDER BY c.cat_order";

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

      message_die(GENERAL_ERROR, 'Could not query categories list', '', __LINE__, __FILE__, $sql);

   }

   $category_rows = array();

   while(($category_rows[] = $db->sql_fetchrow($result))) ;

   $db->sql_freeresult($result);

   $bWinCache = false;

   $forum_data = wincache_ucache_get("FORUMDATAKEY", $bWinCache);

   if(!$bWinCache){

      $sql = "SELECT f.forum_id, f.cat_id, f.forum_name, f.forum_desc, f.forum_status, f.forum_order,
               f.forum_posts, f.forum_last_post_id, f.forum_last_post_time, f.auth_view, f.forum_icon,
               f.forum_parent
            FROM ( ".FORUMS_TABLE." f)
                     ORDER BY f.cat_id, f.forum_order";

      if(!($result = $db->sql_query($sql))){
         message_die(GENERAL_ERROR, 'Could not obtain forums information', '', __LINE__, __FILE__,
                     $sql);
      }

      $forum_data = $db->sql_fetchrowset($result);

// SET time limit on XXX below - JLA

      wincache_ucache_set("FORUMDATAKEY", $forum_data, XXX);

   }

   $subforums_list = array();

   if(($total_categories = count($category_rows))){

      foreach($forum_data as $row){

         if($row['forum_last_post_time'] > $userdata['user_lastvisit'])
            $new_topic_data[$row['forum_id']][$row['topic_id']] = $row['forum_last_post_time'];

      }

      $db->sql_freeresult($result);

      if(!($total_forums = count($forum_data))){

         message_die(GENERAL_MESSAGE, $lang['No_forums']);

      }



//END WINCACHE


this really helps thing along quite well.

_________________
http://www.jlaforums.com
Back to top
Display posts from previous:   
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 4 Hours
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0587 seconds using 16 queries. (SQL 0.0088 Parse 0.0008 Other 0.0490)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo