Skip to content
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
91 changes: 55 additions & 36 deletions coderd/database/dbgen/dbgen.go
Original file line number Diff line number Diff line change
Expand Up @@ -1714,55 +1714,74 @@ func TemplateVersionTerraformValues(t testing.TB, db database.Store, orig databa
return v
}

// WorkspaceAgentStat inserts a workspace agent stat row. Seed the
// session_counts column by setting SessionCounts on orig, for example with
// the SessionCounts helper:
//
// WorkspaceAgentStat(t, db, database.WorkspaceAgentStat{
// SessionCounts: dbgen.SessionCounts(t, map[string]int64{"ssh": 1}),
// })
func WorkspaceAgentStat(t testing.TB, db database.Store, orig database.WorkspaceAgentStat) database.WorkspaceAgentStat {
if orig.ConnectionsByProto == nil {
orig.ConnectionsByProto = json.RawMessage([]byte("{}"))
}
jsonProto := []byte(fmt.Sprintf("[%s]", orig.ConnectionsByProto))

// The insert rejects null session count elements.
jsonCounts := orig.SessionCounts
if jsonCounts == nil {
jsonCounts = json.RawMessage("{}")
}

params := database.InsertWorkspaceAgentStatsParams{
ID: []uuid.UUID{takeFirst(orig.ID, uuid.New())},
CreatedAt: []time.Time{takeFirst(orig.CreatedAt, dbtime.Now())},
UserID: []uuid.UUID{takeFirst(orig.UserID, uuid.New())},
TemplateID: []uuid.UUID{takeFirst(orig.TemplateID, uuid.New())},
WorkspaceID: []uuid.UUID{takeFirst(orig.WorkspaceID, uuid.New())},
AgentID: []uuid.UUID{takeFirst(orig.AgentID, uuid.New())},
ConnectionsByProto: jsonProto,
ConnectionCount: []int64{takeFirst(orig.ConnectionCount, 0)},
RxPackets: []int64{takeFirst(orig.RxPackets, 0)},
RxBytes: []int64{takeFirst(orig.RxBytes, 0)},
TxPackets: []int64{takeFirst(orig.TxPackets, 0)},
TxBytes: []int64{takeFirst(orig.TxBytes, 0)},
SessionCountVSCode: []int64{takeFirst(orig.SessionCountVSCode, 0)},
SessionCountJetBrains: []int64{takeFirst(orig.SessionCountJetBrains, 0)},
SessionCountReconnectingPTY: []int64{takeFirst(orig.SessionCountReconnectingPTY, 0)},
SessionCountSSH: []int64{takeFirst(orig.SessionCountSSH, 0)},
ConnectionMedianLatencyMS: []float64{takeFirst(orig.ConnectionMedianLatencyMS, 0)},
Usage: []bool{takeFirst(orig.Usage, false)},
ID: []uuid.UUID{takeFirst(orig.ID, uuid.New())},
CreatedAt: []time.Time{takeFirst(orig.CreatedAt, dbtime.Now())},
UserID: []uuid.UUID{takeFirst(orig.UserID, uuid.New())},
TemplateID: []uuid.UUID{takeFirst(orig.TemplateID, uuid.New())},
WorkspaceID: []uuid.UUID{takeFirst(orig.WorkspaceID, uuid.New())},
AgentID: []uuid.UUID{takeFirst(orig.AgentID, uuid.New())},
ConnectionsByProto: jsonProto,
ConnectionCount: []int64{takeFirst(orig.ConnectionCount, 0)},
RxPackets: []int64{takeFirst(orig.RxPackets, 0)},
RxBytes: []int64{takeFirst(orig.RxBytes, 0)},
TxPackets: []int64{takeFirst(orig.TxPackets, 0)},
TxBytes: []int64{takeFirst(orig.TxBytes, 0)},
SessionCounts: json.RawMessage(fmt.Sprintf("[%s]", jsonCounts)),
ConnectionMedianLatencyMS: []float64{takeFirst(orig.ConnectionMedianLatencyMS, 0)},
Usage: []bool{takeFirst(orig.Usage, false)},
}
err := db.InsertWorkspaceAgentStats(genCtx, params)
require.NoError(t, err, "insert workspace agent stat")

return database.WorkspaceAgentStat{
ID: params.ID[0],
CreatedAt: params.CreatedAt[0],
UserID: params.UserID[0],
AgentID: params.AgentID[0],
WorkspaceID: params.WorkspaceID[0],
TemplateID: params.TemplateID[0],
ConnectionsByProto: orig.ConnectionsByProto,
ConnectionCount: params.ConnectionCount[0],
RxPackets: params.RxPackets[0],
RxBytes: params.RxBytes[0],
TxPackets: params.TxPackets[0],
TxBytes: params.TxBytes[0],
ConnectionMedianLatencyMS: params.ConnectionMedianLatencyMS[0],
SessionCountVSCode: params.SessionCountVSCode[0],
SessionCountJetBrains: params.SessionCountJetBrains[0],
SessionCountReconnectingPTY: params.SessionCountReconnectingPTY[0],
SessionCountSSH: params.SessionCountSSH[0],
Usage: params.Usage[0],
ID: params.ID[0],
CreatedAt: params.CreatedAt[0],
UserID: params.UserID[0],
AgentID: params.AgentID[0],
WorkspaceID: params.WorkspaceID[0],
TemplateID: params.TemplateID[0],
ConnectionsByProto: orig.ConnectionsByProto,
ConnectionCount: params.ConnectionCount[0],
RxPackets: params.RxPackets[0],
RxBytes: params.RxBytes[0],
TxPackets: params.TxPackets[0],
TxBytes: params.TxBytes[0],
ConnectionMedianLatencyMS: params.ConnectionMedianLatencyMS[0],
Usage: params.Usage[0],
SessionCounts: jsonCounts,
}
}

