From a048f92d0362bc4f7b0a077e77d5fb2d89bb2467 Mon Sep 17 00:00:00 2001 From: Marcin Haba Date: Mon, 6 Feb 2023 13:14:31 +0100 Subject: [PATCH] baculum: Add estimated job values endpoint that uses job historical data for estimation --- .../protected/API/Modules/JobManager.php | 131 ++++++++++++++++++ .../API/Pages/API/JobEstimateStat.php | 66 +++++++++ .../protected/API/Pages/API/endpoints.xml | 1 + .../protected/API/openapi_baculum.json | 81 +++++++++++ .../Common/Modules/Miscellaneous.php | 2 + 5 files changed, 281 insertions(+) create mode 100644 gui/baculum/protected/API/Pages/API/JobEstimateStat.php diff --git a/gui/baculum/protected/API/Modules/JobManager.php b/gui/baculum/protected/API/Modules/JobManager.php index b41183662..a624d8898 100644 --- a/gui/baculum/protected/API/Modules/JobManager.php +++ b/gui/baculum/protected/API/Modules/JobManager.php @@ -466,5 +466,136 @@ WHERE Client.ClientId='$clientid' $wh"; $sth->execute($where['params']); return $sth->fetchAll(PDO::FETCH_ASSOC); } + + /** + * Get job estimation values based on job history. + * For PostgreSQL catalog the byte and file values are computed using linear regression. + * For MySQL and SQLite catalog the byte and file values are average values + * It returns array in form: + * [ + * 'bytes_est' => estimated job bytes + * 'bytes_corr' => correlation of the historical size values + * 'files_est' => estimated job files + * 'files_corr' => correlation of the historical file values + * 'job_count' => number of jobs taken into account + * 'avg_duration' => average job duration per job level, + * 'success_perc' => percentage usage successful jobs (for all job levels) + * ] + * + * @param string $job job name + * @param string $level job level letter + * @return array|bool job estimation values + */ + public function getJobEstimatation($job, $level) { + $now = time(); + $q = ''; + $sql = ''; + $db_params = $this->getModule('api_config')->getConfig('db'); + if ($db_params['type'] === Database::PGSQL_TYPE) { + $sql = 'SELECT + COALESCE(CORR(jobbytes, jobtdate), 0) AS corr_jobbytes, + (' . $now . ' * REGR_SLOPE(jobbytes, jobtdate) + REGR_INTERCEPT(jobbytes, jobtdate)) AS jobbytes, + COALESCE(CORR(jobfiles, jobtdate), 0) AS corr_jobfiles, + (' . $now . ' * REGR_SLOPE(jobfiles, jobtdate) + REGR_INTERCEPT(jobfiles, jobtdate)) AS jobfiles, + COUNT(1) AS nb_jobs'; + } else { + $sql = 'SELECT + 0.1 AS corr_jobbytes, + AVG(jobbytes) AS jobbytes, + 0.1 AS corr_jobfiles, + AVG(jobfiles) AS jobfiles, + COUNT(1) AS nb_jobs'; + } + + if ($level == 'D') { + $q = 'AND Job.StartTime > ( + SELECT StartTime + FROM Job + WHERE Job.Name = \'' . $job . '\' + AND Job.Level = \'F\' + AND Job.JobStatus IN (\'T\', \'W\') + ORDER BY Job.StartTime DESC LIMIT 1 + )'; + } + + $sql .= ' + FROM ( + SELECT JobBytes AS jobbytes, + JobFiles AS jobfiles, + JobTDate AS jobtdate + FROM Job + WHERE Job.Name = \'' . $job . '\' + AND Job.Level = \'' . $level . '\' + AND Job.JobStatus IN (\'T\', \'W\') + ' . $q . ' + ORDER BY StartTime DESC + LIMIT 4 + ) AS temp'; + + $connection = JobRecord::finder()->getDbConnection(); + $connection->setActive(true); + $pdo = $connection->getPdoInstance(); + $sth = $pdo->query($sql); + $result = $sth->fetch(PDO::FETCH_ASSOC); + $duration = $this->getJobHistoryDuration($job, $level); + $success = $this->getJobHistorySuccessPercent($job); + return [ + 'bytes_est' => (int) ($result['jobbytes'] ?? '0'), + 'bytes_corr' => (float) $result['corr_jobbytes'], + 'files_est' => (int) ($result['jobfiles'] ?? '0'), + 'files_corr' => (float) $result['corr_jobfiles'], + 'job_count' => (int) $result['nb_jobs'], + 'avg_duration' => (int) ($duration['duration'] ?? '0'), + 'success_perc' => (int) ($success['success'] ?? '0') + ]; + } + + /** + * Get average job duration by job. + * NOTE: It is job duration per job level, not overall job duration for + * all job statuses. + * + * @param string $job job name + * @return array|bool average job duration or false if no job found + */ + public function getJobHistoryDuration($job, $level) { + $duration = ''; + $db_params = $this->getModule('api_config')->getConfig('db'); + + if ($db_params['type'] === Database::PGSQL_TYPE) { + $duration = 'date_part(\'epoch\', EndTime) - date_part(\'epoch\', StartTime)'; + } elseif ($db_params['type'] === Database::MYSQL_TYPE) { + $duration = 'UNIX_TIMESTAMP(EndTime) - UNIX_TIMESTAMP(StartTime)'; + } elseif ($db_params['type'] === Database::SQLITE_TYPE) { + $duration = 'strftime(\'%s\', EndTime) - strftime(\'%s\', StartTime)'; + } + + $sql = 'SELECT AVG(' . $duration . ') AS duration + FROM Job + WHERE Name=\'' . $job . '\' AND Level=\'' . $level . '\''; + + $connection = JobRecord::finder()->getDbConnection(); + $connection->setActive(true); + $pdo = $connection->getPdoInstance(); + $sth = $pdo->query($sql); + return $sth->fetch(PDO::FETCH_ASSOC); + } + + /** + * Get percentage value of successful jobs by job. + * + * @param string $job job name + * @return array|bool percentage success ratio or false if no job found + */ + public function getJobHistorySuccessPercent($job) { + $sql = 'SELECT (COUNT(*) * 100.0 / NULLIF((SELECT COUNT(*) FROM Job WHERE Name=\'' . $job . '\'), 0)) as success + FROM Job + WHERE Name=\'' . $job . '\' AND JobStatus=\'T\''; + $connection = JobRecord::finder()->getDbConnection(); + $connection->setActive(true); + $pdo = $connection->getPdoInstance(); + $sth = $pdo->query($sql); + return $sth->fetch(PDO::FETCH_ASSOC); + } } ?> diff --git a/gui/baculum/protected/API/Pages/API/JobEstimateStat.php b/gui/baculum/protected/API/Pages/API/JobEstimateStat.php new file mode 100644 index 000000000..1ae064d59 --- /dev/null +++ b/gui/baculum/protected/API/Pages/API/JobEstimateStat.php @@ -0,0 +1,66 @@ + + * @category API + * @package Baculum API + */ +class JobEstimateStat extends BaculumAPIServer { + + public function get() { + $misc = $this->getModule('misc'); + $job = $this->Request->contains('name') && $misc->isValidName($this->Request['name']) ? $this->Request['name'] : null; + $level = $this->Request->contains('level') && $misc->isValidJobLevel($this->Request['level']) && in_array($this->Request['level'], $misc->backupJobLevels) ? $this->Request['level'] : null; + + $result = $this->getModule('bconsole')->bconsoleCommand( + $this->director, + ['.jobs'], + null, + true + ); + if (is_string($job) && $result->exitcode === 0 && !in_array($job, $result->output)) { + // Job not allowed for specific user + $job = null; + } + + if (is_null($job)) { + $this->output = JobError::ERROR_JOB_DOES_NOT_EXISTS; + $this->error = JobError::MSG_ERROR_JOB_DOES_NOT_EXISTS; + return; + } + if (is_null($level)) { + $this->output = JobError::ERROR_INVALID_JOBLEVEL; + $this->error = JobError::MSG_ERROR_INVALID_JOBLEVEL; + return; + } + $this->output = $this->getModule('job')->getJobEstimatation($job, $level); + $this->error = JobError::ERROR_NO_ERRORS; + } +} + +?> diff --git a/gui/baculum/protected/API/Pages/API/endpoints.xml b/gui/baculum/protected/API/Pages/API/endpoints.xml index 00aa6bfa9..c22cd57e5 100644 --- a/gui/baculum/protected/API/Pages/API/endpoints.xml +++ b/gui/baculum/protected/API/Pages/API/endpoints.xml @@ -75,6 +75,7 @@ + diff --git a/gui/baculum/protected/API/openapi_baculum.json b/gui/baculum/protected/API/openapi_baculum.json index 5fc72718e..5ed14e62c 100644 --- a/gui/baculum/protected/API/openapi_baculum.json +++ b/gui/baculum/protected/API/openapi_baculum.json @@ -1728,6 +1728,87 @@ ] } }, + "/api/v2/jobs/estimate/stat": { + "get": { + "tags": ["jobs"], + "summary": "Get estimated values based on job history", + "description": "Get estimated job values based on job history", + "responses": { + "200": { + "description": "Estimated values", + "content": { + "application/json": { + "schema": { + "type": "object", + "properties": { + "output": { + "type": "object", + "properties": { + "bytes_est": { + "type": "integer", + "description": "Estimated job bytes" + }, + "bytes_corr": { + "type": "number", + "description": "Correlation of historical job bytes. Value between -1 and 1" + }, + "files_est": { + "type": "integer", + "description": "Estimated job files" + }, + "files_corr": { + "type": "number", + "description": "Correlation of historical job files. Value between -1 and 1" + }, + "job_count": { + "type": "integer", + "description": "Job count taken into account for bytes/files estimation" + }, + "avg_duration": { + "type": "integer", + "description": "Average job duration based on historical job time values. NOTE: it takes into account all finished jobs for given job name and level." + }, + "success_perc": { + "type": "integer", + "description": "Percentage value of successful jobs. NOTE: it takes into account all successful jobs for given job name." + } + } + }, + "error": { + "type": "integer", + "description": "Error code", + "enum": [0, 1, 2, 3, 4, 5, 6, 7, 11, 50, 51, 1000] + } + } + } + } + } + } + }, + "parameters": [ + { + "name": "name", + "in": "query", + "required": true, + "description": "Job name", + "schema": { + "type": "string", + "pattern": "[a-zA-Z0-9:.-_ ]+" + } + }, + { + "name": "level", + "in": "query", + "description": "Backup job level", + "required": true, + "schema": { + "type": "string", + "enum": ["F","I", "D"] + } + } + ] + } + }, "/api/v2/jobs/run": { "post": { "tags": ["jobs"], diff --git a/gui/baculum/protected/Common/Modules/Miscellaneous.php b/gui/baculum/protected/Common/Modules/Miscellaneous.php index ed2d2cc4f..8d4d1e954 100644 --- a/gui/baculum/protected/Common/Modules/Miscellaneous.php +++ b/gui/baculum/protected/Common/Modules/Miscellaneous.php @@ -62,6 +62,8 @@ class Miscellaneous extends TModule { 'A' => 'Data' ); + public $backupJobLevels = ['F', 'I', 'D']; + public $jobStates = array( 'C' => array('value' => 'Created', 'description' =>'Created but not yet running'), 'R' => array('value' => 'Running', 'description' => 'Running'), -- 2.47.3