[perf] New schema wip.
Change-Id: I2d46299df6fd76de572407d468113d42d9e032aa
Reviewed-on: https://skia-review.googlesource.com/c/buildbot/+/306194
Reviewed-by: Kevin Lubick <kjlubick@google.com>
diff --git a/perf/migrations/cdb.sql b/perf/migrations/cdb.sql
index ded8df8..cfd8b76 100644
--- a/perf/migrations/cdb.sql
+++ b/perf/migrations/cdb.sql
@@ -9,131 +9,144 @@
--
-- You should be able to run this file against the same database more than once
-- w/o error.
-
-UPSERT INTO Commits (commit_number, git_hash, commit_time, author, subject)
+UPSERT INTO TraceNames (trace_id, params)
VALUES
- (0, '586101c79b0490b50623e76c71a5fd67d8d92b08', 1158764756, 'unknown@example.com', 'initial directory structure'),
- (1, '0f87cd842dd46205d5252c35da6d2c869f3d2e98', 1158767262, 'unknown@example.com', 'initial code checkin'),
- (2, '48ede9b432a3c3d62835a1400a9ed347b4a93024', 1163013888, 'unknown@example.org', 'Add LICENSE');
+ (
+ '\xfe385b159ff55dca481069805e5ff050',
+ '{"arch": "x86", "config":"8888"}'
+ ),
+ (
+ '\x277262a9236d571883d47dab102070bc',
+ '{"arch":"x86", "config": "565"}'
+ ),
+ (
+ '\x0f17700460ee99c6488c2f6130804de5',
+ '{"arch":"arm", "config":"8888"}'
+ ),
+ (
+ '\x6a5622e86c6059d74373f6a79df96054',
+ '{"arch":"arm", "config":"565"}'
+ ),
+ (
+ '\x0d1f35f01672b2105bbc3f19adfcef67',
+ '{"arch":"riscv", "config":"565"}'
+ );
--- Get most recent git hash.
-SELECT git_hash FROM Commits
-ORDER BY commit_number DESC
-LIMIT 1;
-
--- Get commit_number from git hash.
-SELECT commit_number FROM Commits
-WHERE git_hash='0f87cd842dd46205d5252c35da6d2c869f3d2e98';
-
--- Get commit_number from time.
-SELECT commit_number FROM Commits
-WHERE commit_time <= 1163013888
-ORDER BY commit_number DESC
-LIMIT 1;
-
-INSERT INTO SourceFiles (source_file, source_file_id)
+UPSERT INTO TraceValues2 (trace_id, commit_number, val, source_file_id)
VALUES
- ('gs://perf-bucket/2020/02/08/11/testdata.json',1),
- ('gs://perf-bucket/2020/02/08/12/testdata.json',2),
- ('gs://perf-bucket/2020/02/08/13/testdata.json',3),
- ('gs://perf-bucket/2020/02/08/14/testdata.json',4)
-ON CONFLICT
-DO NOTHING;
-
--- INSERT ON CONFLICT RETURNING doesn't work as needed
--- because the below query doesnt' return anything because it
--- stops at DO NOTHING.
---
--- INSERT INTO SourceFiles (source_file)
--- VALUES
--- ('gs://perf-bucket/2020/02/08/11/testdata.json')
--- ON CONFLICT
--- DO NOTHING
--- RETURNING source_file_id;
-
-INSERT INTO TraceIDs (trace_name, trace_id)
-VALUES
- (',arch=x86,config=8888,', 1),
- (',arch=x86,config=565,', 2),
- (',arch=arm,config=8888,',3),
- (',arch=arm,config=565,', 4)
-ON CONFLICT
-DO NOTHING;
-
-SELECT trace_id, trace_name FROM TraceIDs;
-
-UPSERT INTO TraceValues (trace_id, commit_number, val, source_file_id)
-VALUES
- (1, 1, 1.2, 1),
- (1, 2, 1.3, 2),
- (1, 3, 1.4, 3),
- (1, 256, 1.1, 4),
- (2, 1, 2.2, 1),
- (2, 2, 2.3, 2),
- (2, 3, 2.4, 3),
- (2, 256, 2.1, 4);
-
-UPSERT INTO Postings (tile_number, key_value, trace_id)
-VALUES
- (2, 'config=565', 4),
- (0, 'arch=x86', 1),
- (0, 'arch=x86', 2),
- (0, 'arch=arm', 3),
- (0, 'arch=arm', 4),
- (0, 'config=8888', 1),
- (0, 'config=8888', 3),
- (0, 'config=565', 2),
- (0, 'config=565', 4);
+ ('\xfe385b159ff55dca481069805e5ff050', 1, 1.2, 1),
+ ('\xfe385b159ff55dca481069805e5ff050', 2, 1.3, 2),
+ ('\xfe385b159ff55dca481069805e5ff050', 3, 1.4, 3),
+ (
+ '\x0d1f35f01672b2105bbc3f19adfcef67',
+ 256,
+ 1.1,
+ 4
+ ),
+ ('\x277262a9236d571883d47dab102070bc', 1, 2.2, 1),
+ ('\x277262a9236d571883d47dab102070bc', 2, 2.3, 2),
+ ('\x277262a9236d571883d47dab102070bc', 3, 2.4, 3),
+ (
+ '\x0d1f35f01672b2105bbc3f19adfcef67',
+ 256,
+ 2.1,
+ 4
+ );
-- All trace_ids that match a particular key=value.
-SELECT tile_number, key_value, trace_id FROM Postings
-WHERE tile_number=0 AND key_value='arch=x86'
-ORDER BY trace_id;
-
--- Retrieve matching values. Note that sqlite querys are limited to 1MB,
--- so we might need to break up the trace_ids if the query is too long.
-SELECT trace_id, commit_number, val FROM TraceValues
-WHERE commit_number>=0 AND commit_number<255 AND trace_id IN (1,2);
-
--- Build traces using a JOIN.
-SELECT TraceIDs.trace_name, TraceValues.commit_number, TraceValues.val FROM TraceIDs
-INNER JOIN TraceValues ON TraceValues.trace_id = TraceIDs.trace_id
-WHERE TraceIDs.trace_name=',arch=x86,config=8888,' OR TraceIDs.trace_name=',arch=x86,config=565,';
-
--- Retrieve source file.
-SELECT source_file_id, source_file from SourceFiles
-WHERE source_file_id=1;
-
-SELECT DISTINCT key_value FROM Postings
-WHERE tile_number=0;
-
--- Most recent tile.
-SELECT tile_number FROM Postings ORDER BY tile_number DESC LIMIT 1;
-
--- Count indices for Tile.
-SELECT COUNT(*) FROM Postings WHERE tile_number=0;
-
--- GetSource by trace name.
-SELECT SourceFiles.source_file FROM TraceIDs
-INNER JOIN TraceValues ON TraceValues.trace_id = TraceIDs.trace_id
-INNER JOIN SourceFiles ON SourceFiles.source_file_id = TraceValues.source_file_id
-WHERE TraceIDs.trace_name=',arch=x86,config=8888,' AND TraceValues.commit_number=256;
-
--- Fully query traces from tile based on query plan.
-SELECT TraceIDs.trace_name, TraceValues.commit_number, TraceValues.val FROM TraceIDs
-INNER JOIN TraceValues ON TraceValues.trace_id = TraceIDs.trace_id
+SELECT
+ encode(trace_id, 'hex'),
+ params
+FROM
+ TraceNames
WHERE
- TraceValues.trace_id IN (
- SELECT trace_id FROM Postings WHERE key_value IN ('arch=x86', 'arch=arm')
- AND tile_number=0
- )
- AND TraceValues.trace_id IN (
- SELECT trace_id FROM Postings WHERE key_value IN ('config=8888')
- AND tile_number=0
- );
+ params ->> 'arch' IN ('x86');
+
+-- Retrieve matching values.
+SELECT
+ encode(trace_id, 'hex'),
+ commit_number,
+ val
+FROM
+ TraceValues2
+WHERE
+ commit_number >= 0
+ AND commit_number < 255
+ AND trace_id IN (
+ '\xfe385b159ff55dca481069805e5ff050',
+ '\x277262a9236d571883d47dab102070bc'
+ );
+
+-- Compound queries.
+SELECT
+ params
+FROM
+ TraceNames
+WHERE
+ params ->> 'arch' IN ('x86', 'arm')
+ AND params ->> 'config' IN ('8888');
+
+-- ParamSet for two Tiles
+SELECT
+ DISTINCT TraceNames.params
+FROM
+ TraceNames
+ INNER JOIN TraceValues2 ON TraceNames.trace_id = TraceValues2.trace_id
+WHERE
+ TraceValues2.commit_number >= 0
+ AND TraceValues2.commit_number < 512;
-- Count the number traces that are in a single tile.
-SELECT COUNT(DISTINCT trace_id) FROM TraceValues
+SELECT
+ COUNT(DISTINCT trace_id)
+FROM
+ TraceValues2
WHERE
- commit_number > 0
- AND commit_number < 8;
+ commit_number > 0
+ AND commit_number < 256;
+
+-- Most recent commit.
+SELECT
+ commit_number
+FROM
+ TraceValues2
+ORDER BY
+ commit_number DESC
+LIMIT
+ 1;
+
+-- GetSource by trace_id.
+SELECT
+ SourceFiles.source_file
+FROM
+ TraceNames
+ INNER JOIN TraceValues2 ON TraceValues2.trace_id = TraceNames.trace_id
+ INNER JOIN SourceFiles ON SourceFiles.source_file_id = TraceValues2.source_file_id
+WHERE
+ TraceNames.trace_id = '\xfe385b159ff55dca481069805e5ff050'
+ AND TraceValues2.commit_number = 3;
+
+-- Count the number of matches to a query.
+SELECT
+ COUNT(*)
+FROM
+ TraceNames
+ INNER JOIN TraceValues2 ON TraceNames.trace_id = TraceValues2.trace_id
+WHERE
+ TraceNames.params ->> 'arch' IN ('riscv')
+ AND TraceValues2.commit_number >= 256
+ AND TraceValues2.commit_number < 512;
+
+-- Fully query traces from tile based on query plan w/o the sub-select.
+SELECT
+ TraceNames.params,
+ TraceValues2.commit_number,
+ TraceValues2.val
+FROM
+ TraceNames
+ INNER JOIN TraceValues2 ON TraceValues2.trace_id = TraceNames.trace_id
+WHERE
+ TraceNames.params ->> 'arch' IN ('x86')
+ AND TraceNames.params ->> 'config' IN ('565', '8888')
+ AND TraceValues2.commit_number >= 0
+ AND TraceValues2.commit_number < 255;
\ No newline at end of file
diff --git a/perf/migrations/cockroachdb/0002_create_initial_tables.down.sql b/perf/migrations/cockroachdb/0002_create_initial_tables.down.sql
new file mode 100644
index 0000000..078a7e0
--- /dev/null
+++ b/perf/migrations/cockroachdb/0002_create_initial_tables.down.sql
@@ -0,0 +1,2 @@
+DROP TABLE TraceNames;
+DROP TABLE TraceValues2;
\ No newline at end of file
diff --git a/perf/migrations/cockroachdb/0002_create_initial_tables.up.sql b/perf/migrations/cockroachdb/0002_create_initial_tables.up.sql
new file mode 100644
index 0000000..1614a69
--- /dev/null
+++ b/perf/migrations/cockroachdb/0002_create_initial_tables.up.sql
@@ -0,0 +1,21 @@
+-- This table is used to store trace names. See go/tracestore/sqltracestore.
+CREATE TABLE IF NOT EXISTS TraceNames (
+ -- md5(trace_name)
+ trace_id BYTES PRIMARY KEY,
+ -- The params that make up the trace_id, {"arch=x86", "config=8888"}.
+ params JSONB NOT NULL,
+ INVERTED INDEX (params)
+);
+
+-- This table is used to store trace values. See go/tracestore/sqltracestore.
+CREATE TABLE IF NOT EXISTS TraceValues2 (
+ -- Id of the trace name from TraceNames.
+ trace_id BYTES,
+ -- A types.CommitNumber.
+ commit_number INT,
+ -- The floating point measurement.
+ val REAL,
+ -- Id of the source filename, from SourceFiles.
+ source_file_id INT,
+ PRIMARY KEY (trace_id, commit_number)
+);
\ No newline at end of file
