Recently I had to improve performance of several PHP sites. One of the problems is complicated queries, that take a long time to compute by the MySQL server. Therefore, I decided to look for a solution.
I found a nice solution on http://pureform.wordpress.com/2008/05/21/using-memcache-with-mysql-and-php/ and I decided to work this out for my sites.
For php4 sites I rewrote this code to a simple class:
/**
* Bastiaan Welmers - 20081013
* instataniate this class and use mysql_query_cache instead of mysql_query for slow queries
*
* php4 style class
*
*/
class memCacheDb
{
var $memcache;
// constructor
function memCacheDb()
{
# Connect to memcache:
$this->memcache = new Memcache;
$this->memcache->connect('localhost', 11211) or die ("Could not connect to memcache");
}
# Gets key / value pair into memcache ... called by mysql_query_cache()
function getCache($key) {
return ($this->memcache) ? $this->memcache->get($key) : false;
}
# Puts key / value pair into memcache ... called by mysql_query_cache()
function setCache($key,$object,$timeout = 60) {
return ($this->memcache) ? $this->memcache->set($key,$object,MEMCACHE_COMPRESSED,$timeout) : false;
}
# Caching version of mysql_query()
function mysql_query_cache($sql, $linkIdentifier = false, $timeout = 600 /* = 10 minutes expiration */) {
if (!($cache = $this->getCache(md5("mysql_query" . $sql)))) {
$cache = false;
$r = ($linkIdentifier !== false) ? mysql_query($sql,$linkIdentifier) : mysql_query($sql);
if (is_resource($r) && (($rows = mysql_num_rows($r)) != 0)) {
for ($i=0;$i<$rows;$i++) {
$fields = mysql_num_fields($r);
$row = mysql_fetch_array($r);
for ($j=0;$j<$fields;$j++) {
if ($i == 0) {
$columns[$j] = mysql_field_name($r,$j);
}
$cache[$i][$columns[$j]] = $row[$j];
}
}
if (!$this->setCache(md5("mysql_query" . $sql),$cache,$timeout)) {
die('Error trying to connect to memcache');
# If we get here, there isn't a memcache daemon running or responding
}
}
}
return $cache;
}
}
For the PHP5 applications I wrote a PHP5 style style class and I added expiration logic. You can now use MemCacheDb::deleteMatching($arrayOfKeywords) to remove queries from cache that contain certain keywords. You can use this for example in your CMS, just call the deleteMatching method in the update/creation logic with the updated table name in the keywords and all cached queries will expire.
/**
* Bastiaan Welmers - 20081013
* instataniate this class and use this mysql_query methoid instead of mysql_query for slow queries
* it wil return a array with assosiative arrays with the output.
* So use foreach($mdb->mysql_query($query) as $rows) {}
*
* php5 style class
*
*/
class MemCacheDb
{
private $_memcache;
private $_appKey = 'Change this key for your application';
private $_queries = null;
// constructor
public function __construct()
{
// Connect to memcache:
$this->_memcache = new Memcache();
$this->_memcache->connect('localhost', 11211) or die ("Could not connect to memcache");
$this->_appKey = md5(__FILE__ . $this->_appKey);
}
public function __destruct()
{
if (is_array($this->_queries))
{
$this->_saveQueries();
}
}
public function getStats()
{
return $this->_memcache->getStats();
}
private function _loadQueries()
{
if (!is_array($this->_queries))
{
$queries = $this->_memcache->get($this->_appKey . '__queries');
if ($queries !== false && is_array($queries))
$this->_queries = $queries;
else
$this->_queries = array();
}
}
public function getQueries()
{
$this->_loadQueries();
return $this->_queries;
}
public function searchQueries($search)
{
$queries = array();
foreach($this->getQueries() as $sql => $timestamp)
{
if (strpos($sql, $search) !== false)
{
$queries[] = $sql;
}
}
return $queries;
}
public function deleteQuery($sql)
{
$this->_memcache->delete($this->_getQueryKey($sql));
$this->_loadQueries();
if (isset($this->_queries[$sql]))
unset($this->_queries[$sql]);
$this->_saveQueries();
}
public function deleteMatching($searchItems)
{
if (!is_array($searchItems))
{
$searchItems = array($searchItems);
}
foreach($searchItems as $searchItem)
{
foreach($this->searchQueries($searchItem) as $query)
$this->deleteQuery($query);
}
}
public function deleteAll()
{
foreach($this->getQueries() as $sql => $timestamp)
{
$this->deleteQuery($sql);
}
}
private function _saveQueries($timeout = 1800)
{
if (is_array($this->_queries))
{
// clean old stuff
foreach($this->_queries as $sql => $timestamp)
{
if (time() - $timestamp > $timeout)
unset($this->_queries[$sql]);
}
return $this->_memcache->set($this->_appKey . '__queries', $this->_queries, 0, $timeout);
}
else
return false;
}
// Gets key / value pair into memcache … called by mysql_query_cache()
private function _getCache($key) {
return ($this->_memcache) ? $this->_memcache->get($key) : false;
}
// Puts key / value pair into memcache … called by mysql_query_cache()
private function _setCache($key, $object, $timeout) {
$this->_loadQueries();
if ($this->_memcache instanceof Memcache)
return $this->_memcache->set($key, $object, 0, $timeout);
else
return false;
}
private function _cacheQuery($sql, $object, $timeout)
{
if ($this->_setCache($this->_getQueryKey($sql), $object, $timeout))
{
$this->_loadQueries();
$this->_queries[$sql] = time();
$this->_saveQueries($timeout);
return true;
}
else
{
return false;
}
}
public function expireQuery($query)
{
$this->_memcache->delete($this->_getQueryKey($query));
}
public function mysql_query($sql, $linkIdentifier = false, $timeout = 600)
{
return $this->mysql_query_cache($sql, $linkIdentifier, $timeout);
}
private function _getQueryKey($sql)
{
return md5($this->_appKey . $sql);
}
// Caching version of mysql_query()
public function mysql_query_cache($sql, $linkIdentifier = false, $timeout = 600 /* = 10 minutes expiration */) {
if (!($cache = $this->_getCache($this->_getQueryKey($sql)))) {
$cache = array();
$r = ($linkIdentifier !== false) ? mysql_query($sql,$linkIdentifier) : mysql_query($sql);
if (is_resource($r) && (($rows = mysql_num_rows($r)) != 0)) {
for ($i=0;$i<$rows;$i++) {
$fields = mysql_num_fields($r);
$row = mysql_fetch_array($r);
for ($j=0;$j<$fields;$j++) {
if ($i == 0) {
$columns[$j] = mysql_field_name($r,$j);
}
$cache[$i][$columns[$j]] = $row[$j];
}
}
if (!$this->_cacheQuery($sql, $cache, $timeout)) {
die('Error trying to connect to memcache');
// If we get here, there isn't a memcache daemon running or responding
}
}
}
return $cache;
}
}
TODO: this code need to get more documentation, and a static method to store the object instance so it can be called everywhere in the application.
