[RC] SQL Cache (Cached Generation)

A place for MOD Authors to post and receive feedback on MODs still in development. No MODs within this forum should be used within a live environment! No new topics are allowed in this forum.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

IMPORTANT: MOD Development Forum rules

On February 1, 2009 this forum will be set to read only as part of retiring of phpBB2.
greatgamer
Registered User
Posts: 657
Joined: Wed Nov 27, 2002 8:47 pm

Post by greatgamer »

Sorry but I have another question. If you have enabled MySQL caching on the server's my.cnf file, doesn't that defeat the purpose of using this mod? Thanks.

User avatar
stevemagruder
Registered User
Posts: 210
Joined: Wed Jul 02, 2003 2:05 pm
Location: Louisville, KY
Contact:

Post by stevemagruder »

greatgamer wrote: Sorry but I have another question. If you have enabled MySQL caching on the server's my.cnf file, doesn't that defeat the purpose of using this mod? Thanks.


I'm not knowledgeable about how the MySQL cache works, but even if the MySQL cache is enabled, you are still (presumably) hitting the database server when a query is supplied data from the cache. With this mod, you will dramatically decrease the hits to that server altogether.
Steve Magruder - WebCommons :: Media
Administrator for the Louisville History & Issues discussion board

greatgamer
Registered User
Posts: 657
Joined: Wed Nov 27, 2002 8:47 pm

Post by greatgamer »

Thanks for the quick responses :) This may explain the MySQL caching system:

http://www.databasejournal.com/features ... hp/3110171

When enabled the MySQL based dynamic pages load up as fast as an HTML page since data is only updated when the info is new.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

The MySQL query cache is only good for slowly changing data, and when the sql query is identical from one page view to another. So for example, the ranks data doesn't change very often (if at all) once a board is set up. But topic data can literally change every time someone looks at it. Why? Because the topic_views counter increases. :-) My query cache hit runs just over 50% on my busy board, and that's after I have eliminated queries to the ranks table, config table, and several other tables that never change.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

greatgamer
Registered User
Posts: 657
Joined: Wed Nov 27, 2002 8:47 pm

Post by greatgamer »

Thanks, so you are saying that this mod will increase speed even with MySQL cache enabled? Thanks.

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

I don't really know, as I have not looked at the code. The challenge is that you can only cache queries that don't change, whether MySQL does the cache or this MOD does the cache may not matter.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

User avatar
stevemagruder
Registered User
Posts: 210
Joined: Wed Jul 02, 2003 2:05 pm
Location: Louisville, KY
Contact:

Post by stevemagruder »

drathbun wrote: I don't really know, as I have not looked at the code. The challenge is that you can only cache queries that don't change, whether MySQL does the cache or this MOD does the cache may not matter.


This mod has two levels of cache: Overall and Post-related. The overall cache applies to queried data that rarely changes, like configuration or category/forum definitions. The post-related cache applies to topic/post data, as with many forums, it will change somewhat frequently, but in the time between posts, the board receives many page views.

This mod has dramatically reduced my hits to the MySQL server.

The only downside, and it's minor, is that the displayed values for topic views update only upon each post. That's fine with me. But I made it less of a downside by clearing the post-related cache whenever someone votes in a poll (that wasn't part of this mod).
Steve Magruder - WebCommons :: Media
Administrator for the Louisville History & Issues discussion board

greatgamer
Registered User
Posts: 657
Joined: Wed Nov 27, 2002 8:47 pm

Post by greatgamer »

Oh last question, was this mod ever adapted to work with the sub forums mod?

User avatar
stevemagruder
Registered User
Posts: 210
Joined: Wed Jul 02, 2003 2:05 pm
Location: Louisville, KY
Contact:

Post by stevemagruder »

greatgamer wrote: Oh last question, was this mod ever adapted to work with the sub forums mod?


Not that I know of.

I've had to extend what it does to other mods/changes I have done to my branch of phpBB.

Which reminds me... for those who aren't programmers and who have added a good deal of mods to their board, this particular mod is really only a partial coverage of what is possible to cache -- it cannot "read the minds" of other mods made to the board.

I had mods that displayed "featured posts" on the index page as well as "recent activity" (latest replied-to topics) on a new page -- these had to be updated to be a part of this caching system like the base phpBB code was.
Steve Magruder - WebCommons :: Media
Administrator for the Louisville History & Issues discussion board

greatgamer
Registered User
Posts: 657
Joined: Wed Nov 27, 2002 8:47 pm

Post by greatgamer »

Great thanks. If I don't want to install that SQL info on the page footer which code can I leave out besides the template edit. Is this the only part?

Code: Select all

# 
#-----[ OPEN ]------------------------------------------ 
#
language/lang_english/lang_main.php

# 
#-----[ FIND ]------------------------------------------ 
#
//
// That's all, Folks!
// -------------------------------------------------

# 
#-----[ BEFORE, ADD ]------------------------------------------ 
#
//
// Cached generation
//
$lang['debug_generation'] = 'Debug: page generation = %s seconds,';
$lang['sql_queries'] = 'sql queries = %s';


# 
#-----[ OPEN ]------------------------------------------ 
#
includes/page_tail.php

# 
#-----[ FIND ]------------------------------------------ 
#
	'overall_footer' => ( empty($gen_simple_header) ) ? 'overall_footer.tpl' : 'simple_footer.tpl')
);

