This repository has been archived by the owner on Mar 8, 2022. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 2
Timescaledb Schema
bialesdaniel edited this page Dec 14, 2020
·
32 revisions
| Column name | Data Type | Constrains | Description |
|---|---|---|---|
| id | integer | SERIAL PRIMARY KEY | Auto-generated index. Start from 1. |
| time | timestamp with time zone | NOT NULL | Start time of the performance testing. |
| query_mode | character varying(30) | NOT NULL | The query mode when running the run_junit.py. Possible values are extended/simple. |
| jenkins_job_id | character varying(15) | NOT NULL | The job id of the current job in Jenkins. |
| git_branch. | character varying(255) | NOT NULL | The branch name where the nightly job runs. |
| git_commit_id | character varying(40) | NOT NULL | The commit id of the Git commit that the performance tests were run on. |
| db_version | character varying(255) | NOT NULL | The version of NoisePage. |
| environment | jsonb | NOT NULL | Information about the test environment that the OLTPBench tests were run on. |
| benchmark_type | character varying(20) | NOT NULL | The type of benchmark testing. Possible values are specified by OLTPBench. |
| scale_factor | numeric(10,4) | NOT NULL | The size of the database to load. |
| terminals | smallint | NOT NULL, >= 0 | The number of client threads that will issue requests to the DBMS. |
| client_time | smallint | NOT NULL, >= 0 | How long did this test run. 60/600 |
| weights | jsonb | NOT NULL | The weight of transactions. Different benchmark types have different weights but the sum should be 100. |
| max_connection_threads | smallint | NOT NULL, >= 0 | The maximum number of concurrent threads that are allowed. |
| wal_device | character varying(30) | NOT NULL | The device that the write-ahead log is written to. HDD/Ram disk/SATA SSD/NVMe SSD/None. |
| metrics | jsonb | NOT NULL | OLTPBench performance metrics. |
| incremental_metrics | jsonb | NOT NULL | OLTPBench performance metrics taken throughout the duration of the test. |
| Column name | Sample Data |
|---|---|
| id | 1 |
| time | 2020-07-29 21:54:36.176+00 |
| query_mode | extended |
| jenkins_job_id | 9 |
| git_branch. | master |
| git_commit_id | 1188c709e8421f5f501acee414df612a409b3aff |
| db_version | 9.5 |
| environment | { "cpu_number": "24", "cpu_socket": "true", "os_version": "Ubuntu 18.04 bionic" } |
| benchmark_type | tatp |
| scale_factor | 1.0000 |
| terminals | 4 |
| client_time | 60 |
| weights | { "GetAccessData": 35, "UpdateLocation": 14, "GetNewDestination": 10, "GetSubscriberData": 35, "DeleteCallForwarding": 2, "InsertCallForwarding": 2, "UpdateSubscriberData": 2 } |
| max_connection_threads | 4 |
| wal_device | HDD |
| metrics | { "latency": { "avg": "3.4940", "max": "140.7000", "min": "0.3200", "l_25": "0.8010", "l_75": "4.2860", "l_90": "7.7110", "l_95": "11.9700", "l_99": "22.9400", "median": "2.1080" }, "throughput": "9148.747010825471000", "memory_info": { "rss": { "avg": "8010000000.0000" }, "vms": { "avg": "22300000000.0000" } }} |
| incremental_metrics | [ { "time": 0, "latency": { "avg": "4.2600", "max": "140.0000", "min": "0.3600", "l_25": "1.0000", "l_75": "5.3900", "l_90": "9.4900", "l_95": "13.8000", "l_99": "25.2000", "median": "2.6000" }, "throughput": "7490.200000000000000", "memory_info": { "rss": 4769419264, "vms": 18916319232 } }, { "time": 5, "latency": { "avg": "3.6500", "max": "60.1000", "min": "0.3600", "l_25": "0.9300", "l_75": "4.6700", "l_90": "7.9500", "l_95": "11.7000", "l_99": "22.1000", "median": "2.2400" }, "throughput": "8756.600000000000000", "memory_info": { "rss": 5148712960, "vms": 19047882752 } }, { "time": 10, "latency": { "avg": "3.5100", "max": "68.5000", "min": "0.3600", "l_25": "0.8200", "l_75": "4.3300", "l_90": "7.7900", "l_95": "11.8000", "l_99": "22.2000", "median": "2.1400" }, "throughput": "9082.000000000000000", "memory_info": { "rss": 5634334720, "vms": 19390767104 } }, { "time": 15, "latency": { "avg": "3.5200", "max": "45.0000", "min": "0.3600", "l_25": "0.8300", "l_75": "4.3600", "l_90": "7.7200", "l_95": "11.8000", "l_99": "22.3000", "median": "2.1700" }, "throughput": "9071.600000000000000", "memory_info": { "rss": 6173048832, "vms": 20303032320 } }, { "time": 20, "latency": { "avg": "3.4400", "max": "55.8000", "min": "0.3400", "l_25": "0.7800", "l_75": "4.2400", "l_90": "7.6800", "l_95": "11.8000", "l_99": "21.8000", "median": "2.0800" }, "throughput": "9289.800000000000000", "memory_info": { "rss": 6700052480, "vms": 20466606080 } }, { "time": 25, "latency": { "avg": "3.3900", "max": "45.0000", "min": "0.3300", "l_25": "0.7700", "l_75": "4.1600", "l_90": "7.4900", "l_95": "11.8000", "l_99": "22.0000", "median": "2.0600" }, "throughput": "9420.400000000000000", "memory_info": { "rss": 7258542080, "vms": 21680857088 } }, { "time": 30, "latency": { "avg": "3.3800", "max": "54.8000", "min": "0.3400", "l_25": "0.7600", "l_75": "4.0900", "l_90": "7.5100", "l_95": "11.9000", "l_99": "22.9000", "median": "2.0000" }, "throughput": "9433.000000000000000", "memory_info": { "rss": 7817527296, "vms": 21844434944 } }, { "time": 35, "latency": { "avg": "3.3900", "max": "47.2000", "min": "0.3300", "l_25": "0.7600", "l_75": "4.1200", "l_90": "7.5200", "l_95": "11.9000", "l_99": "22.9000", "median": "2.0100" }, "throughput": "9418.400000000000000", "memory_info": { "rss": 8371994624, "vms": 22647644160 } }, { "time": 40, "latency": { "avg": "3.3500", "max": "44.4000", "min": "0.3400", "l_25": "0.7500", "l_75": "4.0500", "l_90": "7.4100", "l_95": "11.6000", "l_99": "23.0000", "median": "2.0200" }, "throughput": "9521.800000000000000", "memory_info": { "rss": 8919482368, "vms": 23402618880 } }, { "time": 45, "latency": { "avg": "3.4200", "max": "45.4000", "min": "0.3200", "l_25": "0.7700", "l_75": "4.1900", "l_90": "7.4900", "l_95": "11.6000", "l_99": "23.5000", "median": "2.0600" }, "throughput": "9320.000000000000000", "memory_info": { "rss": 9483198464, "vms": 23629115392 } }, { "time": 50, "latency": { "avg": "3.3300", "max": "51.5000", "min": "0.3200", "l_25": "0.7500", "l_75": "4.0000", "l_90": "7.1500", "l_95": "11.7000", "l_99": "23.9000", "median": "2.0000" }, "throughput": "9579.800000000000000", "memory_info": { "rss": 10035478528, "vms": 24333754368 } }, { "time": 55, "latency": { "avg": "3.3900", "max": "47.8000", "min": "0.3200", "l_25": "0.7500", "l_75": "4.0900", "l_90": "7.4200", "l_95": "11.5000", "l_99": "24.0000", "median": "2.0200" }, "throughput": "9401.800000000000000", "memory_info": { "rss": 10593054720, "vms": 25332002816 } }] |
When designing this schema the goal was to balance flexibility with efficiency. Therefore, we decided that all data relating to the parameters of the test should have their own column (i.e benchmark_type, query_mode). Also, anything that we may want to query on should have its own column (i.e. git_commit_id, git_branch). Then the result data should be JSON fields to allow for flexibility of easily adding or removing metrics). Furthermore, the environment should be a JSON field as well because we may want to capture more information about the testing environment in the future, and as far as we could foresee it was not data that we would query on.
| Column name | Data Type | Constrains | Description |
|---|---|---|---|
| id | integer | SERIAL PRIMARY KEY | Auto-generated index. Start from 1. |
| time | timestamp with time zone | NOT NULL | Start time of the performance testing. |
| jenkins_job_id | character varying(15) | NOT NULL | The job id of the current job in Jenkins. |
| git_branch | character varying(255) | NOT NULL | The branch name where the nightly job runs. |
| git_commit_id | character varying(40) | NOT NULL | The commit id of the Git commit that the performance tests were run on. |
| db_version | character varying(255) | NOT NULL | The version of NoisePage. |
| environment | jsonb | NOT NULL | Information about the test environment that the OLTPBench tests were run on. |
| benchmark_suite | character varying(255) | NOT NULL | The name of the test suite that the microbenchmark test belongs to. |
| benchmark_name | character varying(255) | NOT NULL | The name of the microbenchmark test. |
| threads | smallint | NOT NULL, >= 0 | The number of threads to use for multi-threaded benchmarks. |
| min_runtime | smallint | NOT NULL, >= 0 | The minimum time the benchmarks could be run for (seconds). |
| wal_device | character varying(30) | NOT NULL | The device that the write-ahead log is written to. HDD/Ram disk/SATA SSD/NVMe SSD/None. |
| metrics | jsonb | NOT NULL | Microbenchmark performance metrics. |
| Column name | Sample Data |
|---|---|
| id | 1 |
| time | 2020-07-29 21:54:36.176+00 |
| jenkins_job_id | 9 |
| git_branch_id | master |
| git_commit_id | 7fbb936e861cf1a926de9aea70dcde89ac8aefae |
| db_version | 1.0.0 |
| environment | { "cpu_number": "24", "cpu_socket": "true", "os_version": "Ubuntu 20.04 focal" } |
| benchmark_suite | BwTreeBenchmark |
| benchmark_name | RandomInsertSequentialRead |
| threads | 4 |
| min_runtime | 10 |
| wal_device | Ram disk |
| metrics | { "status": "PASS", "tolerance": 50, "iterations": 3, "throughput": "6440532.417346500000000", "ref_throughput": "5545147.865463149000000", "stdev_throughput": "385121.845389707400000" } |
For the microbenchmarks, we made similar design decisions relating to flexibility and efficiency as we did for the oltpbench_results schema. We also decided to store ref_throughput, which is a rolling 30-day average, and stdev_throughput, which is the standard deviation for those 30 days in with the metrics. This would save us from having to calculate that as part of the query in our dashboard.
| Column name | Data Type | Constrains | Description |
|---|---|---|---|
| id | integer | SERIAL PRIMARY KEY | Auto-generated index. Start from 1. |
| time | timestamp with time zone | NOT NULL | The time the artifact stats were collected. |
| jenkins_job_id | character varying(15) | NOT NULL | The job id of the current job in Jenkins. |
| git_branch | character varying(255) | NOT NULL | The branch name where the nightly job runs. |
| git_commit_id | character varying(40) | NOT NULL | The commit id of the Git commit that the artifact stats were gathered from. |
| db_version | character varying(255) | NOT NULL | The version of NoisePage. |
| environment | jsonb | NOT NULL | Information about the test environment that the OLTPBench tests were run on. |
| metrics | jsonb | NOT NULL | Artifact stats that were collected. |
| Column name | Sample Data |
|---|---|
| id | 1 |
| time | 2020-07-29 21:54:36.176+00 |
| jenkins_job_id | 9 |
| git_branch_id | master |
| git_commit_id | 7fbb936e861cf1a926de9aea70dcde89ac8aefae |
| db_version | 1.0.0 |
| environment | { "cpu_number": "24", "cpu_socket": "true", "os_version": "Ubuntu 20.04 focal" } |
| metrics | { "binary_size_kb": 1234.342, "compile_time_sec": 130.0020, } |
The artifact stats do not have "test parameters" like the other metrics that we are gathering. Therefore, we only needed a metrics field that indiscriminately captures whichever key-value pair that the developer sends to it. Due to the restrictions on Grafana, the value should be a numeric value and the developer should indicate the units in the key name.