Re: Query meltdown: caching results

Gordon wrote:
> (Sorry for the repost but I thought this would be appropriate to both
> groups. I did tell Google to delete my first post but odds are some
> guys got that copy already anyway)
>
> After a lot of hairpulling, I finally found a mechanism in PHP for
> doing what I wanted. I just had to know 2 things:
>
> 1) How to get the PDO engine to use my customized prepared statement
> class instead of PDOStatement
> 2) Extending PDOStatement to transparently add results caching is too
> difficult and complex in the timeframe required
>
> Once I knew these things I made a PDOStatement extension class that
> instead of trying to transparently add caching to the existing methods
> added a couple of new ones instead. Code below:
>
> <?php
>
> class Statement extends PDOStatement
> {
> private $resultCache = array ();
> private $database = NULL;
> public $hits = 0;
> public $misses = 0;
>
> public function ask (array $params = array ())
> // Executes a prepared statement on the database that fetches
> data
> {
> $hash = md5 (implode (',', $params));
> if (!$this -> resultCache [$hash])
> {
> $this -> misses++;
> // Execute the query
> if ($this -> execute ($params))
> {
> // Cache the results
> $this -> resultCache [$hash] = $this -
>> fetchAll
> (PDO::FETCH_ASSOC);
> }
> }
> else
> {
> $this -> hits++;
> }
> return ($this -> resultCache [$hash]);
> }
> public function tell (array $params = array ())
> // Execute a prepared statement that causes the database to be
> modified
> {
> // Execute the query
> if ($this -> execute ($params))
> {
> $rowCount = $this -> rowCount ();
> if ($rowCount)
> {
> // Tell the parent Database object to
> clear statement caches
> $this -> database -> clearResults ();
> }
> return ($rowCount);
> }
> }
> public function clearResults ()
> // Clear cache
> {
> $this -> resultCache = array ();
> }
> private function __construct ($db)
> // Class constructor
> {
> $this -> database = $db;
> //print_r ($this);
> }
>
> }
>
> class Database extends PDO
> // Adds some extra functionality to the built in PDO class
> {
> private $statementCache = array ();
> private $txCount = 0;
> private $txErr = false;
>
> // Prepared statement cacheing
> public function prepare ($statement, array $options = array
> ())
> {
> $hash = md5 ($statement);
> if ((!isset ($this -> statementCache [$hash]))
> || (!is_object ($this -> statementCache [$hash])))
> {
> //echo ('Preparing statement "'.
> $statement .'"<br>');
> $this -> statementCache [$hash] =
> parent::prepare ($statement,
> $options);
> }
> else
> {
> //echo ('Statement "' . $statement . '"
> already prepared<br>');
> }
> return ($this -> statementCache [$hash]);
> }
> public function clearResults ()
> // Clear the results cache of all associated prepared
> statements
> {
> foreach ($this -> statementCache as $thisStatement)
> {
> $thisStatement -> clearResults ();
> }
> }
> // Add support for transaction nesting
> public function beginTransaction ()
> {
> if (($this -> txCount == 0) && (!$this -> txErr))
> {
> $result = parent::beginTransaction ();
> }
> $this -> txCount ++;
> if (DEBUG_TX)
> {
> echo ('begin: ' . $this -> txCount . '
> transaction(s)<br />');
> }
> return ($result);
> }
> public function commit ()
> {
> $this -> txCount --;
> if ($this -> txCount <= 0)
> {
> $this -> txErr? $result =
> parent::rollback (): $result =
> parent::commit ();
> $this -> txErr = false;
> }
> if (DEBUG_TX)
> {
> echo ('commit: ' . $this -> txCount . '
> transaction(s)<br />');
> }
> return ($result);
> }
> public function rollback ()
> {
> $this -> txErr = true;
> $this -> txCount --;
> if ($this -> txCount <= 0)
> {
> $result = parent::rollback ();
> $this -> txErr = false;
> }
> if (DEBUG_TX)
> {
> echo ('rollback: ' . $this -> txCount . '
> transaction(s)<br />');
> }
> return ($result);
> }
> // Housekeeping
> private function removeExpiredLocks ()
> {
> $query = 'DELETE FROM cms_locks
> WHERE lck_timestamp +
> lck_duration < NOW();';
> $preparedQuery = $this -> prepare ($query);
> if ($preparedQuery -> execute ())
> {
> return ($preparedQuery -> rowCount ());
> }
> }
> // Class constructor
> public function __construct ()
> {
> parent::__construct ( 'pgsql:host=' . CFG_DB_HOST
> .'
> port=' . CFG_DB_PORT
> .'
> dbname=' . CFG_DB_DBNAME,
>
> CFG_DB_USERNAME,
>
> CFG_DB_PASSWORD);
> /*
> The documentation for the following line on php.net is
> really bad!
>
> What is does is set what class will be used to handle
> prepared
> statements. By default
> the PDO -> prepare() command returns a prepared
> statement as a
> PDOStatement class object.
> We want to extend PDOStatements to provide some extra
> functionality,
> so when we run the
> prepare () method, we want to return something other
> than a
> PDOStatement.
>
> This line tells the database to use our Statement
> class for prepared
> statements instead
> of the PDOStatement class. It also passes a reference
> to the
> database object that
> spawned it to the constructor as an argument. We'll
> use this
> reference in the Statement
> class to send messages back to the Database class
> */
> $this -> setAttribute (PDO::ATTR_STATEMENT_CLASS,
> array
> ('Statement', array ($this)));
> $this -> setAttribute (PDO::ATTR_ERRMODE,
> PDO::ERRMODE_WARNING);
> $this -> query ('SET search_path = ' . CFG_DB_PATH .
> ';');
> $this -> removeExpiredLocks ();
> }
>
> }
>
> The system works like this: I use my Database class where I would
> have used PDO to create a database connection. When I prepare() a
> statement I either get a new prepared statement back, or the
> previously created one is returned. The prepared statement contains
> an array that caches the results produced with various parameters. My
> prepared statement class also adds 2 database querying methods, ask ()
> and tell (), that I use where I normally would have used an execute
> (). If I want to retrieve information from the database I ask () it
> to return the data matching my parameters. Whenever I want to make a
> change I tell () it the data I want saved to the database. The ask ()
> method checks the statement's local cache of results and if it finds
> one matching the passed parameters, it returns it. If not, then it
> execute()s the query to retrieve them.
>
> When I tell () the database to make a change the statement that
> received the message to do so sends a message to its parent Database
> object. The object goes through its list of cached prepared
> statements and sends them messages telling them to dump the contents
> of their caches.
>
> It's not an ideal solution, if two different queries produce identical
> result sets or ones that overlap in some way then they get cached
> twice, and the cache invalidation mechanism is extremely primitive (if
> any change is made, dump everything). Nonetheless this little caching
> system has had quite a dramatic effect on performance. It's still not
> as fast as I would like though. :)
>
> I reposted the code here in the hope that somebody finds it useful,
> and/or in case somebody has suggestions for improvement. While the
> code itself was quite simple in the end, finding the information
> needed to make it work wasn't. The ATTR_STATEMENT_CLASS parameter is
> not well documented. Hopefully others can learn from my difficulties
> here and learn from them.

Gordon,

Thought you might be looking to cache the completed pages (as they
are requested). I would have thought something like APC would fit the bill:

$key = $_SERVER['HTTP_REFERER'];
if ($cached_html = apc_fetch($key)
{
echo $cached_html;
exit;
}

<rest of your code>
...when you output a page down here you store it like:

echo $html; // your output
apc_store($key,$html,300); // 300 secs = 5 mins
exit;

This way you never even touch a query unless the time limit is up. If
a page is changed, all you need to do is:

apc_delete($key);

...which will automatically re-cache the new page (due to the logic
flow) on the next request.

--
Norman
Registered Linux user #461062