Bastiaans Blog

Recently I switched from mod_php to FastCGI for serving PHP websites. With all the advantages of being able to run all websites separatly, the drawback is that configuration options like php_value and php_flag in .htaccess or other Apache configurations do not work anymore. There are some hacks that can be loaded as a PHP Module so PHP itself will read .htaccess files, but none of them work quite well. So the other solution is to use ini_set() in a global PHP include file in the very beginning of the PHP application.

Zend Framework applications are quite easy as all request are forwarded to index.php handling all request, so all global settings like include_paths can be handled there. However, with some PHP websites it is quite hard to introduce a common global PHP include file for application that has no single PHP file the application starts with, and it depends on a include_path set with php_value. The solution is to use Apache mod_rewrite to redirect all php request to a single file, proxy.php, that sets the global settings, and runs the original PHP file requested. (more…)

When I was measuring execution time in a new PHP application using Doctrine and Zend_Date, I discovered lots of time is consumed by the code. It will slow down the app really if you show 50 objects in a list on a page when every object uses 100ms to list the results. It’s hard to get that library code fast, it’s easier to write a cache class around the object.
(more…)

Today I rewrote the previous mysql - memcache classes, mentioned in my previous post.
I wrote a clas with static methods (why should you need an object at all?) with logic. supporting an easy way store items in a PHP application. A second DB class extends this one, for an easy way of caching complicated queries.
(more…)

Today I discovered a problem with my PHP/MySQL application. SQL data coming from the Zend_Db logic has the wrong encoding.

Like IMO all modern applications should, my application only uses UTF-8 for displaying and handling all non-ASCII characters. To make the MySQL server understand that I talk UTF-8 to it, I always use the following immediately after connecting:
(more…)

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.