// SessionCounts marshals counts for the SessionCounts seed field of
// WorkspaceAgentStat.
func SessionCounts(t testing.TB, counts map[string]int64) json.RawMessage {
t.Helper()
if counts == nil {
counts = map[string]int64{}
}
raw, err := json.Marshal(counts)
require.NoError(t, err, "marshal session counts")
return raw
}

func OAuth2ProviderApp(t testing.TB, db database.Store, seed database.OAuth2ProviderApp) database.OAuth2ProviderApp {
Expand Down
6 changes: 3 additions & 3 deletions coderd/database/dbpurge/dbpurge_test.go
Original file line number Diff line number Diff line change
Expand Up @@ -392,7 +392,7 @@ func TestDeleteOldWorkspaceAgentStats(t *testing.T) {
ConnectionCount: 1,
ConnectionMedianLatencyMS: 1,
RxBytes: 1111,
SessionCountSSH: 1,
SessionCounts: dbgen.SessionCounts(t, map[string]int64{"ssh": 1}),
})

// Stat inserted 180 days - 2 hour ago, should not be deleted before rollup.
Expand All @@ -401,7 +401,7 @@ func TestDeleteOldWorkspaceAgentStats(t *testing.T) {
ConnectionCount: 1,
ConnectionMedianLatencyMS: 1,
RxBytes: 2222,
SessionCountSSH: 1,
SessionCounts: dbgen.SessionCounts(t, map[string]int64{"ssh": 1}),
})

// Stat inserted 179 days - 4 hour ago, should not be deleted at all.
Expand All @@ -410,7 +410,7 @@ func TestDeleteOldWorkspaceAgentStats(t *testing.T) {
ConnectionCount: 1,
ConnectionMedianLatencyMS: 1,
RxBytes: 3333,
SessionCountSSH: 1,
SessionCounts: dbgen.SessionCounts(t, map[string]int64{"ssh": 1}),
})

// when
Expand Down
36 changes: 18 additions & 18 deletions coderd/database/dbrollup/dbrollup_test.go
Original file line number Diff line number Diff line change
Expand Up @@ -75,7 +75,7 @@ func TestRollup_TwoInstancesUseLocking(t *testing.T) {
CreatedAt: refTime.Add(-time.Minute),
ConnectionMedianLatencyMS: 1,
ConnectionCount: 1,
SessionCountSSH: 1,
SessionCounts: dbgen.SessionCounts(t, map[string]int64{"ssh": 1}),
})

