Welcome to all phpBB2 Refugees!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. |
|
Author |
Message |
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: 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
|
Posted: 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 IL • Mac 512K Blog • Mac GUI |
|
Back to top |
|
|
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: 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
|
Posted: 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 IL • Mac 512K Blog • Mac GUI |
|
Back to top |
|
|
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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.
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
|
Posted: 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.
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 |
|
|
|
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
|
|