[builder_oracle] Track Caviums as a limited resource
Caviums are high memory bots in our pool that we allocate to particular
tasks and have a lead time to scale up, so we should track them in
addition to other inelastic resources.
Bug: 94775
Change-Id: I3df39fdfcf50af662a2ead8ec3936fc79c949c0a
Reviewed-on: https://fuchsia-review.googlesource.com/c/infra/infra/+/653166
Reviewed-by: Yuping Zhai <yupingz@google.com>
Commit-Queue: Catherine Duncan <catduncan@google.com>
diff --git a/cmd/builder_oracle/queries.go b/cmd/builder_oracle/queries.go
index 28dc9e1..7b82d71 100644
--- a/cmd/builder_oracle/queries.go
+++ b/cmd/builder_oracle/queries.go
@@ -6,6 +6,48 @@
import "time"
+// Helper functions that can be prepended to queries to reduce duplicated code
+var helperFunctions string = `
+CREATE TEMP FUNCTION KEY_EXISTS(t ANY TYPE, k STRING)
+RETURNS BOOL
+AS (
+ EXISTS(SELECT 1 from UNNEST(t) WHERE key = k)
+);
+
+CREATE TEMP FUNCTION GET_VALUE(t ANY TYPE, k STRING)
+AS (
+ (SELECT values FROM UNNEST(t) WHERE key = k LIMIT 1)[OFFSET(0)]
+);
+
+
+CREATE TEMP FUNCTION TO_MACHINE(t ANY TYPE)
+RETURNS STRING
+AS (
+ (
+ SELECT
+ CASE
+ WHEN
+ KEY_EXISTS(t.bot.dimensions, 'device_type')
+ THEN
+ GET_VALUE(t.bot.dimensions, 'device_type')
+ WHEN
+ GET_VALUE(t.bot.dimensions, 'machine_type')
+ = 'n1-highmem-8'
+ THEN 'Cavium'
+ WHEN
+ KEY_EXISTS(t.bot.dimensions, 'gce') AND GET_VALUE(t.bot.dimensions, 'gce')='1'
+ THEN 'gce'
+ WHEN
+ KEY_EXISTS(t.bot.dimensions, 'os')
+ THEN
+ GET_VALUE(t.bot.dimensions, 'os')
+ ELSE 'Misc'
+ END
+ FROM (SELECT 1 AS foo)
+ )
+);
+`
+
// Fetches an hour with the highest count of builds to approximate a point in time where
// we have the most demand on physical devices
@@ -21,8 +63,7 @@
AND create_time > TIMESTAMP_SUB(
TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY, 'America/Los_Angeles'),
INTERVAL 30 DAY)
- --- between 9 and 6 PST
- AND EXTRACT(HOUR FROM start_time) IN (1, 17, 18, 19, 20, 21, 22, 23, 24)
+ AND EXTRACT(HOUR FROM start_time AT TIME ZONE 'America/Los_Angeles') IN (9, 10, 11, 12, 13, 14, 15, 16, 17)
GROUP BY hour
ORDER BY builds DESC
LIMIT 1
@@ -57,32 +98,17 @@
// Fetches counts of distinct machine ids during specified timeframe to approximate available capacity
-var botCountQuery string = `
-SELECT device_type, bot_count
-FROM
- (
- SELECT
- (
- SELECT
- values
- FROM
- UNNEST(bot.dimensions)
- WHERE
- key = 'device_type'
- LIMIT 1
- )[OFFSET(0)]
- AS device_type,
- COUNT(DISTINCT bot.bot_id) AS bot_count
- FROM chrome-swarming.swarming.bot_events
- WHERE
- event_time >= TIMESTAMP("%s")
- AND event_time <= TIMESTAMP("%s")
- AND ARRAY_LENGTH(bot.pools) != 0
- AND bot.pools[OFFSET(0)] LIKE 'fuchsia.tests%%'
- GROUP BY device_type
- )
-WHERE device_type IS NOT NULL
-
+var botCountQuery string = helperFunctions + `
+SELECT
+ TO_MACHINE(r) AS device_type,
+ COUNT(DISTINCT r.bot.bot_id) AS bot_count
+FROM chrome-swarming.swarming.bot_events AS r
+WHERE
+ r.event_time >= TIMESTAMP('%s')
+ AND r.event_time <= TIMESTAMP('%s')
+ AND ARRAY_LENGTH(r.bot.pools) != 0
+ AND r.bot.pools[OFFSET(0)] LIKE 'fuchsia.tests%%'
+GROUP BY device_type
`
// Result object for botCountQuery
@@ -94,14 +120,7 @@
// Fetches builder runs that ran on physical devices.
-var testingTasksQuery string = `
-CREATE TEMP FUNCTION array_fetch(
- bucket ANY TYPE, lookup_key STRING)
-RETURNS STRING
-AS (
- (SELECT values FROM UNNEST(bucket) WHERE key = lookup_key LIMIT 1)[OFFSET(0)]
-);
-
+var testingTasksQuery string = helperFunctions + `
WITH
build_info AS (
SELECT
@@ -119,35 +138,21 @@
WHERE
b.create_time >= TIMESTAMP('%s')
AND b.create_time <= TIMESTAMP('%s')
- AND EXTRACT(HOUR FROM b.create_time)
- IN (1, 17, 18, 19, 20, 21, 22, 23, 24)
- AND EXTRACT(DAYOFWEEK FROM b.create_time) IN (2, 3, 4, 5, 6)
+ AND EXTRACT(HOUR FROM b.create_time AT TIME ZONE 'America/Los_Angeles') IN (9, 10, 11, 12, 13, 14, 15, 16, 17)
+ AND EXTRACT(DAYOFWEEK FROM b.create_time AT TIME ZONE 'America/Los_Angeles') IN (2, 3, 4, 5, 6)
),
task_info_internal AS (
SELECT
TIMESTAMP_DIFF(c.end_time, c.start_time, MICROSECOND) * 1000
AS duration_nanos,
request.root_task_id,
- CASE
- WHEN
- array_fetch(
- c.request.task_slices[OFFSET(0)].properties.dimensions,
- 'device_type')
- IS NULL
- THEN 'gce'
- ELSE
- array_fetch(
- c.request.task_slices[OFFSET(0)].properties.dimensions,
- 'device_type')
- END
- AS device_type
+ TO_MACHINE(c) AS device_type
FROM chrome-swarming.swarming.task_results_summary AS c
WHERE
c.create_time >= TIMESTAMP('%s')
AND c.create_time <= TIMESTAMP('%s')
- AND EXTRACT(HOUR FROM c.create_time)
- IN (1, 17, 18, 19, 20, 21, 22, 23, 24)
- AND EXTRACT(DAYOFWEEK FROM c.create_time) IN (2, 3, 4, 5, 6)
+ AND EXTRACT(HOUR FROM c.create_time AT TIME ZONE 'America/Los_Angeles') IN (9, 10, 11, 12, 13, 14, 15, 16, 17)
+ AND EXTRACT(DAYOFWEEK FROM c.create_time AT TIME ZONE 'America/Los_Angeles') IN (2, 3, 4, 5, 6)
AND c.bot.pools[OFFSET(0)] LIKE 'fuchsia.tests%%'
),
device_testing_tasks AS (
