public function configImprovementAction()
{
$db = $this->container->getDBUtils();
$this->buildFilters();
$this->buildGroupFilters();
$whereClause = $this->filters->getWhereClause();
$model_html = '';
$model_info = $db->get_rows("SELECT id_learner, model, algorithm, dataslice FROM aloja_ml.learners");
foreach ($model_info as $row) {
$model_html = $model_html . "<li><b>" . $row['id_learner'] . "</b> => " . $row['algorithm'] . " : " . $row['model'] . " : " . $row['dataslice'] . "</li>";
}
$rows_config = '';
try {
$concat_config = Utils::getConfig($this->filters->getGroupFilters());
$filter_execs = DBUtils::getFilterExecs();
$order_conf = 'LENGTH(conf), conf';
$params = $this->filters->getFiltersSelectedChoices(array('prediction_model', 'upred', 'uobsr'));
$whereClauseML = str_replace("exe_time", "pred_time", $whereClause);
$whereClauseML = str_replace("start_time", "creation_time", $whereClauseML);
$query = "SELECT COUNT(*) AS num, CONCAT({$concat_config}) conf\n\t\t\t\t\tFROM aloja2.execs AS e JOIN aloja2.clusters AS c USING (id_cluster)\n\t\t\t\t\tLEFT JOIN aloja_ml.predictions p USING (id_exec)\n\t\t\t\t\tWHERE 1 {$filter_execs} {$whereClause}\n\t\t\t\t\tGROUP BY conf ORDER BY {$order_conf}";
$queryPredicted = "SELECT COUNT(*) AS num, CONCAT({$concat_config}) conf\n\t\t\t\t\tFROM aloja_ml.predictions AS e\n\t\t\t\t\tJOIN clusters c USING (id_cluster)\n\t\t\t\t\tWHERE 1 {$filter_execs} " . str_replace("p.", "e.", $whereClauseML) . " AND e.id_learner = '" . $params['prediction_model'] . "'\n\t\t\t\t\tGROUP BY conf ORDER BY {$order_conf}";
//get configs first (categories)
if ($params['uobsr'] == 1 && $params['upred'] == 1) {
$query = "\n\t\t\t\tSELECT SUM(u1.num) AS num, u1.conf as conf\n\t\t\t\tFROM (\n\t\t\t\t\t({$query})\n\t\t\t\t\tUNION\n\t\t\t\t\t({$queryPredicted})\n\t\t\t\t) AS u1\n\t\t\t\tGROUP BY conf ORDER BY {$order_conf}\n\t\t\t";
} else {
if ($params['uobsr'] == 0 && $params['upred'] == 1) {
$query = $queryPredicted;
}
}
$rows_config = $db->get_rows($query);
$height = 600;
if (count($rows_config) > 4) {
$num_configs = count($rows_config);
$height = round($height + 10 * ($num_configs - 4));
}
$query = "SELECT e.id_exec,\n\t\t\t concat({$concat_config}) conf, e.bench as bench,\n\t\t\t avg(e.exe_time) AVG_exe_time,\n\t\t\t min(e.exe_time) MIN_exe_time,\n\t\t\t (select AVG(exe_time) FROM aloja2.execs WHERE bench = e.bench {$whereClause}) AVG_ALL_exe_time,\n\t\t\t 'none'\n\t\t\t from aloja2.execs e JOIN aloja2.clusters c USING (id_cluster)\n\t\t\t LEFT JOIN aloja_ml.predictions AS p USING (id_exec)\n\t\t\t WHERE 1 {$filter_execs} {$whereClause}\n\t\t\t GROUP BY conf, e.bench order by e.bench, {$order_conf}";
$queryPredicted = "\n\t\t\t\tSELECT e.id_exec, CONCAT({$concat_config}) conf, CONCAT('pred_',e.bench) as bench, AVG(e.pred_time) AVG_exe_time, min(e.pred_time) MIN_exe_time,\n\t\t\t\t(\n\t\t\t\t\tSELECT AVG(p.pred_time)\n\t\t\t\t\tFROM aloja_ml.predictions p\n\t\t\t\t\tWHERE p.bench = e.bench " . str_replace("e.", "p.", $whereClauseML) . " AND p.id_learner = '" . $params['prediction_model'] . "'\n\t\t\t\t) AVG_ALL_exe_time, 'none'\n\t\t\t\tFROM aloja_ml.predictions AS e\n\t\t\t\tJOIN clusters c USING (id_cluster)\n\t\t\t\tWHERE 1 {$filter_execs} " . str_replace("p.", "e.", $whereClauseML) . " AND e.id_learner = '" . $params['prediction_model'] . "'\n\t\t\t\tGROUP BY conf, e.bench ORDER BY e.bench, {$order_conf}\n\t\t\t";
//get the result rows
if ($params['uobsr'] == 1 && $params['upred'] == 1) {
$query = "SELECT j.id_exec, j.conf, j.bench, j.AVG_exe_time, j.MIN_exe_time, j.AVG_ALL_exe_time, 'none' FROM (\n\t\t\t\t\t({$query}) UNION ({$queryPredicted})) AS j\n\t\t\t\tGROUP BY j.conf, j.bench ORDER BY j.bench, {$order_conf}\n\t\t\t";
} else {
if ($params['uobsr'] == 0 && $params['upred'] == 1) {
$query = $queryPredicted;
} else {
if ($params['uobsr'] == 0 && $params['upred'] == 0) {
$this->container->getTwig()->addGlobal('message', "Warning: No data selected (Predictions|Observations) from the ML Filters. Adding the Observed executions to the figure by default.\n");
}
}
}
$rows = $db->get_rows($query);
if (!$rows) {
throw new \Exception("No results for query!");
}
} catch (\Exception $e) {
$this->container->getTwig()->addGlobal('message', $e->getMessage() . "\n");
}
$categories = '';
$count = 0;
$confOrders = array();
foreach ($rows_config as $row_config) {
$categories .= "'{$row_config['conf']} #{$row_config['num']}',";
$count += $row_config['num'];
$confOrders[] = $row_config['conf'];
}
$series = '';
$bench = '';
if ($rows) {
$seriesIndex = 0;
foreach ($rows as $row) {
//close previous serie if not first one
if ($bench && $bench != strtolower($row['bench'])) {
$series .= "]\n }, ";
}
//starts a new series
if ($bench != strtolower($row['bench'])) {
$seriesIndex = 0;
$bench = strtolower($row['bench']);
$series .= "\n {\n name: '" . strtolower($row['bench']) . "',\n data: [";
}
while ($row['conf'] != $confOrders[$seriesIndex]) {
$series .= "[null],";
$seriesIndex++;
}
$series .= "['{$row['conf']}'," . round($row['AVG_ALL_exe_time'] / $row['AVG_exe_time'], 3) . "],";
$seriesIndex++;
}
//close the last series
$series .= "]\n }, ";
}
return $this->render('configEvaluationViews/config_improvement.html.twig', array('title' => 'Improvement of Hadoop Execution by SW and HW Configurations', 'highcharts_js' => HighCharts::getHeader(), 'categories' => $categories, 'series' => $series, 'models' => $model_html));
}
public function configImprovementAction()
{
$db = $this->container->getDBUtils();
$preset = null;
if (sizeof($_GET) <= 1) {
$preset = Utils::setDefaultPreset($db, 'Config Improvement');
}
$selPreset = isset($_GET['presets']) ? $_GET['presets'] : "none";
$rows_config = '';
try {
$where_configs = '';
$concat_config = "";
$datefrom = Utils::read_params('datefrom', $where_configs);
$dateto = Utils::read_params('dateto', $where_configs);
$benchs = Utils::read_params('benchs', $where_configs);
$nets = Utils::read_params('nets', $where_configs);
$disks = Utils::read_params('disks', $where_configs);
$blk_sizes = Utils::read_params('blk_sizes', $where_configs);
$comps = Utils::read_params('comps', $where_configs);
$id_clusters = Utils::read_params('id_clusters', $where_configs);
$mapss = Utils::read_params('mapss', $where_configs);
$replications = Utils::read_params('replications', $where_configs);
$iosfs = Utils::read_params('iosfs', $where_configs);
$iofilebufs = Utils::read_params('iofilebufs', $where_configs);
$money = Utils::read_params('money', $where_configs);
$datanodes = Utils::read_params('datanodess', $where_configs, false);
$benchtype = Utils::read_params('bench_types', $where_configs);
$vm_sizes = Utils::read_params('vm_sizes', $where_configs, false);
$vm_coress = Utils::read_params('vm_coress', $where_configs, false);
$vm_RAMs = Utils::read_params('vm_RAMs', $where_configs, false);
$hadoop_versions = Utils::read_params('hadoop_versions', $where_configs, false);
$types = Utils::read_params('types', $where_configs, false);
$filters = Utils::read_params('filters', $where_configs, false);
$allunchecked = isset($_GET['allunchecked']) ? $_GET['allunchecked'] : '';
$minexetime = Utils::read_params('minexetime', $where_configs, false);
$maxexetime = Utils::read_params('maxexetime', $where_configs, false);
$provider = Utils::read_params('providers', $where_configs, false);
$vm_OS = Utils::read_params('vm_OSs', $where_configs, false);
$selectedGroups = array();
if (isset($_GET['selected-groups']) && $_GET['selected-groups'] != "") {
$selectedGroups = explode(",", $_GET['selected-groups']);
$concat_config = Utils::getConfig($selectedGroups);
} else {
$concat_config = 'disk';
$selectedGroups = array('disk');
}
$filter_execs = DBUtils::getFilterExecs();
$order_conf = 'LENGTH(conf), conf';
//get configs first (categories)
$query = "SELECT count(*) num, concat({$concat_config}) conf from execs e\n JOIN clusters c USING (id_cluster) WHERE 1 {$filter_execs} {$where_configs}\n GROUP BY conf ORDER BY {$order_conf} #AVG(exe_time)\n ;";
$rows_config = $db->get_rows($query);
$height = 600;
if (count($rows_config) > 4) {
$num_configs = count($rows_config);
$height = round($height + 10 * ($num_configs - 4));
}
//get the result rows
//#(select CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(exe_time ORDER BY exe_time SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) FROM execs e WHERE bench = e.bench $filter_execs $where_configs) P50_ALL_exe_time,
$query = "SELECT #count(*),\n \t\t e.id_exec,\n concat({$concat_config}) conf, bench,\n avg(exe_time) AVG_exe_time,\n #max(exe_time) MAX_exe_time,\n min(exe_time) MIN_exe_time,\n #CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(exe_time ORDER BY exe_time SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `P50_exe_time`,\n #CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(exe_time ORDER BY exe_time SEPARATOR ','), ',', 95/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `P95_exe_time`,\n #CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(exe_time ORDER BY exe_time SEPARATOR ','), ',', 05/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `P05_exe_time`,\n (select AVG(exe_time) FROM execs WHERE bench = e.bench {$where_configs}) AVG_ALL_exe_time,\n #(select MAX(exe_time) FROM execs WHERE bench = e.bench {$where_configs}) MAX_ALL_exe_time,\n #(select MIN(exe_time) FROM execs WHERE bench = e.bench {$where_configs}) MIN_ALL_exe_time,\n 'none'\n from execs e JOIN clusters USING (id_cluster)\n WHERE 1 {$filter_execs} {$where_configs}\n GROUP BY conf, bench order by bench, {$order_conf};";
$rows = $db->get_rows($query);
if ($rows) {
//print_r($rows);
} else {
throw new \Exception("No results for query!");
}
} catch (\Exception $e) {
$this->container->getTwig()->addGlobal('message', $e->getMessage() . "\n");
}
$categories = '';
$count = 0;
$confOrders = array();
foreach ($rows_config as $row_config) {
$categories .= "'{$row_config['conf']} #{$row_config['num']}',";
$count += $row_config['num'];
$confOrders[] = $row_config['conf'];
}
$series = '';
$bench = '';
if ($rows) {
$seriesIndex = 0;
foreach ($rows as $row) {
//close previous serie if not first one
if ($bench && $bench != strtolower($row['bench'])) {
$series .= "]\n }, ";
}
//starts a new series
if ($bench != strtolower($row['bench'])) {
$seriesIndex = 0;
$bench = strtolower($row['bench']);
$series .= "\n {\n name: '" . strtolower($row['bench']) . "',\n data: [";
}
while ($row['conf'] != $confOrders[$seriesIndex]) {
$series .= "[null],";
$seriesIndex++;
}
$series .= "['{$row['conf']}'," . round($row['AVG_ALL_exe_time'] / $row['AVG_exe_time'], 3) . "],";
$seriesIndex++;
}
//close the last series
$series .= "]\n }, ";
}
echo $this->container->getTwig()->render('config_improvement/config_improvement.html.twig', array('selected' => 'Config Improvement', 'title' => 'Improvement of Hadoop Execution by SW and HW Configurations', 'highcharts_js' => HighCharts::getHeader(), 'categories' => $categories, 'series' => $series, 'datefrom' => $datefrom, 'dateto' => $dateto, 'benchs' => $benchs, 'nets' => $nets, 'disks' => $disks, 'blk_sizes' => $blk_sizes, 'comps' => $comps, 'id_clusters' => $id_clusters, 'mapss' => $mapss, 'replications' => $replications, 'iosfs' => $iosfs, 'iofilebufs' => $iofilebufs, 'count' => $count, 'height' => $height, 'money' => $money, 'datanodess' => $datanodes, 'bench_types' => $benchtype, 'vm_sizes' => $vm_sizes, 'vm_coress' => $vm_coress, 'vm_RAMs' => $vm_RAMs, 'vm_OS' => $vm_OS, 'hadoop_versions' => $hadoop_versions, 'types' => $types, 'providers' => $provider, 'filters' => $filters, 'allunchecked' => $allunchecked, 'minexetime' => $minexetime, 'maxexetime' => $maxexetime, 'selectedGroups' => $selectedGroups, 'preset' => $preset, 'selPreset' => $selPreset, 'options' => Utils::getFilterOptions($db)));
}
