Sstrutt: Initial creation

2011-12-19T10:16:41Z

Initial creation

New page

<source lang="php">

<html xmlns='http://www.w3.org/TR/REC-html40'>



<html>
<head>
<title>vi-db (cap-man)</title>
<link rel='SHORTCUT ICON' href='favicon.ico'>
<link rel='stylesheet' type='text/css' href='alpha.css' />
<meta name='Author' content='Simon Strutt'>
<meta name='Description' content='VI Info'>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" >
<meta http-equiv="Content-Type" content="image/jpeg" >
<meta http-equiv='Pragma' content='no-cache'>
<meta http-equiv='Expires' content='900'>
</head>
<body>

<?php
$time_start = microtime(true);

//Local variables etc
require 'config.php';

// Thresholds (get from config.php where available)
$cpu_pc = $cpu_pc_alert;
$vcpu_pcpu = 4; // To be derived from config.php
$mem_pc = $mem_pc_alert;
$clust_n = 1; // To be derived from config.php
$disk_pc = $disk_pc_alert;
$disk_unit = 40; // To be derived from config.php

// open connection and select database
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect to database!");
mysql_select_db($db) or die ("Unable to select database!");

//Set debug on if required
if (array_key_exists('debug', $_REQUEST) and ($_REQUEST["debug"] == 1)) {
$debug = 1;
}

// show parameters and errors
if ($debug) {
error_reporting(-1);
print_r($_REQUEST);
print "<br>";
}

// Update thresholds if defined...
if (array_key_exists('thres_vcpu', $_REQUEST)) {
$vcpu_pcpu = $_REQUEST['thres_vcpu'];
}
if (array_key_exists('thres_mem', $_REQUEST)) {
$mem_pc = $_REQUEST['thres_mem'];
}
if (array_key_exists('thres_n', $_REQUEST)) {
$clust_n = $_REQUEST['thres_n'];
}
if (array_key_exists('thres_disk', $_REQUEST)) {
$disk_pc = $_REQUEST['thres_disk'];
}
if (array_key_exists('thres_dunit', $_REQUEST)) {
$disk_unit = $_REQUEST['thres_dunit'];
}

//print "<div title='body'><div title='disclaimer'>";
print "DISCLAIMER: Please use all information gained from here with caution - I haven't performed exhaustive validation and testing of any of the information that may be presented. <br>\n";
print "If you're making changes or decisions, please double check with the reality of what is displayed by the vCentre servers. You retain full responsibility for your actions...!\n ";
print "\n";
//print "</div>\n";

// ------------------- Layout table cell ----------------------
print "<table align='center' cellspacing=10><tr><td valign='top' colspan=2>\n";
//print "<div title='cluster overview'>\n";

print "<h1>Cluster Capacity</h1>\n";

$query = "SELECT vc.vcid, vc.name AS vc, cluster.clid, cluster.name AS cluster, COUNT(esxid) AS esxs, SUM(cpu_core) AS cpu_core, SUM(cpu_pc) AS cpu_pc_sum, SUM(mem)/1024 AS mem, ";
$query .= "SUM(mem_pc) AS mem_pc_sum FROM esx JOIN esx_state USING (esxid) JOIN vc USING (vcid) JOIN cluster USING (clid) WHERE esx.exist=1 GROUP BY clid ORDER BY vc, cluster.name;";

$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
die ("No clusters found in db!!! Unexpected error!");
}

print "<table class='BasicTable' align='center' valign='top'>";
print "<tr><th rowspan=2>vCentre<th rowspan=2>Cluster<th rowspan=2>ESXs<th colspan=3 class='section'>CPU<th colspan=2>Memory<th colspan=3>Disk<th colspan=4>Free Capacity<br><small>(using redundancy)</small><th colspan=4 class='black_text'>Spare Capacity<br><small>(retaining failover capacity)</small>\n";
// CPU
print "<tr><th>Total<br>Cores<th><small>vCPU/<br>pCPU</small><th>%<br><small>load</small>";
// Memory
print "<th>Total<br><small>GB</small><th>%<br><small>load</small>";
// Disk
print "<th><small>No of<small><br>LUNs<th>Total<br><small>TB</small><th>%<br><small>used</small>";
// Free Capacity
print "<th>CPU<br><small>cores</small><th>Mem<br><small>GB</small><th>Disk<br><small>GB</small><th>Disk<br><small>+VM off</small>";
// Spare Capacity
print "<th class='black_text'>CPU<br><small>cores</small><th class='black_text'>Mem<br><small>GB</small><th class='black_text'>Disk<br><small>GB</small><th class='black_text'>Disk<br><small>+VM off</small>\n";