closeRolluper := func(rolluper *dbrollup.Rolluper, resume chan struct{}) {
Expand Down Expand Up @@ -163,7 +163,7 @@ func TestRollupTemplateUsageStats(t *testing.T) {
CreatedAt: anHourAndSixMonthsAgo.AddDate(0, 0, -1),
ConnectionMedianLatencyMS: 1,
ConnectionCount: 1,
SessionCountSSH: 1,
SessionCounts: dbgen.SessionCounts(t, map[string]int64{"ssh": 1}),
})
_ = dbgen.WorkspaceAppStat(t, db, database.WorkspaceAppStat{
UserID: user.ID,
Expand All @@ -176,24 +176,24 @@ func TestRollupTemplateUsageStats(t *testing.T) {

// Stats inserted 6 months - 1 day ago, should be rolled up.
wags1 := dbgen.WorkspaceAgentStat(t, db, database.WorkspaceAgentStat{
TemplateID: tpl.ID,
WorkspaceID: ws.ID,
AgentID: agent.ID,
UserID: user.ID,
CreatedAt: anHourAndSixMonthsAgo.AddDate(0, 0, 1),
ConnectionMedianLatencyMS: 1,
ConnectionCount: 1,
SessionCountReconnectingPTY: 1,
TemplateID: tpl.ID,
WorkspaceID: ws.ID,
AgentID: agent.ID,
UserID: user.ID,
CreatedAt: anHourAndSixMonthsAgo.AddDate(0, 0, 1),
ConnectionMedianLatencyMS: 1,
ConnectionCount: 1,
SessionCounts: dbgen.SessionCounts(t, map[string]int64{"reconnecting_pty": 1}),
})
wags2 := dbgen.WorkspaceAgentStat(t, db, database.WorkspaceAgentStat{
TemplateID: tpl.ID,
WorkspaceID: ws.ID,
AgentID: agent.ID,
UserID: user.ID,
CreatedAt: wags1.CreatedAt.Add(time.Minute),
ConnectionMedianLatencyMS: 1,
ConnectionCount: 1,
SessionCountReconnectingPTY: 1,
TemplateID: tpl.ID,
WorkspaceID: ws.ID,
AgentID: agent.ID,
UserID: user.ID,
CreatedAt: wags1.CreatedAt.Add(time.Minute),
ConnectionMedianLatencyMS: 1,
ConnectionCount: 1,
SessionCounts: dbgen.SessionCounts(t, map[string]int64{"reconnecting_pty": 1}),
})
// wags2 and waps1 overlap, so total usage is 4 - 1.
waps1 := dbgen.WorkspaceAppStat(t, db, database.WorkspaceAppStat{
Expand Down
11 changes: 5 additions & 6 deletions coderd/database/dump.sql

Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.

Original file line number Diff line number Diff line change
@@ -0,0 +1,23 @@
ALTER TABLE workspace_agent_stats
ADD COLUMN session_count_vscode bigint DEFAULT 0 NOT NULL,
ADD COLUMN session_count_jetbrains bigint DEFAULT 0 NOT NULL,
ADD COLUMN session_count_reconnecting_pty bigint DEFAULT 0 NOT NULL,
ADD COLUMN session_count_ssh bigint DEFAULT 0 NOT NULL;

-- Restore the four known session counts. Other keys are discarded.
UPDATE workspace_agent_stats
SET
session_count_vscode = COALESCE((session_counts ->> 'vscode')::bigint, 0),
session_count_jetbrains = COALESCE((session_counts ->> 'jetbrains')::bigint, 0),
session_count_reconnecting_pty = COALESCE((session_counts ->> 'reconnecting_pty')::bigint, 0),
session_count_ssh = COALESCE((session_counts ->> 'ssh')::bigint, 0)
WHERE session_counts <> '{}'::jsonb;

DROP INDEX workspace_agent_stats_template_id_created_at_user_id_idx;

ALTER TABLE workspace_agent_stats
DROP COLUMN session_counts;

CREATE INDEX workspace_agent_stats_template_id_created_at_user_id_idx ON workspace_agent_stats USING btree (template_id, created_at, user_id) INCLUDE (session_count_vscode, session_count_jetbrains, session_count_reconnecting_pty, session_count_ssh, connection_median_latency_ms) WHERE (connection_count > 0);

COMMENT ON INDEX workspace_agent_stats_template_id_created_at_user_id_idx IS 'Support index for template insights endpoint to build interval reports faster.';
Original file line number Diff line number Diff line change
@@ -0,0 +1,78 @@
LOCK TABLE workspace_agent_stats IN ACCESS EXCLUSIVE MODE;

DO $$
DECLARE
latest_rollup_start timestamptz;
backlog_start timestamptz;
backlog_end timestamptz;
BEGIN
SELECT MAX(start_time) INTO latest_rollup_start FROM template_usage_stats;

-- Every row the rollup has not consumed yet has to be converted below, and
Comment thread
code-asher marked this conversation as resolved.
Resolved
-- the table stays locked for all of them. Measure the rollup against the
-- data rather than against the clock: a deployment shut down over a weekend
-- records nothing while it is off, and an idle deployment leaves the
-- watermark behind because there is nothing to roll up. Neither has a
-- backlog, and neither takes noticeably longer to convert.
SELECT MIN(created_at), MAX(created_at)
INTO backlog_start, backlog_end
FROM workspace_agent_stats
WHERE (latest_rollup_start IS NULL OR created_at >= latest_rollup_start)
AND (
session_count_vscode > 0
OR session_count_jetbrains > 0
OR session_count_reconnecting_pty > 0
OR session_count_ssh > 0
);

IF backlog_end - backlog_start > interval '24 hours' THEN
RAISE WARNING 'migration 000590 found % of workspace agent stats that template usage stats never rolled up', justify_hours(backlog_end - backlog_start)
USING DETAIL = format(
'This migration converts every workspace_agent_stats row the rollup has not consumed, %s through %s, and holds ACCESS EXCLUSIVE on the table until it finishes. Expect the migration to take longer than usual; do not interrupt it.',
backlog_start, backlog_end
),
HINT = 'A backlog this wide usually means the rollup is failing or blocked: after the upgrade, check coderd logs for "failed to rollup data", and check pg_locks for a session holding the rollup advisory lock.';
END IF;
END
$$;

ALTER TABLE workspace_agent_stats
ADD COLUMN session_counts jsonb DEFAULT '{}'::jsonb NOT NULL;

COMMENT ON COLUMN workspace_agent_stats.session_counts IS 'Positive session counts keyed by the canonical app name reported by the agent.';

-- Convert the window DeleteOldWorkspaceAgentStats retains plus everything the
-- rollup has not consumed yet, which is what the rollups and operational
-- statistics still read. Anything older has already been rolled up. When
-- nothing has ever rolled up there is no watermark to fall back on, so convert
-- every row rather than roll old activity up as zero minutes later.
UPDATE workspace_agent_stats
SET session_counts = jsonb_strip_nulls(jsonb_build_object(
'vscode', CASE WHEN session_count_vscode > 0 THEN session_count_vscode END,
'jetbrains', CASE WHEN session_count_jetbrains > 0 THEN session_count_jetbrains END,
'reconnecting_pty', CASE WHEN session_count_reconnecting_pty > 0 THEN session_count_reconnecting_pty END,
'ssh', CASE WHEN session_count_ssh > 0 THEN session_count_ssh END
))
WHERE created_at >= (
SELECT COALESCE(MAX(start_time) - interval '1 day', '-infinity'::timestamptz)
FROM template_usage_stats
)
AND (
session_count_vscode > 0
OR session_count_jetbrains > 0
OR session_count_reconnecting_pty > 0
OR session_count_ssh > 0
);

-- Recreate the index without the dropped columns in its INCLUDE list.
DROP INDEX workspace_agent_stats_template_id_created_at_user_id_idx;

ALTER TABLE workspace_agent_stats
DROP COLUMN session_count_vscode,
DROP COLUMN session_count_jetbrains,
DROP COLUMN session_count_reconnecting_pty,
DROP COLUMN session_count_ssh;

CREATE INDEX workspace_agent_stats_template_id_created_at_user_id_idx ON workspace_agent_stats USING btree (template_id, created_at, user_id) INCLUDE (connection_median_latency_ms) WHERE (connection_count > 0);

COMMENT ON INDEX workspace_agent_stats_template_id_created_at_user_id_idx IS 'Support index for template insights endpoint to build interval reports faster.';
Loading
Loading