[SCHEMA CHANGE] Autobisection results store This CL contains schema for a new store used to keep autobisection results. This way we can support analytical queries like "what's the % of autobisects ignored per bot?". Using Prometheus for this is considered an anti-pattern: those queries are analytical, and prometheus doesn't work well with high cardinality. Additionally, this schema adds a new "anomaly_id" field in the ag.Anomaly proto, thanks to which we will later be able to save information about the anomaly we bisected. Bug: 513167043 Change-Id: I5619b6669fde24c452e7669d605cb664ec6ef288 Reviewed-on: https://skia-review.googlesource.com/c/buildbot/+/1235216 Commit-Queue: Marcin Mordecki <mordeckimarcin@google.com> Reviewed-by: Maxim Sheshukov <maximsheshukov@google.com>
diff --git a/perf/go/anomalygroup/proto/v1/anomalygroup_service.pb.go b/perf/go/anomalygroup/proto/v1/anomalygroup_service.pb.go index 6203d7a..0bcfb53 100644 --- a/perf/go/anomalygroup/proto/v1/anomalygroup_service.pb.go +++ b/perf/go/anomalygroup/proto/v1/anomalygroup_service.pb.go
@@ -912,7 +912,9 @@ // the median from the previous data point MedianBefore float32 `protobuf:"fixed32,5,opt,name=median_before,json=medianBefore,proto3" json:"median_before,omitempty"` // the median from the current data point - MedianAfter float32 `protobuf:"fixed32,6,opt,name=median_after,json=medianAfter,proto3" json:"median_after,omitempty"` + MedianAfter float32 `protobuf:"fixed32,6,opt,name=median_after,json=medianAfter,proto3" json:"median_after,omitempty"` + // anomaly id + Id string `protobuf:"bytes,7,opt,name=id,proto3" json:"id,omitempty"` unknownFields protoimpl.UnknownFields sizeCache protoimpl.SizeCache } @@ -989,6 +991,13 @@ return 0 } +func (x *Anomaly) GetId() string { + if x != nil { + return x.Id + } + return "" +} + var File_anomalygroup_service_proto protoreflect.FileDescriptor const file_anomalygroup_service_proto_rawDesc = "" + @@ -1047,7 +1056,7 @@ "\x11reported_issue_id\x18\x05 \x01(\x03R\x0freportedIssueId\x12)\n" + "\x10subsciption_name\x18\x06 \x01(\tR\x0fsubsciptionName\x123\n" + "\x15subscription_revision\x18\a \x01(\tR\x14subscriptionRevision\x12%\n" + - "\x0ebenchmark_name\x18\b \x01(\tR\rbenchmarkName\"\xc9\x02\n" + + "\x0ebenchmark_name\x18\b \x01(\tR\rbenchmarkName\"\xd9\x02\n" + "\aAnomaly\x12!\n" + "\fstart_commit\x18\x01 \x01(\x03R\vstartCommit\x12\x1d\n" + "\n" + @@ -1055,7 +1064,8 @@ "\bparamset\x18\x03 \x03(\v2&.anomalygroup.v1.Anomaly.ParamsetEntryR\bparamset\x123\n" + "\x15improvement_direction\x18\x04 \x01(\tR\x14improvementDirection\x12#\n" + "\rmedian_before\x18\x05 \x01(\x02R\fmedianBefore\x12!\n" + - "\fmedian_after\x18\x06 \x01(\x02R\vmedianAfter\x1a;\n" + + "\fmedian_after\x18\x06 \x01(\x02R\vmedianAfter\x12\x0e\n" + + "\x02id\x18\a \x01(\tR\x02id\x1a;\n" + "\rParamsetEntry\x12\x10\n" + "\x03key\x18\x01 \x01(\tR\x03key\x12\x14\n" + "\x05value\x18\x02 \x01(\tR\x05value:\x028\x01*7\n" +
diff --git a/perf/go/anomalygroup/proto/v1/anomalygroup_service.proto b/perf/go/anomalygroup/proto/v1/anomalygroup_service.proto index 7315900..4324b12 100644 --- a/perf/go/anomalygroup/proto/v1/anomalygroup_service.proto +++ b/perf/go/anomalygroup/proto/v1/anomalygroup_service.proto
@@ -202,6 +202,8 @@ float median_before = 5; // the median from the current data point float median_after = 6; + // anomaly id + string id = 7; }
diff --git a/perf/go/autobisection/sqlautobisectionstore/schema/BUILD.bazel b/perf/go/autobisection/sqlautobisectionstore/schema/BUILD.bazel new file mode 100644 index 0000000..b1a85ea --- /dev/null +++ b/perf/go/autobisection/sqlautobisectionstore/schema/BUILD.bazel
@@ -0,0 +1,8 @@ +load("@rules_go//go:def.bzl", "go_library") + +go_library( + name = "schema", + srcs = ["schema.go"], + importpath = "go.skia.org/infra/perf/go/autobisection/sqlautobisectionstore/schema", + visibility = ["//visibility:public"], +)
diff --git a/perf/go/autobisection/sqlautobisectionstore/schema/schema.go b/perf/go/autobisection/sqlautobisectionstore/schema/schema.go new file mode 100644 index 0000000..94fef29 --- /dev/null +++ b/perf/go/autobisection/sqlautobisectionstore/schema/schema.go
@@ -0,0 +1,16 @@ +package schema + +// AutobisectionSchema represents the SQL schema of the Autobisections table. +type AutobisectionSchema struct { + // The Pinpoint job ID. + JobID string `sql:"job_id TEXT PRIMARY KEY"` + + // Link to the Anomaly Group / Regression. + AnomalyGroupID string `sql:"anomaly_group_id TEXT"` + + // ID of the anomaly bisect operates on. + AnomalyId string `sql:"anomaly_id TEXT"` + + // Whether a culprit or regression was verified. + IsRealRegression bool `sql:"is_real_regression BOOL"` +}
diff --git a/perf/go/sql/BUILD.bazel b/perf/go/sql/BUILD.bazel index 72e7f99..a4430cd 100644 --- a/perf/go/sql/BUILD.bazel +++ b/perf/go/sql/BUILD.bazel
@@ -9,6 +9,7 @@ deps = [ "//perf/go/alerts/sqlalertstore/schema", "//perf/go/anomalygroup/sqlanomalygroupstore/schema", + "//perf/go/autobisection/sqlautobisectionstore/schema", "//perf/go/chromeperf/sqlreversekeymapstore/schema", "//perf/go/culprit/sqlculpritstore/schema", "//perf/go/favorites/sqlfavoritestore/schema",
diff --git a/perf/go/sql/expectedschema/migrate.go b/perf/go/sql/expectedschema/migrate.go index 2e79343..b0ed8d6 100644 --- a/perf/go/sql/expectedschema/migrate.go +++ b/perf/go/sql/expectedschema/migrate.go
@@ -41,17 +41,19 @@ // DO NOT DROP TABLES IN VAR BELOW. // FOR MODIFYING COLUMNS USE ADD/DROP COLUMN INSTEAD. var FromLiveToNextSpanner = ` - ALTER TABLE Regressions2 ADD COLUMN legacy_key TEXT; - CREATE INDEX IF NOT EXISTS by_legacy_key ON Regressions2(legacy_key); - ALTER TABLE RegressionsShortcuts ADD COLUMN is_legacy BOOLEAN DEFAULT FALSE; + CREATE TABLE IF NOT EXISTS Autobisections ( + job_id TEXT PRIMARY KEY, + anomaly_group_id TEXT, + anomaly_id TEXT, + is_real_regression BOOL, + createdat TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP + ); ` // ONLY DROP TABLE IF YOU JUST CREATED A NEW TABLE. // FOR MODIFYING COLUMNS USE ADD/DROP COLUMN INSTEAD. var FromNextToLiveSpanner = ` - ALTER TABLE RegressionsShortcuts DROP COLUMN is_legacy; - DROP INDEX IF EXISTS by_legacy_key; - ALTER TABLE Regressions2 DROP COLUMN legacy_key; + DROP TABLE IF EXISTS Autobisections; ` // This function will check whether there's a new schema checked-in,
diff --git a/perf/go/sql/expectedschema/schema_prev_spanner.json b/perf/go/sql/expectedschema/schema_prev_spanner.json index 6bd880b..b348454 100644 --- a/perf/go/sql/expectedschema/schema_prev_spanner.json +++ b/perf/go/sql/expectedschema/schema_prev_spanner.json
@@ -76,6 +76,7 @@ "regressions2.frame": "jsonb def: nullable:YES", "regressions2.id": "character varying def:spanner.generate_uuid() nullable:NO", "regressions2.is_improvement": "boolean def: nullable:YES", + "regressions2.legacy_key": "character varying def: nullable:YES", "regressions2.median_after": "real def: nullable:YES", "regressions2.median_before": "real def: nullable:YES", "regressions2.prev_commit_number": "bigint def: nullable:YES", @@ -85,6 +86,7 @@ "regressions2.triage_status": "character varying def: nullable:YES", "regressionsshortcuts.anomaly_ids": "ARRAY def: nullable:YES", "regressionsshortcuts.createdat": "timestamp with time zone def:CURRENT_TIMESTAMP nullable:YES", + "regressionsshortcuts.is_legacy": "boolean def:false nullable:YES", "regressionsshortcuts.sid": "character varying def: nullable:NO", "reversekeymap.createdat": "timestamp with time zone def:CURRENT_TIMESTAMP nullable:YES", "reversekeymap.modified_value": "character varying def: nullable:NO", @@ -156,6 +158,7 @@ "regressions2.by_trace_id_and_commit", "regressions2.by_sub_name_triage_status_creation_time_asc", "regressions2.by_sub_name_creation_time", + "regressions2.by_legacy_key", "regressions2.by_commit_and_prev_commit", "regressions2.by_commit_alert", "regressions2.by_alert_id",
diff --git a/perf/go/sql/expectedschema/schema_spanner.json b/perf/go/sql/expectedschema/schema_spanner.json index b348454..0a3af3a 100644 --- a/perf/go/sql/expectedschema/schema_spanner.json +++ b/perf/go/sql/expectedschema/schema_spanner.json
@@ -20,6 +20,11 @@ "anomalygroups.id": "character varying def:spanner.generate_uuid() nullable:NO", "anomalygroups.last_modified_time": "timestamp with time zone def: nullable:YES", "anomalygroups.reported_issue_id": "character varying def: nullable:YES", + "autobisections.anomaly_group_id": "character varying def: nullable:YES", + "autobisections.anomaly_id": "character varying def: nullable:YES", + "autobisections.createdat": "timestamp with time zone def:CURRENT_TIMESTAMP nullable:YES", + "autobisections.is_real_regression": "boolean def: nullable:YES", + "autobisections.job_id": "character varying def: nullable:NO", "commits.author": "character varying def: nullable:YES", "commits.commit_number": "bigint def: nullable:NO", "commits.commit_time": "bigint def: nullable:YES", @@ -140,6 +145,7 @@ "alerts.idx_alerts_subname", "alerts.PRIMARY_KEY", "anomalygroups.PRIMARY_KEY", + "autobisections.PRIMARY_KEY", "commits.PRIMARY_KEY", "culprits.by_revision", "culprits.PRIMARY_KEY",
diff --git a/perf/go/sql/spanner/schema_spanner.go b/perf/go/sql/spanner/schema_spanner.go index 823e540..bc70808 100644 --- a/perf/go/sql/spanner/schema_spanner.go +++ b/perf/go/sql/spanner/schema_spanner.go
@@ -30,6 +30,13 @@ last_modified_time TIMESTAMPTZ, createdat TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ) TTL INTERVAL '1095 days' ON createdat; +CREATE TABLE IF NOT EXISTS Autobisections ( + job_id TEXT PRIMARY KEY, + anomaly_group_id TEXT, + anomaly_id TEXT, + is_real_regression BOOL, + createdat TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP +) TTL INTERVAL '1095 days' ON createdat; CREATE TABLE IF NOT EXISTS Commits ( commit_number INT PRIMARY KEY, git_hash TEXT NOT NULL, @@ -235,6 +242,13 @@ "last_modified_time", } +var Autobisections = []string{ + "job_id", + "anomaly_group_id", + "anomaly_id", + "is_real_regression", +} + var Commits = []string{ "commit_number", "git_hash",
diff --git a/perf/go/sql/sql_test.go b/perf/go/sql/sql_test.go index 8aec7d8..5f73c8a 100644 --- a/perf/go/sql/sql_test.go +++ b/perf/go/sql/sql_test.go
@@ -17,6 +17,7 @@ const DropTables = ` DROP TABLE IF EXISTS Alerts; DROP TABLE IF EXISTS AnomalyGroups; + DROP TABLE IF EXISTS Autobisections; DROP TABLE IF EXISTS Commits; DROP TABLE IF EXISTS Culprits; DROP TABLE IF EXISTS Favorites; @@ -167,6 +168,7 @@ cluster_type TEXT, cluster_summary JSONB, frame JSONB, + legacy_key TEXT, sub_name TEXT, trace_id BYTEA, triage_status TEXT, @@ -176,6 +178,7 @@ CREATE TABLE RegressionsShortcuts ( sid TEXT PRIMARY KEY, anomaly_ids TEXT ARRAY, + is_legacy BOOL DEFAULT FALSE, createdat timestamp WITH time zone DEFAULT CURRENT_TIMESTAMP ) TTL INTERVAL '1095 days' ON createdat; CREATE TABLE IF NOT EXISTS ReverseKeyMap ( @@ -257,6 +260,7 @@ CREATE INDEX IF NOT EXISTS by_alert_id on Regressions2 (alert_id); CREATE INDEX IF NOT EXISTS by_commit_alert on Regressions2 (commit_number, alert_id); CREATE INDEX IF NOT EXISTS by_commit_and_prev_commit on Regressions2 (commit_number, prev_commit_number); +CREATE INDEX IF NOT EXISTS by_legacy_key on Regressions2 (legacy_key); CREATE INDEX IF NOT EXISTS by_sub_name_creation_time on Regressions2 (sub_name, creation_time DESC); CREATE INDEX IF NOT EXISTS by_sub_name_triage_status_creation_time_asc on Regressions2 (sub_name, triage_status, creation_time ASC); CREATE INDEX IF NOT EXISTS by_trace_id_and_commit on Regressions2 (trace_id, commit_number);
diff --git a/perf/go/sql/tables.go b/perf/go/sql/tables.go index 7fe486d..a2f03b1 100644 --- a/perf/go/sql/tables.go +++ b/perf/go/sql/tables.go
@@ -5,6 +5,7 @@ import ( alertschema "go.skia.org/infra/perf/go/alerts/sqlalertstore/schema" anomalygroupschema "go.skia.org/infra/perf/go/anomalygroup/sqlanomalygroupstore/schema" + autobisectionschema "go.skia.org/infra/perf/go/autobisection/sqlautobisectionstore/schema" reversekeymapschema "go.skia.org/infra/perf/go/chromeperf/sqlreversekeymapstore/schema" culpritschema "go.skia.org/infra/perf/go/culprit/sqlculpritstore/schema" favoriteschema "go.skia.org/infra/perf/go/favorites/sqlfavoritestore/schema" @@ -24,6 +25,7 @@ type Tables struct { Alerts []alertschema.AlertSchema AnomalyGroups []anomalygroupschema.AnomalyGroupSchema + Autobisections []autobisectionschema.AutobisectionSchema Commits []gitschema.Commit Culprits []culpritschema.CulpritSchema Favorites []favoriteschema.FavoriteSchema