$odd_row = true;
$cl_list = array();
while ($cluster = mysql_fetch_assoc($result)) {
$cl_list[$cluster['clid']]=$cluster['cluster'];
$odd_row = !$odd_row;
if ($odd_row) {
print "<tr class='odd'>";
} else {
print "<tr>";
}
$vm = mysql_fetch_assoc(mysql_query("SELECT COUNT(vmid) AS vms, SUM(cpu) AS cpus, SUM(mem)/1024 AS mem FROM vm JOIN vm_ext USING (vmid) WHERE clid=".$cluster['clid']." AND is_on=1 AND exist=1;"));
$esx_core = mysql_result(mysql_query("SELECT MAX(cpu_core) FROM esx WHERE clid=". $cluster['clid'] .";"), 0);
$esx_mem = mysql_result(mysql_query("SELECT MAX(mem)/1024 FROM esx WHERE clid=". $cluster['clid'] .";"), 0);
$ds_space_vm_off = mysql_result(mysql_query("SELECT SUM(vmdk.size)/1024 AS size FROM vm JOIN vc USING (vcid) JOIN cluster USING (clid) JOIN vmdk USING (vmid) WHERE vm.exist=1 AND is_on=0 AND clid=". $cluster['clid'] .";"), 0);
$ds = mysql_fetch_assoc(mysql_query("SELECT COUNT(lun) AS luns, SUM(size)/1024 AS size, SUM(used)/1024 AS used FROM (SELECT DISTINCT lun AS lun, size AS size, used AS used FROM ds JOIN esx2ds USING (dsid) JOIN esx USING (esxid) WHERE ds.exist=1 AND esx.exist=1 AND clid=".$cluster['clid']." AND ds.name NOT LIKE('%Local%') AND ds.name NOT LIKE('%iSCSI%')) AS ds;"));
print "<td><a href='vc-detail.php?vcid=".$cluster['vcid']."&clid=".$cluster['clid']."'>".$cluster['vc']."</a><td><a href='cluster-detail.php?clid=".$cluster['clid']."'><b>".$cluster['cluster']."</b></a><td class='num'><a href='esxs.php?clid=".$cluster['clid']."'>".$cluster['esxs']."</a>";
// CPU
print "<td class='num'>".$cluster['cpu_core']."<td class='num'>".round(($vm['cpus']/$cluster['cpu_core']),1)."<td class='num'>".round($cluster['cpu_pc_sum']/$cluster['esxs']);
// Memory
print "<td class='num'>".number_format(round($cluster['mem']))."<td class='num'>".round($cluster['mem_pc_sum']/$cluster['esxs']);
// Disk
print "<td class='num'>".$ds['luns']."<td class='num'>".number_format(round($ds['size']/1024),1)."<td class='num'>".round($ds['used']/$ds['size']*100);
// Free Capacity (CPU + Mem)
print "<td class='num'>".($cluster['cpu_core']*$vcpu_pcpu-$vm['cpus'])."<td class='num'>".number_format(round(($cluster['mem']-$vm['mem'])));
// Free Capacity (Disk)
print "<td class='num'>".number_format((round($ds['size']-$ds['used'])))."<td class='num'>".number_format((round($ds['size']-$ds['used']+$ds_space_vm_off)));
// Spare Capacity (CPU + Mem)
$cpu_spare = ($cluster['cpu_core']-$esx_core*$clust_n)*$vcpu_pcpu-$vm['cpus'];
if ($cpu_spare < 1) {
print "<td class='num'><b><span class='warn'>$cpu_spare</span></b>";
} else {
print "<td class='num'><b>$cpu_spare</b>";
}
$mem_spare = ($cluster['mem']-$esx_mem*$clust_n)-$vm['mem'];
if ($mem_spare < 1) {
print "<td class='num'><b><span class='warn'>".number_format(round($mem_spare))."</span></b>";
} else {
print "<td class='num'><b>".number_format(round($mem_spare))."</b>";
}
//Calculate useable storage space free
$luns_result = mysql_query("SELECT lun, size/1024 AS size, used/1024 AS used FROM ds JOIN esx2ds USING (dsid) JOIN esx USING (esxid) WHERE ds.exist=1 AND esx.exist=1 AND clid=".$cluster['clid']." AND ds.name NOT LIKE('%Local%') AND ds.name NOT LIKE('%iSCSI%') GROUP BY lun;");
$ds_space = 0;
$title = "";
while ($ds = mysql_fetch_assoc($luns_result)) {
$space = $ds['size']*($disk_pc/100)-$ds['used'];
$ds_space += floor($space/$disk_unit)*$disk_unit;
$title .= "LUN ".$ds['lun'].": $space ($ds_space) | ";
}
if ($ds_space < 1) {
print "<td class='num' title='$title'><b><span class='warn'>".number_format($ds_space)."</span></b>";
} else {
print "<td class='num' title='$title'><b>".number_format($ds_space)."</b>";
}
if (($ds_space+$ds_space_vm_off) < 1) {
print "<td class='num'><b><span class='warn'>".number_format(round($ds_space+$ds_space_vm_off,-1))."</span></b>\n";
} else {
print "<td class='num'><b>".number_format(round($ds_space+$ds_space_vm_off,-1))."</b>\n";
}

if (array_key_exists('req', $_REQUEST) and $_REQUEST["req"]=="do" and $_REQUEST["req_clust"]==$cluster['clid']) {
$cl_test = array();
$cl_test['vcpu'] = $cpu_spare;
$cl_test['mem'] = $mem_spare;
$cl_test['disk'] = $ds_space;
$cl_test['disk_off'] = $ds_space+$ds_space_vm_off;
}
}
print "</table>\n";