# 
#-----[ AFTER, ADD ]------------------------------------------ 
#
//
// Cached Generation
//
$page_gen_time = sprintf('%.5f', microtime_float() - $GLOBALS['page_gen_start']);

# 
#-----[ FIND ]------------------------------------------ 
#
$template->assign_vars(array(

# 
#-----[ AFTER, ADD ]------------------------------------------ 
#
	// Cached Generation	
	'PAGE_GENERATION' => sprintf($lang['debug_generation'], $page_gen_time),
	'MYSQL_QUERIES' => sprintf($lang['sql_queries'], $db->num_queries),

# 
#-----[ OPEN ]------------------------------------------ 
#
templates/subSilver/overall_footer.tpl

# 
#-----[ FIND ]------------------------------------------ 
#
Powered by <a href="http://www.phpbb.com/" target="_phpbb" class="copyright">phpBB</a> &copy; 2001, 2005 phpBB Group<br 

/>{TRANSLATION_INFO}</span></div>

# 
#-----[ IN-LINE FIND ]------------------------------------------ 
#
{TRANSLATION_INFO}

# 
#-----[ IN-LINE AFTER, ADD ]------------------------------------------ 
#
<br />{PAGE_GENERATION}&nbsp;{MYSQL_QUERIES}
Or does that have to be included for this mod to work properly?

User avatar
stevemagruder
Registered User
Posts: 210
Joined: Wed Jul 02, 2003 2:05 pm
Location: Louisville, KY
Contact:

Post by stevemagruder »

That looks like the code to exclude if you don't want that feature (even though I think it's very useful). It's not necessary for the caching to work.
Steve Magruder - WebCommons :: Media
Administrator for the Louisville History & Issues discussion board

greatgamer
Registered User
Posts: 657
Joined: Wed Nov 27, 2002 8:47 pm

Post by greatgamer »

Great thanks a lot :) Sorry for all of the questions, but can I leave this out as well? It looks like it is part of that SQL info part in the footer:

Code: Select all

# 
#-----[ OPEN ]------------------------------------------ 
#
common.php

# 
#-----[ FIND ]------------------------------------------ 
#
//  
// Define some basic configuration arrays this also prevents 

# 
#-----[ BEFORE, ADD ]------------------------------------------ 
#
function microtime_float()  
{  
   list($usec, $sec) = explode(" ", microtime());  
   return ((float)$usec + (float)$sec);  
}  
 
$GLOBALS['page_gen_start'] = microtime_float(); 

# 
#-----[ FIND ]------------------------------------------ 
#
	FROM " . CONFIG_TABLE;
if( !($result = $db->sql_query($sql)) )

# 
#-----[ REPLACE WITH ]------------------------------------------ 
#
	FROM " . CONFIG_TABLE;
if( !($result = $db->sql_query($sql, false, true)) )

User avatar
stevemagruder
Registered User
Posts: 210
Joined: Wed Jul 02, 2003 2:05 pm
Location: Louisville, KY
Contact:

Post by stevemagruder »

You can leave out the first change, but the second change caches the configuration query -- I'd leave that in.
Steve Magruder - WebCommons :: Media
Administrator for the Louisville History & Issues discussion board

greatgamer
Registered User
Posts: 657
Joined: Wed Nov 27, 2002 8:47 pm

Post by greatgamer »

Thanks again :)

greatgamer
Registered User
Posts: 657
Joined: Wed Nov 27, 2002 8:47 pm

Post by greatgamer »

I noticed in includes/functions.php it explains to find and replace this:

Code: Select all

# 
#-----[ FIND ]------------------------------------------ 
#
		FROM  " . WORDS_TABLE;
	if( !($result = $db->sql_query($sql)) )

# 
#-----[ REPLACE WITH ]------------------------------------------ 
#
		FROM  " . WORDS_TABLE;
	if( !($result = $db->sql_query($sql, false, true)) )
However that is not there (at least it is not in my includes/functions.php file). Ah nevermind it looks like mine has changed from using one of the mods from a while back (perhaps the word censor per forum mod).

Post Reply

Return to “[2.0.x] MODs in Development”