From c26d278865ff8e6be35f6899337fe129889f887a Mon Sep 17 00:00:00 2001 From: Fabian Mastenbroek Date: Mon, 11 May 2020 20:43:47 +0200 Subject: feat: Add revised database schema for experiments --- opendc/opendc-experiments-sc20/schema.sql | 142 +++++++++++++++++++++++++----- 1 file changed, 121 insertions(+), 21 deletions(-) (limited to 'opendc/opendc-experiments-sc20/schema.sql') diff --git a/opendc/opendc-experiments-sc20/schema.sql b/opendc/opendc-experiments-sc20/schema.sql index 51990a75..677240fa 100644 --- a/opendc/opendc-experiments-sc20/schema.sql +++ b/opendc/opendc-experiments-sc20/schema.sql @@ -1,22 +1,122 @@ -DROP TABLE IF EXISTS host_reports; -CREATE TABLE host_reports ( - id BIGSERIAL PRIMARY KEY NOT NULL, - experiment_id BIGINT NOT NULL, - time BIGINT NOT NULL, - duration BIGINT NOT NULL, - requested_burst BIGINT NOT NULL, - granted_burst BIGINT NOT NULL, - overcommissioned_burst BIGINT NOT NULL, - interfered_burst BIGINT NOT NULL, - cpu_usage DOUBLE PRECISION NOT NULL, - cpu_demand DOUBLE PRECISION NOT NULL, - image_count INTEGER NOT NULL, - server TEXT NOT NULL, - host_state TEXT NOT NULL, - host_usage DOUBLE PRECISION NOT NULL, - power_draw DOUBLE PRECISION NOT NULL, - total_submitted_vms BIGINT NOT NULL, - total_queued_vms BIGINT NOT NULL, - total_running_vms BIGINT NOT NULL, - total_finished_vms BIGINT NOT NULL +-- A portfolio represents a collection of scenarios are tested. +DROP TABLE IF EXISTS portfolios; +CREATE TABLE portfolios +( + id BIGSERIAL PRIMARY KEY NOT NULL, + name TEXT NOT NULL ); + +-- A scenario represents a single point in the design space (a unique combination of parameters) +DROP TABLE IF EXISTS scenarios; +CREATE TABLE scenarios +( + id BIGSERIAL PRIMARY KEY NOT NULL, + portfolio_id BIGINT NOT NULL, + repetitions INTEGER NOT NULL, + topology TEXT NOT NULL, + workload_name TEXT NOT NULL, + workload_fraction DOUBLE PRECISION NOT NULL, + allocation_policy TEXT NOT NULL, + failures BIT NOT NULL, + interference BIT NOT NULL, + + FOREIGN KEY (portfolio_id) REFERENCES portfolios (id) + ON DELETE CASCADE + ON UPDATE CASCADE +); + +CREATE TYPE run_state AS ENUM ('wait', 'active', 'fail', 'ok'); + +-- An experiment run represent a single invocation of a trial and is used to distinguish between repetitions of the +-- same set of parameters. +DROP TABLE IF EXISTS runs; +CREATE TABLE runs +( + id INTEGER NOT NULL, + scenario_id BIGINT NOT NULL, + seed INTEGER NOT NULL, + state run_state NOT NULL DEFAULT 'wait'::run_state, + start_time TIMESTAMP NOT NULL, + end_time TIMESTAMP, + + PRIMARY KEY (scenario_id, id), + FOREIGN KEY (scenario_id) REFERENCES scenarios (id) + ON DELETE CASCADE + ON UPDATE CASCADE +); + +-- Metrics of the hypervisors reported per slice +DROP TABLE IF EXISTS host_metrics; +CREATE TABLE host_metrics +( + id BIGSERIAL PRIMARY KEY NOT NULL, + scenario_id BIGINT NOT NULL, + run_id INTEGER NOT NULL, + host_id TEXT NOT NULL, + state TEXT NOT NULL, + timestamp TIMESTAMP NOT NULL, + duration BIGINT NOT NULL, + vm_count INTEGER NOT NULL, + requested_burst BIGINT NOT NULL, + granted_burst BIGINT NOT NULL, + overcommissioned_burst BIGINT NOT NULL, + interfered_burst BIGINT NOT NULL, + cpu_usage DOUBLE PRECISION NOT NULL, + cpu_demand DOUBLE PRECISION NOT NULL, + power_draw DOUBLE PRECISION NOT NULL, + + FOREIGN KEY (scenario_id, run_id) REFERENCES runs (scenario_id, id) + ON DELETE CASCADE + ON UPDATE CASCADE +); + +CREATE INDEX host_metrics_idx ON host_metrics (scenario_id, run_id, timestamp, host_id); + +-- Metrics of the VMs reported per slice +DROP TABLE IF EXISTS vm_metrics; +CREATE TABLE vm_metrics +( + id BIGSERIAL PRIMARY KEY NOT NULL, + scenario_id BIGINT NOT NULL, + run_id INTEGER NOT NULL, + vm_id TEXT NOT NULL, + host_id TEXT NOT NULL, + state TEXT NOT NULL, + timestamp TIMESTAMP NOT NULL, + duration BIGINT NOT NULL, + requested_burst BIGINT NOT NULL, + granted_burst BIGINT NOT NULL, + overcommissioned_burst BIGINT NOT NULL, + interfered_burst BIGINT NOT NULL, + cpu_usage DOUBLE PRECISION NOT NULL, + cpu_demand DOUBLE PRECISION NOT NULL, + + FOREIGN KEY (scenario_id, run_id) REFERENCES runs (scenario_id, id) + ON DELETE CASCADE + ON UPDATE CASCADE +); + +CREATE INDEX vm_metrics_idx ON vm_metrics (scenario_id, run_id, timestamp, vm_id); + +-- Metrics of the provisioner reported per change +DROP TABLE IF EXISTS provisioner_metrics; +CREATE TABLE provisioner_metrics +( + id BIGSERIAL PRIMARY KEY NOT NULL, + scenario_id BIGINT NOT NULL, + run_id INTEGER NOT NULL, + timestamp TIMESTAMP NOT NULL, + host_total_count INTEGER NOT NULL, + host_available_count INTEGER NOT NULL, + vm_total_count INTEGER NOT NULL, + vm_active_count INTEGER NOT NULL, + vm_inactive_count INTEGER NOT NULL, + vm_waiting_count INTEGER NOT NULL, + vm_failed_count INTEGER NOT NULL, + + FOREIGN KEY (scenario_id, run_id) REFERENCES runs (scenario_id, id) + ON DELETE CASCADE + ON UPDATE CASCADE +); + +CREATE INDEX provisioner_metrics_idx ON provisioner_metrics (scenario_id, run_id, timestamp); -- cgit v1.2.3 From 924179b45b4e7e1ac848fd852fe39d927ca0d85a Mon Sep 17 00:00:00 2001 From: Fabian Mastenbroek Date: Wed, 13 May 2020 01:28:18 +0200 Subject: feat: Add experiment orchestrator in Kotlin --- opendc/opendc-experiments-sc20/schema.sql | 75 +++++++++++++++++++------------ 1 file changed, 47 insertions(+), 28 deletions(-) (limited to 'opendc/opendc-experiments-sc20/schema.sql') diff --git a/opendc/opendc-experiments-sc20/schema.sql b/opendc/opendc-experiments-sc20/schema.sql index 677240fa..515348c6 100644 --- a/opendc/opendc-experiments-sc20/schema.sql +++ b/opendc/opendc-experiments-sc20/schema.sql @@ -1,13 +1,26 @@ --- A portfolio represents a collection of scenarios are tested. -DROP TABLE IF EXISTS portfolios; +-- An experiment represents a collection of portfolios. +DROP TABLE IF EXISTS experiments CASCADE; +CREATE TABLE experiments +( + id BIGSERIAL PRIMARY KEY NOT NULL, + creation_time TIMESTAMP NOT NULL DEFAULT (now()) +); + +-- A portfolio represents a collection of scenarios tested. +DROP TABLE IF EXISTS portfolios CASCADE; CREATE TABLE portfolios ( - id BIGSERIAL PRIMARY KEY NOT NULL, - name TEXT NOT NULL + id BIGSERIAL PRIMARY KEY NOT NULL, + experiment_id BIGINT NOT NULL, + name TEXT NOT NULL, + + FOREIGN KEY (experiment_id) REFERENCES experiments (id) + ON DELETE CASCADE + ON UPDATE CASCADE ); -- A scenario represents a single point in the design space (a unique combination of parameters) -DROP TABLE IF EXISTS scenarios; +DROP TABLE IF EXISTS scenarios CASCADE; CREATE TABLE scenarios ( id BIGSERIAL PRIMARY KEY NOT NULL, @@ -17,27 +30,30 @@ CREATE TABLE scenarios workload_name TEXT NOT NULL, workload_fraction DOUBLE PRECISION NOT NULL, allocation_policy TEXT NOT NULL, - failures BIT NOT NULL, - interference BIT NOT NULL, + failures BOOLEAN NOT NULL, + interference BOOLEAN NOT NULL, FOREIGN KEY (portfolio_id) REFERENCES portfolios (id) ON DELETE CASCADE ON UPDATE CASCADE + ); +DROP TYPE IF EXISTS run_state CASCADE; CREATE TYPE run_state AS ENUM ('wait', 'active', 'fail', 'ok'); -- An experiment run represent a single invocation of a trial and is used to distinguish between repetitions of the -- same set of parameters. -DROP TABLE IF EXISTS runs; +DROP TABLE IF EXISTS runs CASCADE; CREATE TABLE runs ( - id INTEGER NOT NULL, - scenario_id BIGINT NOT NULL, - seed INTEGER NOT NULL, - state run_state NOT NULL DEFAULT 'wait'::run_state, - start_time TIMESTAMP NOT NULL, - end_time TIMESTAMP, + id INTEGER NOT NULL, + scenario_id BIGINT NOT NULL, + seed INTEGER NOT NULL, + state run_state NOT NULL DEFAULT 'wait'::run_state, + submit_time TIMESTAMP NOT NULL DEFAULT (now()), + start_time TIMESTAMP DEFAULT (NULL), + end_time TIMESTAMP DEFAULT (NULL), PRIMARY KEY (scenario_id, id), FOREIGN KEY (scenario_id) REFERENCES scenarios (id) @@ -46,7 +62,7 @@ CREATE TABLE runs ); -- Metrics of the hypervisors reported per slice -DROP TABLE IF EXISTS host_metrics; +DROP TABLE IF EXISTS host_metrics CASCADE; CREATE TABLE host_metrics ( id BIGSERIAL PRIMARY KEY NOT NULL, @@ -70,10 +86,11 @@ CREATE TABLE host_metrics ON UPDATE CASCADE ); +DROP INDEX IF EXISTS host_metrics_idx; CREATE INDEX host_metrics_idx ON host_metrics (scenario_id, run_id, timestamp, host_id); -- Metrics of the VMs reported per slice -DROP TABLE IF EXISTS vm_metrics; +DROP TABLE IF EXISTS vm_metrics CASCADE; CREATE TABLE vm_metrics ( id BIGSERIAL PRIMARY KEY NOT NULL, @@ -96,27 +113,29 @@ CREATE TABLE vm_metrics ON UPDATE CASCADE ); +DROP INDEX IF EXISTS vm_metrics_idx; CREATE INDEX vm_metrics_idx ON vm_metrics (scenario_id, run_id, timestamp, vm_id); -- Metrics of the provisioner reported per change -DROP TABLE IF EXISTS provisioner_metrics; +DROP TABLE IF EXISTS provisioner_metrics CASCADE; CREATE TABLE provisioner_metrics ( - id BIGSERIAL PRIMARY KEY NOT NULL, - scenario_id BIGINT NOT NULL, - run_id INTEGER NOT NULL, - timestamp TIMESTAMP NOT NULL, - host_total_count INTEGER NOT NULL, - host_available_count INTEGER NOT NULL, - vm_total_count INTEGER NOT NULL, - vm_active_count INTEGER NOT NULL, - vm_inactive_count INTEGER NOT NULL, - vm_waiting_count INTEGER NOT NULL, - vm_failed_count INTEGER NOT NULL, + id BIGSERIAL PRIMARY KEY NOT NULL, + scenario_id BIGINT NOT NULL, + run_id INTEGER NOT NULL, + timestamp TIMESTAMP NOT NULL, + host_total_count INTEGER NOT NULL, + host_available_count INTEGER NOT NULL, + vm_total_count INTEGER NOT NULL, + vm_active_count INTEGER NOT NULL, + vm_inactive_count INTEGER NOT NULL, + vm_waiting_count INTEGER NOT NULL, + vm_failed_count INTEGER NOT NULL, FOREIGN KEY (scenario_id, run_id) REFERENCES runs (scenario_id, id) ON DELETE CASCADE ON UPDATE CASCADE ); +DROP INDEX IF EXISTS provisioner_metrics_idx; CREATE INDEX provisioner_metrics_idx ON provisioner_metrics (scenario_id, run_id, timestamp); -- cgit v1.2.3 From ac8c2bd72cf6408a4f8325529a9d67be47637b9f Mon Sep 17 00:00:00 2001 From: Fabian Mastenbroek Date: Thu, 14 May 2020 20:01:49 +0200 Subject: feat: Add option for specifying failure interval --- opendc/opendc-experiments-sc20/schema.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'opendc/opendc-experiments-sc20/schema.sql') diff --git a/opendc/opendc-experiments-sc20/schema.sql b/opendc/opendc-experiments-sc20/schema.sql index 515348c6..92cb5d1f 100644 --- a/opendc/opendc-experiments-sc20/schema.sql +++ b/opendc/opendc-experiments-sc20/schema.sql @@ -30,7 +30,7 @@ CREATE TABLE scenarios workload_name TEXT NOT NULL, workload_fraction DOUBLE PRECISION NOT NULL, allocation_policy TEXT NOT NULL, - failures BOOLEAN NOT NULL, + failure_frequency DOUBLE PRECISION NOT NULL, interference BOOLEAN NOT NULL, FOREIGN KEY (portfolio_id) REFERENCES portfolios (id) -- cgit v1.2.3 From 287d85732a8bcd5d85a8628006828fa460baaff9 Mon Sep 17 00:00:00 2001 From: Fabian Mastenbroek Date: Fri, 15 May 2020 02:18:45 +0200 Subject: refactor: Move entirely to Parquet --- opendc/opendc-experiments-sc20/schema.sql | 141 ------------------------------ 1 file changed, 141 deletions(-) delete mode 100644 opendc/opendc-experiments-sc20/schema.sql (limited to 'opendc/opendc-experiments-sc20/schema.sql') diff --git a/opendc/opendc-experiments-sc20/schema.sql b/opendc/opendc-experiments-sc20/schema.sql deleted file mode 100644 index 92cb5d1f..00000000 --- a/opendc/opendc-experiments-sc20/schema.sql +++ /dev/null @@ -1,141 +0,0 @@ --- An experiment represents a collection of portfolios. -DROP TABLE IF EXISTS experiments CASCADE; -CREATE TABLE experiments -( - id BIGSERIAL PRIMARY KEY NOT NULL, - creation_time TIMESTAMP NOT NULL DEFAULT (now()) -); - --- A portfolio represents a collection of scenarios tested. -DROP TABLE IF EXISTS portfolios CASCADE; -CREATE TABLE portfolios -( - id BIGSERIAL PRIMARY KEY NOT NULL, - experiment_id BIGINT NOT NULL, - name TEXT NOT NULL, - - FOREIGN KEY (experiment_id) REFERENCES experiments (id) - ON DELETE CASCADE - ON UPDATE CASCADE -); - --- A scenario represents a single point in the design space (a unique combination of parameters) -DROP TABLE IF EXISTS scenarios CASCADE; -CREATE TABLE scenarios -( - id BIGSERIAL PRIMARY KEY NOT NULL, - portfolio_id BIGINT NOT NULL, - repetitions INTEGER NOT NULL, - topology TEXT NOT NULL, - workload_name TEXT NOT NULL, - workload_fraction DOUBLE PRECISION NOT NULL, - allocation_policy TEXT NOT NULL, - failure_frequency DOUBLE PRECISION NOT NULL, - interference BOOLEAN NOT NULL, - - FOREIGN KEY (portfolio_id) REFERENCES portfolios (id) - ON DELETE CASCADE - ON UPDATE CASCADE - -); - -DROP TYPE IF EXISTS run_state CASCADE; -CREATE TYPE run_state AS ENUM ('wait', 'active', 'fail', 'ok'); - --- An experiment run represent a single invocation of a trial and is used to distinguish between repetitions of the --- same set of parameters. -DROP TABLE IF EXISTS runs CASCADE; -CREATE TABLE runs -( - id INTEGER NOT NULL, - scenario_id BIGINT NOT NULL, - seed INTEGER NOT NULL, - state run_state NOT NULL DEFAULT 'wait'::run_state, - submit_time TIMESTAMP NOT NULL DEFAULT (now()), - start_time TIMESTAMP DEFAULT (NULL), - end_time TIMESTAMP DEFAULT (NULL), - - PRIMARY KEY (scenario_id, id), - FOREIGN KEY (scenario_id) REFERENCES scenarios (id) - ON DELETE CASCADE - ON UPDATE CASCADE -); - --- Metrics of the hypervisors reported per slice -DROP TABLE IF EXISTS host_metrics CASCADE; -CREATE TABLE host_metrics -( - id BIGSERIAL PRIMARY KEY NOT NULL, - scenario_id BIGINT NOT NULL, - run_id INTEGER NOT NULL, - host_id TEXT NOT NULL, - state TEXT NOT NULL, - timestamp TIMESTAMP NOT NULL, - duration BIGINT NOT NULL, - vm_count INTEGER NOT NULL, - requested_burst BIGINT NOT NULL, - granted_burst BIGINT NOT NULL, - overcommissioned_burst BIGINT NOT NULL, - interfered_burst BIGINT NOT NULL, - cpu_usage DOUBLE PRECISION NOT NULL, - cpu_demand DOUBLE PRECISION NOT NULL, - power_draw DOUBLE PRECISION NOT NULL, - - FOREIGN KEY (scenario_id, run_id) REFERENCES runs (scenario_id, id) - ON DELETE CASCADE - ON UPDATE CASCADE -); - -DROP INDEX IF EXISTS host_metrics_idx; -CREATE INDEX host_metrics_idx ON host_metrics (scenario_id, run_id, timestamp, host_id); - --- Metrics of the VMs reported per slice -DROP TABLE IF EXISTS vm_metrics CASCADE; -CREATE TABLE vm_metrics -( - id BIGSERIAL PRIMARY KEY NOT NULL, - scenario_id BIGINT NOT NULL, - run_id INTEGER NOT NULL, - vm_id TEXT NOT NULL, - host_id TEXT NOT NULL, - state TEXT NOT NULL, - timestamp TIMESTAMP NOT NULL, - duration BIGINT NOT NULL, - requested_burst BIGINT NOT NULL, - granted_burst BIGINT NOT NULL, - overcommissioned_burst BIGINT NOT NULL, - interfered_burst BIGINT NOT NULL, - cpu_usage DOUBLE PRECISION NOT NULL, - cpu_demand DOUBLE PRECISION NOT NULL, - - FOREIGN KEY (scenario_id, run_id) REFERENCES runs (scenario_id, id) - ON DELETE CASCADE - ON UPDATE CASCADE -); - -DROP INDEX IF EXISTS vm_metrics_idx; -CREATE INDEX vm_metrics_idx ON vm_metrics (scenario_id, run_id, timestamp, vm_id); - --- Metrics of the provisioner reported per change -DROP TABLE IF EXISTS provisioner_metrics CASCADE; -CREATE TABLE provisioner_metrics -( - id BIGSERIAL PRIMARY KEY NOT NULL, - scenario_id BIGINT NOT NULL, - run_id INTEGER NOT NULL, - timestamp TIMESTAMP NOT NULL, - host_total_count INTEGER NOT NULL, - host_available_count INTEGER NOT NULL, - vm_total_count INTEGER NOT NULL, - vm_active_count INTEGER NOT NULL, - vm_inactive_count INTEGER NOT NULL, - vm_waiting_count INTEGER NOT NULL, - vm_failed_count INTEGER NOT NULL, - - FOREIGN KEY (scenario_id, run_id) REFERENCES runs (scenario_id, id) - ON DELETE CASCADE - ON UPDATE CASCADE -); - -DROP INDEX IF EXISTS provisioner_metrics_idx; -CREATE INDEX provisioner_metrics_idx ON provisioner_metrics (scenario_id, run_id, timestamp); -- cgit v1.2.3