//print "</div>\n";
// ------------------- Layout table cell ----------------------
print "\n<tr><td valign='top'>\n";
//print "<div title='thresh' style='float:left;'>\n";

print "<h2>Thresholds</h2>\n";

print "<table class='BasicTable' align='left' valign='top'>\n";
print "<tr><th>Mertic<th>Value<th>Description\n";
//print "<tr><th>CPU %<td>".$cpu_pc."<td>Maximum CPU usage for an ESX in a cluster (suggested - $cpu_pc_alert %)\n";
print "<tr><form><th>vCPU/pCPU<td><input type='text' name='thres_vcpu' size=2 maxlength=2 style='text-align: right' value=$vcpu_pcpu><td>Maximum no of VM CPUs per ESX CPU core (suggested - 4)\n";
print "<tr><th>Mem %<td><input type='text' name='thres_mem' size=2 maxlength=3 style='text-align: right' value=$mem_pc><td>Maximum memory usage for an ESX in a cluster (suggested - $mem_pc_alert %)\n";
print "<tr><th>N +<td><input type='text' name='thres_n' size=2 maxlength=1 style='text-align: right' value=$clust_n><td>Minimum ESX redundancy for a cluster (suggested - 1)\n";
print "<tr><th>Disk %<td><input type='text' name='thres_disk' size=2 maxlength=3 style='text-align: right' value=$disk_pc><td>Maximum space usage for a datastore (suggested - $disk_pc_alert %)\n";
print "<tr><th>Disk Unit<td><input type='text' name='thres_dunit' size=2 maxlength=3 style='text-align: right' value=$disk_unit><td>Unit size for a VM's disk free space calculation (suggested - 40)\n";
print "<tr><td colspan=3 align='center'><button type='submit' name='thres' value='update'>Update</button>\n";
print "</table>\n";
//print "</div>";

// ------------------- Layout table cell ----------------------
print "\n<td align='left' valign='top'>\n";
//print "<div style='width:1200px;'>\n";
//print "<div title='reqs' style='float:right; width:600px'>\n";

print "<h2>Requirements</h2>\n";
print "<table align='left' valign='top'><tr><td>\n";
print "<table class='ReqTable' align='left' valign='top'>\n";
print "<tr><td>vCPUs<td>Mem (GB)<td>Disk (GB)\n";
print "<tr><td><input type='text' name='req_vcpu' size=3 maxlength=3 style='text-align: right'";
if (array_key_exists('req_vcpu', $_REQUEST)) {
print " value='".$_REQUEST["req_vcpu"]."'";
}
print ">\n<td><input type='text' name='req_mem' size=3 maxlength=4 style='text-align: right'";
if (array_key_exists('req_mem', $_REQUEST)) {
print " value='".$_REQUEST["req_mem"]."'";
}
print ">\n<td><input type='text' name='req_disk' size=3 maxlength=4 style='text-align: right'";
if (array_key_exists('req_disk', $_REQUEST)) {
print " value='".$_REQUEST["req_disk"]."'";
}
print ">\n<tr><td colspan=3>Destination Cluster<tr><td colspan=3><select name='req_clust'>";
foreach ($cl_list as $clid => $cl_name) {
print "<option value=$clid";
if (array_key_exists('req_clust', $_REQUEST) and $_REQUEST["req_clust"]==$clid) {
print " selected='selected'";
}
print ">$cl_name</option>\n";
}
print "</select>";

