<?php
/*
 * $jwk: ss_mysql_local.php,v 1.1 2007/11/08 22:18:50 jwk Exp $
 *
 * ss_mysql_local.php
 *
 * This is a script server script that gathers statistics from the
 * the local MySQL server.
 *
 *
 *
 * Joel Knight
 * <knight.joel@gmail>
 * 2007.11.08
 */


/* do NOT run this script through a web browser */
if (!isset($_SERVER["argv"][0]) || isset($_SERVER['REQUEST_METHOD'])  || isset($_SERVER['REMOTE_ADDR'])) {
	die("<br><strong>This script is only meant to run at the command line.</strong>");
}

$no_http_headers = true;

error_reporting(0);

if (!isset($called_by_script_server)) {
	include_once(dirname(__FILE__) . "/../include/global.php");

	print call_user_func("ss_mysql_commands");
	print call_user_func("ss_mysql_open");
	print call_user_func("ss_mysql_qcache");
	print call_user_func("ss_mysql_queries");
	print call_user_func("ss_mysql_threads");
	print call_user_func("ss_mysql_traffic");
}

function ss_mysql_commands() {
	$names = array(
		"Com_delete",
		"Com_insert",
		"Com_lock_tables",
		"Com_replace",
		"Com_select",
		"Com_unlock_tables",
		"Com_update",
	);

	return get_stats($names);
}

function ss_mysql_open() {
	$names = array(
		"Open_files",
		"Open_streams",
		"Open_tables",
		"Opened_tables",
	);

	return get_stats($names);
}

function ss_mysql_qcache() {
	$names = array(
		"Com_select", 	/* needed to Qcache_hit_rate */
		"Qcache_free_blocks",
		"Qcache_free_memory",
		"Qcache_hits",
		/* Qcache_hit_rate is calculated in get_stats() */
		"Qcache_inserts",
		"Qcache_lowmem_prunes",
		"Qcache_not_cached",
		"Qcache_queries_in_cache",
		"Qcache_total_blocks",
	);

	return get_stats($names);
}

function ss_mysql_queries() {
	$names = array(
		"Questions",
		/* Queries_per_second is calculated in get_stats() */
		"Slow_queries",
		"Uptime",	/* needed for Queries_per_second */
	);

	return get_stats($names);
}

function ss_mysql_threads() {
	$names = array(
		"Connections",
		"Threads_cached",
		"Threads_connected",
		"Threads_created",
		/* Threads_miss_rate is calculated in get_stats() */
		"Threads_running",
	);

	return get_stats($names);
}

function ss_mysql_traffic() {
	$names = array(
		"Bytes_received",
		"Bytes_sent",
	);

	return get_stats($names);
}

function get_stats($names) {
	/* in 5.0.2, it became necessary to use the GLOBAL keyword in order
	 * to get global server statistics.
	 * http://bugs.mysql.com/bug.php?id=19093
	 */
	$sql = "SHOW /*!50002 GLOBAL */ STATUS WHERE Variable_name IN (" .
		join(",", array_map("quote", $names)) .
		")";
	$results = db_fetch_assoc($sql);

	$stats = array();
	foreach ($results as $row) {
		switch ($row["Variable_name"]) {
			case "Com_select":
				$com_select = $row["Value"];
				break;
			case "Connections":
				$connections = $row["Value"];
				break;
			case "Qcache_hits":
				$qcache_hits = $row["Value"];
				break;
			case "Questions":
				$questions = $row["Value"];
				break;
			case "Threads_created":
				$threads_created = $row["Value"];
				break;
			case "Uptime":
				$uptime = $row["Value"];
				break;
		}
		array_push($stats, sprintf("%s:%s", $row["Variable_name"], $row["Value"]));
	}
	if (isset($questions) && isset($uptime))
		array_push($stats, sprintf("Queries_per_second:%.3f", $questions / $uptime));
	if (isset($connections) && isset($threads_created))
		array_push($stats, sprintf("Threads_miss_rate:%.2f", 
			($threads_created / $connections) * 100));
	if (isset($com_select) && isset($qcache_hits)) 
		array_push($stats, sprintf("Qcache_hit_rate:%.2f",
			($qcache_hits/($qcache_hits+$com_select))*100));

	return join(" ", $stats);
}

function quote($s) {
	return sprintf("'%s'", $s);
}

?>