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. This version of the patch will also cache the relevant counts for individual logged-in users and is more careful about checking whether the value actually exists in the cache by using the status reference to apc_fetch(). Signed-off-by: Dan McGee <dan@archlinux.org> --- web/lib/stats.inc | 85 ++++++++++++++++++++++------------ web/template/stats/updates_table.php | 5 +- 2 files changed, 58 insertions(+), 32 deletions(-) diff --git a/web/lib/stats.inc b/web/lib/stats.inc index 6fbc033..986d9df 100644 --- a/web/lib/stats.inc +++ b/web/lib/stats.inc @@ -2,37 +2,73 @@ include_once('aur.inc'); +# Check if APC extension is loaded +if (!defined('EXTENSION_LOADED_APC')) + define('EXTENSION_LOADED_APC', extension_loaded('apc')); +$apc_prefix = 'aur:'; + +# run a simple db query, retrieving and/or caching the value if APC +# is available for use +# +function db_cache_value($dbq, $dbh, $key) +{ + $bool = false; + if(EXTENSION_LOADED_APC) { + $ret = apc_fetch($key, $bool); + } + if(!$bool) { + $result = db_query($dbq, $dbh); + $row = mysql_fetch_row($result); + $ret = $row[0]; + if(EXTENSION_LOADED_APC) { + # 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); + global $apc_prefix; + $key = $apc_prefix . '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); + } + if(EXTENSION_LOADED_APC) { + apc_store($key, $newest_packages, 300); + } + } include('stats/updates_table.php'); } function user_table($user, $dbh) { - + global $apc_prefix; + $escuser = mysql_real_escape_string($user); $base_q = 'SELECT count(*) FROM Packages,PackageLocations,Users WHERE Packages.MaintainerUID = Users.ID AND Packages.LocationID = PackageLocations.ID AND PackageLocations.Location = "%s" AND Users.Username="' . - mysql_real_escape_string($user).'"'; + $escuser.'"'; - $result = db_query(sprintf($base_q, 'unsupported'), $dbh); - $row = mysql_fetch_row($result); - $maintainer_unsupported_count = $row[0]; + $maintainer_unsupported_count = db_cache_value(sprintf($base_q, 'unsupported'), $dbh, + $apc_prefix . 'user_unsupported_count:' . $escuser); $q = "SELECT count(*) FROM Packages,Users WHERE Packages.OutOfDate = 1 AND Packages.MaintainerUID = Users.ID AND Users.Username='" . - mysql_real_escape_string($user)."'"; + $escuser."'"; - $result = db_query($q, $dbh); - $row = mysql_fetch_row($result); - $flagged_outdated = $row[0]; + $flagged_outdated = db_cache_value($q, $dbh, + $apc_prefix . 'user_flagged_outdated:' . $escuser); # If the user is a TU calculate the number of the packages $atype = account_from_sid($_COOKIE["AURSID"]); if (($atype == 'Trusted User') || ($atype == 'Developer')) { - $result = db_query(sprintf($base_q, 'community'), $dbh); - $row = mysql_fetch_row($result); - $maintainer_community_count = $row[0]; + $maintainer_community_count = db_cache_value(sprintf($base_q, 'community'), $dbh, + $apc_prefix . 'user_community_count:' . $escuser); } include('stats/user_table.php'); @@ -40,32 +76,23 @@ function user_table($user, $dbh) function general_stats_table($dbh) { + global $apc_prefix; # 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, $apc_prefix . '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, $apc_prefix . '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, $apc_prefix . '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, $apc_prefix . '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, $apc_prefix . '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