[aur-dev] [PATCH 3/5] Cache all front page stats in APC if available

[Dan McGee]

Use the APC cache to store all of the counts and the recently updated
package list in a cache, which cuts down on the number of database queries
needed. If the data isn't perfectly up to date we will survive.

Signed-off-by: Dan McGee <dan at archlinux.org>
---
 web/lib/stats.inc                    |   52 +++++++++++++++++++++++-----------
 web/template/stats/updates_table.php |    5 +--
 2 files changed, 37 insertions(+), 20 deletions(-)

diff --git a/web/lib/stats.inc b/web/lib/stats.inc
index 6fbc033..deb246e 100644
--- a/web/lib/stats.inc
+++ b/web/lib/stats.inc
@@ -2,10 +2,38 @@
 
 include_once('aur.inc');
 
+# Check if APC extension is loaded
+if (!defined('EXTENSION_LOADED_APC'))
+	define('EXTENSION_LOADED_APC', extension_loaded('apc'));
+
+# run a simple db query, retrieving and/or caching the value if APC
+# is available for use
+#
+function db_cache_value($dbq, $dbh, $key)
+{
+	if(!(EXTENSION_LOADED_APC && ($ret = apc_fetch($key)))) {
+		$result = db_query($dbq, $dbh);
+		$row = mysql_fetch_row($result);
+		$ret = $row[0];
+		# set the TTL here in seconds: 300 seconds = 5 minutes
+		apc_store($key, $ret, 300);
+	}
+	return $ret;
+}
+
 function updates_table($dbh)
 {
-	$q = 'SELECT * FROM Packages WHERE DummyPkg != 1 ORDER BY GREATEST(SubmittedTS,ModifiedTS) DESC LIMIT 0 , 10';
-	$newest_packages = db_query($q, $dbh);
+	$key = 'aur_recent_updates';
+	if(!(EXTENSION_LOADED_APC && ($newest_packages = apc_fetch($key)))) {
+		$q = 'SELECT * FROM Packages WHERE DummyPkg != 1 ORDER BY GREATEST(SubmittedTS,ModifiedTS) DESC LIMIT 0 , 10';
+		$result = db_query($q, $dbh);
+
+		$newest_packages = new ArrayObject();
+		while ($row = mysql_fetch_assoc($result)) {
+			$newest_packages->append($row);
+		}
+		apc_store($key, $newest_packages, 300);
+	}
 	include('stats/updates_table.php');
 }
 
@@ -42,30 +70,20 @@ function general_stats_table($dbh)
 {
 	# AUR statistics
 	$q = "SELECT count(*) FROM Packages,PackageLocations WHERE Packages.LocationID = PackageLocations.ID AND PackageLocations.Location = 'unsupported'";
-	$result = db_query($q, $dbh);
-	$row = mysql_fetch_row($result);
-	$unsupported_count = $row[0];
+	$unsupported_count = db_cache_value($q, $dbh, 'aur_unsupported_count');
 
 	$q = "SELECT count(*) FROM Packages,PackageLocations WHERE Packages.LocationID = PackageLocations.ID AND PackageLocations.Location = 'community'";
-	$result = db_query($q, $dbh);
-	$row = mysql_fetch_row($result);
-	$community_count = $row[0];
+	$community_count = db_cache_value($q, $dbh, 'aur_community_count');
 
 	$q = "SELECT count(*) from Users";
-	$result = db_query($q, $dbh);
-	$row = mysql_fetch_row($result);
-	$user_count = $row[0];
+	$user_count = db_cache_value($q, $dbh, 'aur_user_count');
 
 	$q = "SELECT count(*) from Users,AccountTypes WHERE Users.AccountTypeID = AccountTypes.ID AND AccountTypes.AccountType = 'Trusted User'";
-	$result = db_query($q, $dbh);
-	$row = mysql_fetch_row($result);
-	$tu_count = $row[0];
+	$tu_count = db_cache_value($q, $dbh, 'aur_tu_count');
 
 	$targstamp = intval(strtotime("-7 days"));
 	$q = "SELECT count(*) from Packages WHERE (Packages.SubmittedTS >= $targstamp OR Packages.ModifiedTS >= $targstamp)";
-	$result = db_query($q, $dbh);
-	$row = mysql_fetch_row($result);
-	$update_count = $row[0];
+	$update_count = db_cache_value($q, $dbh, 'aur_update_count');
 
 	include('stats/general_stats_table.php');
 }
diff --git a/web/template/stats/updates_table.php b/web/template/stats/updates_table.php
index e1eb888..9d1af01 100644
--- a/web/template/stats/updates_table.php
+++ b/web/template/stats/updates_table.php
@@ -6,8 +6,7 @@
 </th>
 </tr>
 
-<?php while ($row = mysql_fetch_assoc($newest_packages)): ?>
-
+<?php foreach ($newest_packages->getIterator() as $row): ?>
 <tr>
 <td class="boxSoft">
 <span class="f4"><span class="blue">
@@ -34,7 +33,7 @@ endif;
 </td>
 </tr>
 
-<?php endwhile; ?>
+<?php endforeach; ?>
 
 </table>
 
-- 
1.6.1