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))); }