SunShop Modifications Part 6

In this sec­tion we look at my cache mech­a­nism. It’s sim­i­lar in prin­ci­ple to the Word­Press “tran­sient” con­cept. For exam­ple, the “top sell­ers in the last 30 days” does not need to be cal­cu­lated every page load. Cal­cu­lat­ing once per hour should be more than suf­fi­cient. Doing both cal­cu­la­tions (all time best sell­ers and best sell­ers last 30 days) takes 1–4 sec­onds depend­ing on server load, and the Top Sell­ers page turns out to be one of the most pop­u­lar pages. It is there­fore a good can­di­date for caching.

I also found that a lot of the border/navigation items take a long time to gen­er­ate, and again are good can­di­dates for caching. Within Sun­Shop we make all sorts of lists for nav­i­ga­tion pur­poses, every page load, and then never use much of the result. With caching, I reduced the hun­dreds of queries per page load down to a dozen or so.

For exam­ple, if it takes a hun­dred 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 cer­tain cases, Sun­Shop does one query to get a sorted list of item IDs, and then loops through the list doing a sep­a­rate query for each item in the list.

We’ll use the Top Sell­ers plu­gin as our use case. We cache the result of each top sell­ers cal­cu­la­tion 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;

Imple­men­ta­tion

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 Sell­ers plu­gin uses this code. I stripped this down to the rel­e­vant code; the entire plu­gin 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 any­thing what­so­ever, and the sec­ond is the MD5 digest of that key. The MD5 key becomes our index for data­base lookups. The plain­text key, strictly speak­ing, does not even need to be stored in the data­base, but I keep it in there so that I can eas­ily observe the caching mech­a­nism in prac­tice on the live site.

Line 2. Build the plain­text key, either bb_topseller or bb_topseller30.

Line 3. Do the cache lookup for this plain­text key, and tell us whether or not we have a cache hit (suc­cess­ful retrieval) or not. If we do (line 4) return the cached result. We need go no further.

Line 6. We will be cal­cu­lat­ing a result and caching it. Set the cache item to expire after 60 minutes.

Line 7. Gen­er­ate the MySQL query.

Line 8. We run the actual query through the dbcache mech­a­nism. This allows us to cache the query result itself as a seri­al­ized PHP array. In this par­tic­u­lar case it’s a wasted step, because we’re going to cache the next step any­way. How­ever, this does show the mech­a­nism for caching actual data­base queries. See the caching code begin­ning with line 86.

Lines 9–11. Here is where we build the Top Sell­ers out­put HTML, plac­ing the result in $text.

Line 12. We cache the result using the plain­text key built at line 2.

Line 13. Return the result. In the­ory, then, all future loads of the Top Sell­ers page within the next hour will return the cached HTML out­put at line 4.

Imple­men­ta­tion lines 18–22. This is the admin-area hook. When­ever we do any sort of admin func­tion which could affect what has been cached, that admin func­tion should call inval­i­date­Cache(). This clears the entire cache table.