print "<tr><td colspan=3 align='center'><button type='submit' name='req' value='do'>Calculate</button></form></table>\n";

if (array_key_exists('req', $_REQUEST) and $_REQUEST["req"]=="do") {
print "<tr><td>";
// Sanity check params
if (!is_numeric($_REQUEST["req_vcpu"])) {
print "<p><span class='req_alert'>vCPUs requirement is not an integer?</p></table>\n";
} elseif (!is_numeric($_REQUEST["req_mem"])) {
print "<p><span class='req_alert'>Memory requirement is not an integer?</p></table>\n";
} elseif (!is_numeric($_REQUEST["req_disk"])) {
print "<<p><span class='req_alert'>Disk Space requirement is not an integer?</p></table>\n";
} else {

// Sanity check passed - do the biz...
if ($_REQUEST["req_vcpu"] > $cl_test['vcpu'] or $_REQUEST["req_mem"] > $cl_test['mem']) {
// Don't fit
print "<p><span class='req_alert'>  Insufficient CPU/Memory capacity !!  </p>\n";
} elseif ($_REQUEST["req_disk"] > $cl_test['disk']) {
if ($_REQUEST["req_disk"] < $cl_test['disk_off']) {
// Only fits if powered off VM;s deleted
print "<p><span class='req_warn'>  Disk capacity short - powered off VM's need to be deleted !  </p>\n";
} else {
// Just don't fit
print "<p><span class='req_alert'>  No disk capacity !!  </p>\n";
}
} else {
// Fits
print "<p><span class='req_good'>  Sufficient capacity!  </span></p>\n";
}

print "<p>Residual capacity post-deployment...</p><ul>\n";
print "<li>".($cl_test['vcpu']-$_REQUEST['req_vcpu'])." vCPUs</li>\n";
print "<li>".(number_format(round($cl_test['mem']-$_REQUEST['req_mem'])))." GB Memory</li>\n";
print "<li>".($cl_test['disk']-$_REQUEST['req_disk'])." GB Disk Space (".number_format(round(($cl_test['disk_off']-$_REQUEST['req_disk']),-1))." GB if <a href='ds-waste.php?clid=".$_REQUEST['req_clust']."'>powered off VM's</a> deleted)</li></ul></table>\n";
}
} else {
print "</table>";
}

//print "</div>\n";

// ------------------- Layout table cell ----------------------
print "\n<tr><td valign='top' colspan=2>\n";
//print "<div id='centered'>\n";
//print "<div title='notes'>\n";

print "<h2>Notes</h2>\n";
print "<p><ul><li>Where clusters have heterogeneous ESX's, the worst case is assumed for <i>N+</i> calculations.</li>";
print "<li>Negative values are shown where clusters do not have a spare ESX’s worth of capacity, or storage with less than $disk_pc% free space.</li>";
print "<li>Spare disk is calculated in $disk_unit GB blocks so that small, unusable bits of space don’t contribute to the end disk capacity value.</li>\n";
print "<li>iSCSI and local disk datastores are excluded.</li>\n";
print "<li>No account is made for datastores that are attached to multiple clusters - the capacity will appear in all clusters!</li>\n";
print "<li>For a list of powered off VM's that could be deleted see <a href='ds-waste.php'>here</a></li>\n";
print "</ul></p>";

//print "</div>\n</div>";
// ------------------- Layout table cell ----------------------
print "\n</table>\n";

// ------------------- Footer---------------------------------
//print "<div title='footer'>\n";
print "<br><table class='NavTable' align='center' valign='top'><th>Main Pages:<td><a href='index.php'>Search page</a><td><a href='status.php'>Status page</a></table><br>\n";

$time = round((microtime(true) - $time_start)*1000);
print "Page took $time msecs to execute on server\n";
//print "</div>\n</div>\n";

print "</body><html>";
?>

</source>

[[Category:PHP Script]]

VI-DB capman.php v2.1 - Revision history

Sstrutt: Initial creation