I have som rather large and time consuming queries running in the Statistics screen of an NOC (Network Operations Center) Flex/Flash Builder 4 application i’we been tinkering with, to prevent the database server to be boggen down by multiple queries fired by this app in multiple places I had to implement caching.
And to do this is alot easier than it might sound like especially for the (PHP) Zend_AMF based services.
Once you have setup your Data Centric client/server connection like it’s described in this Article @ DevZone you begin by editing the gateway.php;
// Store configuration in the registry
Zend_Registry::set("amf-config", $amf);
// Configure Zend_Cache
$frontendOptions = array(
'lifetime' => 12*3600,
'automatic_serialization' => true,
'default_options' => array(
'cache_with_get_variables' => true,
'cache_with_post_variables' => true,
'cache_with_session_variables' => true,
'cache_with_files_variables' => true,
'cache_with_cookie_variables' => true,
'make_id_with_get_variables' => true,
'make_id_with_post_variables' => true,
'make_id_with_session_variables' => true,
'make_id_with_files_variables' => true,
'make_id_with_cookie_variables' => true
)
);
$backendOptions = array(
'cache_dir' => '/tmp/'
);
$cache = Zend_Cache::factory('Core', 'File', $frontendOptions, $backendOptions);
// Store cache configuration in the registry
Zend_Registry::set("cache", $cache);
Then modify your service class to look similar to this;
public function getCustomerStatsByMonth() {
// Get the Cache from Registry
$cache = Zend_Registry::get("cache");
$id = 'getCustomerStatsByMonth';
if(!($rows = $cache->load($id)))
{
// We didnt find anything in the cache so lets get it from DB
$stmt = mysqli_prepare($this->connection, "SELECT
customers.name,
SUBSTR(FROM_UNIXTIME(`cdrs`.`start`),1,7) AS `month`,
outgroups.name_invoices,
ROUND(SUM(`cdrs`.`talktime`)/60) AS `minutes`,
COUNT(`cdrs`.`start`) AS `calls`
FROM
es.outgroups
INNER JOIN es.cdrs
ON (outgroups.id = cdrs.outgroup)
INNER JOIN es.customers
ON (customers.id = cdrs.scustomer)
WHERE (outgroups.name_invoices LIKE 'Sweden%' AND cdrs.start > UNIX_TIMESTAMP('2010-01-01 00:00:00') AND cdrs.status = 'answer' AND cdrs.talktime > 0 AND custome\
rs.parent IN (7,42))
GROUP BY customers.id,SUBSTR(FROM_UNIXTIME(`cdrs`.`start`),1,7),outgroups.name_invoices
ORDER BY SUBSTR(FROM_UNIXTIME(`cdrs`.`start`),1,7),outgroups.name_invoices;");
$this->throwExceptionOnError();
mysqli_stmt_execute($stmt);
$this->throwExceptionOnError();
$rows = array();
mysqli_stmt_bind_result($stmt, $row->name, $row->month, $row->name_invoices, $row->minutes, $row->calls);
while (mysqli_stmt_fetch($stmt)) {
$row->month = new DateTime($row->month.'-01 00:00:00');
$rows[] = $row;
$row = new stdClass();
mysqli_stmt_bind_result($stmt, $row->name, $row->month, $row->name_invoices, $row->minutes, $row->calls);
}
mysqli_stmt_free_result($stmt);
mysqli_close($this->connection);
// Save collected rows into the cache
$cache->save($rows,$id,array('customer_stats'),3*3600);
}
return $rows;
}
Your queries will now be cached after the first time you run them for the specified amount of time (TTL), and boom you’re done. If you require the update function in your service classes to purge the cache simply insert;
// To remove or invalidate in particular cache id, you can use the remove() method :
$cache->remove('idToRemove');
Read more about cache cleaning in the Zend Cache reference.
Hope this little article helps and please comment if you guys have better suggestions.
(Yes I know I can use APC & Memcached, but in this example I didnt have to 🙂 )
Regards
Danny Froberg
