SunShop Modifications Part 6
In this section we look at my cache mechanism. It’s similar in principle to the WordPress “transient” concept. For example, the “top sellers in the last 30 days” does not need to be calculated every page load. Calculating once per hour should be more than sufficient. Doing both calculations (all time best sellers and best sellers last 30 days) takes 1-4 seconds depending on server load, and the Top Sellers page turns out to be one of the most popular pages. It is therefore a good candidate for caching.
I also found that a lot of the border/navigation items take a long time to generate, and again are good candidates for caching. Within SunShop we make all sorts of lists for navigation purposes, every page load, and then never use much of the result. With caching, I reduced the hundreds of queries per page load down to a dozen or so.
For example, if it takes a hundred queries to build up a sorted list, and we can safely cache the result for an hour or so, we have a huge page-performance gain. Why so many queries? In certain cases, SunShop does one query to get a sorted list of item IDs, and then loops through the list doing a separate query for each item in the list.
We’ll use the Top Sellers plugin as our use case. We cache the result of each top sellers calculation for 60 minutes.
Schema
Here is my cache table definition.
CREATE TABLE IF NOT EXISTS `ss_dbcache` (
`id` int(11) NOT NULL auto_increment,
`hashkey` char(32) NOT NULL,
`plainkey` text NOT NULL,
`expires` datetime NOT NULL,
`cachetype` enum('plain','serialized') NOT NULL,
`cachevalue` mediumtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `hashkey` (`hashkey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Implementation
Here is the entire cache implementation:
/**
* Ed Barnard, 9 February 2013.
* Copyright Ed Barnard, all rights reserved.
*
* Attempt to speed up page load times by caching various
* queries and html snippets
*/
class BBDBCache extends BBRegistryCompatibleBase implements BBRegistryCompatible {
private static $requiredIncludes = array(
'class.bb.dbcacheitem.php',
);
private $registry;
private $defaultExpire = 10; // Minutes to cache
public $cache = array();
public static $debug = 0;
public static function invalidateCache() {
global $DB_site;
$sql = "DELETE FROM `ss_dbcache` WHERE id >0";
$DB_site->query($sql);
}
public function haveText($plainKey) {
$cacheKey = $this->createKey($plainKey);
return $this->haveDBCache($cacheKey);
}
public function getText($plainKey) {
$cacheKey = $this->createKey($plainKey);
if($this->haveDBCache($cacheKey)) {
return $this->cache[$cacheKey]->getValue();
}
return null;
}
public function setText($plainKey, $text) {
$cacheKey = $this->createKey($plainKey);
if($this->isCachedButExpired($cacheKey)) {
// Update existing ss_dbcache row
$this->cache[$cacheKey]->setValue($text);
$this->cache[$cacheKey]->store($this->defaultExpire);
} else {
// Adding new ss_dbcache row and adding new object to in-memory cache
$populate = array(
'id' => 0,
'hashkey' => $cacheKey,
'plainkey' => $plainKey,
'expires' => '0000-00-00 00:00:00',
'cachetype' => 'plain',
'expired' => 0,
'cachevalue' => $text,
);
if(self::$debug) {
echo "<pre style='text-align:left;'><strong>What we are caching:</strong>\n";
print_r(array('resultrow' => $resultrow, 'populate' => $populate));
echo "</pre>\n";
}
$item = new BBDBCacheItem($populate);
$item->store($this->defaultExpire);
$this->cache[$cachekey] = $item;
}
}
public function haveDBCache($cacheKey) {
if($this->isCached($cacheKey)) {
if(self::$debug) {
echo "<pre style='text-align:left;'><strong>Our item is in memory cache</strong>\n</pre>\n";
}
return 1;
}
if(self::$debug) {
echo "<pre style='text-align:left;'><strong>Attempting to load item from db cache</strong>\n</pre>\n";
}
$this->cache[$cacheKey] = $this->loadItem($cacheKey);
if(self::$debug) {
echo "<pre style='text-align:left;'><strong>Now checking for successful load</strong>\n</pre>\n";
}
return $this->cache[$cacheKey]->isValid();
}
public function query_first($sql) {
return $this->query_sql($sql, 'query_first');
}
public function query($sql) {
global $showqueries, $explain, $totalQueries;
if(self::$debug) {
$showqueries = 1;
$explain = 1;
$totalQueries = 0;
}
return $this->query_sql($sql, 'query');
}
public function query_sql($sql, $queryFunc) {
global $DB_site;
$plainKey = $sql;
$cacheKey = $this->createKey($plainKey);
if($this->haveDBCache($cacheKey)) {
if(self::$debug) {
echo "<pre style='text-align:left;'><strong>Is cached:</strong>\n$sql</pre>\n";
}
return $this->cache[$cacheKey]->getValue();
}
if(self::$debug) {
echo "<pre style='text-align:left;'><strong>Need to query_first and cache:</strong>\n$sql</pre>\n";
}
if('query_first' === $queryFunc) {
$resultrow = $DB_site->query_first($sql);
} else {
$resultrow = array();
$results = $DB_site->query($sql);
while($onerow = $DB_site->fetch_assoc($results)) {
$resultrow[] = $onerow;
}
}
if($this->isCachedButExpired($cacheKey)) {
// Update existing ss_dbcache row
$this->cache[$cacheKey]->setValue($resultrow);
$this->cache[$cacheKey]->store($this->defaultExpire);
} else {
// Adding new ss_dbcache row and adding new object to in-memory cache
$populate = array(
'id' => 0,
'hashkey' => $cacheKey,
'plainkey' => $plainKey,
'expires' => '0000-00-00 00:00:00',
'cachetype' => 'serialized',
'expired' => 0,
'cachevalue' => $resultrow,
);
if(self::$debug) {
echo "<pre style='text-align:left;'><strong>What we are caching:</strong>\n";
print_r(array('resultrow' => $resultrow, 'populate' => $populate));
echo "</pre>\n";
}
$item = new BBDBCacheItem($populate);
$item->store($this->defaultExpire);
$this->cache[$cachekey] = $item;
}
return $resultrow;
}
public function storeItem($cacheKey) {
if($this->isCached($cacheKey) || $this->isCachedButExpired($cacheKey)) {
return $this->cache[$cacheKey]->store($this->defaultExpire);
}
return 0;
}
public function loadItem($cacheKey) {
if(self::$debug) {
echo "<pre style='text-align:left;'><strong>We are loading from ss_dbcache</strong>\n";
echo "</pre>\n";
}
return BBDBCacheItem::loadItem($cacheKey);
}
public function isCachedButExpired($cacheKey) {
if(!array_key_exists($cacheKey, $this->cache)) {
return 0;
}
return $this->cache[$cacheKey]->isLoadedButExpired();
}
public function isCached($cacheKey) {
if(!array_key_exists($cacheKey, $this->cache)) {
return 0;
}
return $this->cache[$cacheKey]->isValid();
}
public function createKey($plainKey) {
return md5($plainKey);
}
public function setExpire($minutes) {
$minutes = (int)$minutes;
if($minutes > 0) {
$this->defaultExpire = $minutes;
}
}
public function __construct() {
}
public function initialize() {
$this->registry = BBRegistry::getInstance();
$this->loadRequiredIncludes();
}
// Cloned from BBRegistry
private function loadRequiredIncludes() {
foreach(self::$requiredIncludes as $file) {
$this->registry->customInclude($file);
}
}
}
Rather than walk through the above code line by line, let’s take a look at how the Top Sellers plugin uses this code. I stripped this down to the relevant code; the entire plugin may be seen in Part 5:
$dbcache = BBRegistry::getRegisteredObject('BBDBCache', 'class.bb.dbcache.php');
$plainKey = 'bb_topseller'.$interval;
if($dbcache->haveText($plainKey)) {
return $dbcache->getText($plainKey);
}
$dbcache->setExpire(60);
$sql = $this->topseller_sql($interval);
$reviews = $dbcache->query($sql);
foreach ($reviews as $review) {
/* code removed for clarity, output is $text */
}
$dbcache->setText($plainKey, $text);
return $text;
Each cache item has two keys. First is a plain-text key, and can be anything whatsoever, and the second is the MD5 digest of that key. The MD5 key becomes our index for database lookups. The plaintext key, strictly speaking, does not even need to be stored in the database, but I keep it in there so that I can easily observe the caching mechanism in practice on the live site.
Line 2. Build the plaintext key, either bb_topseller or bb_topseller30.
Line 3. Do the cache lookup for this plaintext key, and tell us whether or not we have a cache hit (successful retrieval) or not. If we do (line 4) return the cached result. We need go no further.
Line 6. We will be calculating a result and caching it. Set the cache item to expire after 60 minutes.
Line 7. Generate the MySQL query.
Line 8. We run the actual query through the dbcache mechanism. This allows us to cache the query result itself as a serialized PHP array. In this particular case it’s a wasted step, because we’re going to cache the next step anyway. However, this does show the mechanism for caching actual database queries. See the caching code beginning with line 86.
Lines 9-11. Here is where we build the Top Sellers output HTML, placing the result in $text.
Line 12. We cache the result using the plaintext key built at line 2.
Line 13. Return the result. In theory, then, all future loads of the Top Sellers page within the next hour will return the cached HTML output at line 4.
Implementation lines 18-22. This is the admin-area hook. Whenever we do any sort of admin function which could affect what has been cached, that admin function should call invalidateCache(). This clears the entire cache table.
Thanks, This is great information and you obviously spent a lot of time on it. I need to get my Sunshop customizations out of the code base and what you have shown will help a lot. I really appreciate you publishing this!
Thank you David! You are quite welcome! In retrospect it may have been the long way around to put things off to the side, but it helps to keep custom code out of the main code base.