From c26d278865ff8e6be35f6899337fe129889f887a Mon Sep 17 00:00:00 2001
From: Fabian Mastenbroek <mail.fabianm@gmail.com>
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 <mail.fabianm@gmail.com>
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 <mail.fabianm@gmail.com>
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 <mail.fabianm@gmail.com>
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

