Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite17e8aa

Browse files
authored
feat(coderd/database): keep only 1 day ofworkspace_agent_stats after rollup (#12674)
1 parent4a6693a commite17e8aa

File tree

6 files changed

+184
-16
lines changed

6 files changed

+184
-16
lines changed

‎cli/server.go

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -965,7 +965,7 @@ func (r *RootCmd) Server(newAPI func(context.Context, *coderd.Options) (*coderd.
965965
defershutdownConns()
966966

967967
// Ensures that old database entries are cleaned up over time!
968-
purger:=dbpurge.New(ctx,logger,options.Database)
968+
purger:=dbpurge.New(ctx,logger.Named("dbpurge"),options.Database)
969969
deferpurger.Close()
970970

971971
// Updates workspace usage

‎coderd/database/dbmem/dbmem.go

Lines changed: 55 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1506,13 +1506,65 @@ func (q *FakeQuerier) DeleteOldWorkspaceAgentStats(_ context.Context) error {
15061506
q.mutex.Lock()
15071507
deferq.mutex.Unlock()
15081508

1509+
/*
1510+
DELETE FROM
1511+
workspace_agent_stats
1512+
WHERE
1513+
created_at < (
1514+
SELECT
1515+
COALESCE(
1516+
-- When generating initial template usage stats, all the
1517+
-- raw agent stats are needed, after that only ~30 mins
1518+
-- from last rollup is needed. Deployment stats seem to
1519+
-- use between 15 mins and 1 hour of data. We keep a
1520+
-- little bit more (1 day) just in case.
1521+
MAX(start_time) - '1 days'::interval,
1522+
-- Fall back to 6 months ago if there are no template
1523+
-- usage stats so that we don't delete the data before
1524+
-- it's rolled up.
1525+
NOW() - '6 months'::interval
1526+
)
1527+
FROM
1528+
template_usage_stats
1529+
)
1530+
AND created_at < (
1531+
-- Delete at most in batches of 3 days (with a batch size of 3 days, we
1532+
-- can clear out the previous 6 months of data in ~60 iterations) whilst
1533+
-- keeping the DB load relatively low.
1534+
SELECT
1535+
COALESCE(MIN(created_at) + '3 days'::interval, NOW())
1536+
FROM
1537+
workspace_agent_stats
1538+
);
1539+
*/
1540+
15091541
now:=dbtime.Now()
1510-
sixMonthInterval:=6*30*24*time.Hour
1511-
sixMonthsAgo:=now.Add(-sixMonthInterval)
1542+
varlimit time.Time
1543+
// MAX
1544+
for_,stat:=rangeq.templateUsageStats {
1545+
ifstat.StartTime.After(limit) {
1546+
limit=stat.StartTime.AddDate(0,0,-1)
1547+
}
1548+
}
1549+
// COALESCE
1550+
iflimit.IsZero() {
1551+
limit=now.AddDate(0,-6,0)
1552+
}
15121553

15131554
varvalidStats []database.WorkspaceAgentStat
1555+
varbatchLimit time.Time
1556+
for_,stat:=rangeq.workspaceAgentStats {
1557+
ifbatchLimit.IsZero()||stat.CreatedAt.Before(batchLimit) {
1558+
batchLimit=stat.CreatedAt
1559+
}
1560+
}
1561+
ifbatchLimit.IsZero() {
1562+
batchLimit=time.Now()
1563+
}else {
1564+
batchLimit=batchLimit.AddDate(0,0,3)
1565+
}
15141566
for_,stat:=rangeq.workspaceAgentStats {
1515-
ifstat.CreatedAt.Before(sixMonthsAgo) {
1567+
ifstat.CreatedAt.Before(limit)&&stat.CreatedAt.Before(batchLimit) {
15161568
continue
15171569
}
15181570
validStats=append(validStats,stat)

‎coderd/database/dbpurge/dbpurge.go

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,6 @@ const (
2424
// This is for cleaning up old, unused resources from the database that take up space.
2525
funcNew(ctx context.Context,logger slog.Logger,db database.Store) io.Closer {
2626
closed:=make(chanstruct{})
27-
logger=logger.Named("dbpurge")
2827

2928
ctx,cancelFunc:=context.WithCancel(ctx)
3029
//nolint:gocritic // The system purges old db records without user input.

‎coderd/database/dbpurge/dbpurge_test.go

Lines changed: 70 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -11,12 +11,14 @@ import (
1111
"go.uber.org/goleak"
1212
"golang.org/x/exp/slices"
1313

14+
"cdr.dev/slog"
1415
"cdr.dev/slog/sloggers/slogtest"
1516

1617
"github.com/coder/coder/v2/coderd/database"
1718
"github.com/coder/coder/v2/coderd/database/dbgen"
1819
"github.com/coder/coder/v2/coderd/database/dbmem"
1920
"github.com/coder/coder/v2/coderd/database/dbpurge"
21+
"github.com/coder/coder/v2/coderd/database/dbrollup"
2022
"github.com/coder/coder/v2/coderd/database/dbtestutil"
2123
"github.com/coder/coder/v2/coderd/database/dbtime"
2224
"github.com/coder/coder/v2/provisionerd/proto"
@@ -40,27 +42,62 @@ func TestDeleteOldWorkspaceAgentStats(t *testing.T) {
4042
t.Parallel()
4143

4244
db,_:=dbtestutil.NewDB(t)
43-
logger:=slogtest.Make(t,&slogtest.Options{IgnoreErrors:true})
45+
logger:=slogtest.Make(t,&slogtest.Options{IgnoreErrors:true}).Leveled(slog.LevelDebug)
46+
47+
now:=dbtime.Now()
48+
49+
deferfunc() {
50+
ift.Failed() {
51+
t.Logf("Test failed, printing rows...")
52+
ctx:=testutil.Context(t,testutil.WaitShort)
53+
wasRows,err:=db.GetWorkspaceAgentStats(ctx,now.AddDate(0,-7,0))
54+
iferr==nil {
55+
for_,row:=rangewasRows {
56+
t.Logf("workspace agent stat: %v",row)
57+
}
58+
}
59+
tusRows,err:=db.GetTemplateUsageStats(context.Background(), database.GetTemplateUsageStatsParams{
60+
StartTime:now.AddDate(0,-7,0),
61+
EndTime:now,
62+
})
63+
iferr==nil {
64+
for_,row:=rangetusRows {
65+
t.Logf("template usage stat: %v",row)
66+
}
67+
}
68+
}
69+
}()
4470

4571
ctx,cancel:=context.WithTimeout(context.Background(),testutil.WaitShort)
4672
defercancel()
4773

48-
now:=dbtime.Now()
49-
5074
// given
5175
// Let's use RxBytes to identify stat entries.
5276
// Stat inserted 6 months + 1 hour ago, should be deleted.
5377
first:=dbgen.WorkspaceAgentStat(t,db, database.WorkspaceAgentStat{
54-
CreatedAt:now.Add(-6*30*24*time.Hour-time.Hour),
78+
CreatedAt:now.AddDate(0,-6,0).Add(-time.Hour),
79+
ConnectionCount:1,
5580
ConnectionMedianLatencyMS:1,
5681
RxBytes:1111,
82+
SessionCountSSH:1,
5783
})
5884

59-
// Stat inserted 6 months - 1 hour ago, should not be deleted.
85+
// Stat inserted 6 months - 1 hour ago, should not be deleted before rollup.
6086
second:=dbgen.WorkspaceAgentStat(t,db, database.WorkspaceAgentStat{
61-
CreatedAt:now.Add(-5*30*24*time.Hour+time.Hour),
87+
CreatedAt:now.AddDate(0,-6,0).Add(time.Hour),
88+
ConnectionCount:1,
6289
ConnectionMedianLatencyMS:1,
6390
RxBytes:2222,
91+
SessionCountSSH:1,
92+
})
93+
94+
// Stat inserted 6 months - 1 day - 2 hour ago, should not be deleted at all.
95+
third:=dbgen.WorkspaceAgentStat(t,db, database.WorkspaceAgentStat{
96+
CreatedAt:now.AddDate(0,-6,0).AddDate(0,0,1).Add(2*time.Hour),
97+
ConnectionCount:1,
98+
ConnectionMedianLatencyMS:1,
99+
RxBytes:3333,
100+
SessionCountSSH:1,
64101
})
65102

66103
// when
@@ -70,15 +107,39 @@ func TestDeleteOldWorkspaceAgentStats(t *testing.T) {
70107
// then
71108
varstats []database.GetWorkspaceAgentStatsRow
72109
varerrerror
73-
require.Eventually(t,func()bool {
110+
require.Eventuallyf(t,func()bool {
74111
// Query all stats created not earlier than 7 months ago
75-
stats,err=db.GetWorkspaceAgentStats(ctx,now.Add(-7*30*24*time.Hour))
112+
stats,err=db.GetWorkspaceAgentStats(ctx,now.AddDate(0,-7,0))
76113
iferr!=nil {
77114
returnfalse
78115
}
79116
return!containsWorkspaceAgentStat(stats,first)&&
80117
containsWorkspaceAgentStat(stats,second)
81-
},testutil.WaitShort,testutil.IntervalFast,stats)
118+
},testutil.WaitShort,testutil.IntervalFast,"it should delete old stats: %v",stats)
119+
120+
// when
121+
events:=make(chan dbrollup.Event)
122+
rolluper:=dbrollup.New(logger,db,dbrollup.WithEventChannel(events))
123+
deferrolluper.Close()
124+
125+
_,_=<-events,<-events
126+
127+
// Start a new purger to immediately trigger delete after rollup.
128+
_=closer.Close()
129+
closer=dbpurge.New(ctx,logger,db)
130+
defercloser.Close()
131+
132+
// then
133+
require.Eventuallyf(t,func()bool {
134+
// Query all stats created not earlier than 7 months ago
135+
stats,err=db.GetWorkspaceAgentStats(ctx,now.AddDate(0,-7,0))
136+
iferr!=nil {
137+
returnfalse
138+
}
139+
return!containsWorkspaceAgentStat(stats,first)&&
140+
!containsWorkspaceAgentStat(stats,second)&&
141+
containsWorkspaceAgentStat(stats,third)
142+
},testutil.WaitShort,testutil.IntervalFast,"it should delete old stats after rollup: %v",stats)
82143
}
83144

84145
funccontainsWorkspaceAgentStat(stats []database.GetWorkspaceAgentStatsRow,needle database.WorkspaceAgentStat)bool {

‎coderd/database/queries.sql.go

Lines changed: 29 additions & 1 deletion
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries/workspaceagentstats.sql

Lines changed: 29 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,35 @@ ORDER BY
6666
dateASC;
6767

6868
-- name: DeleteOldWorkspaceAgentStats :exec
69-
DELETEFROM workspace_agent_statsWHERE created_at< NOW()- INTERVAL'180 days';
69+
DELETEFROM
70+
workspace_agent_stats
71+
WHERE
72+
created_at< (
73+
SELECT
74+
COALESCE(
75+
-- When generating initial template usage stats, all the
76+
-- raw agent stats are needed, after that only ~30 mins
77+
-- from last rollup is needed. Deployment stats seem to
78+
-- use between 15 mins and 1 hour of data. We keep a
79+
-- little bit more (1 day) just in case.
80+
MAX(start_time)-'1 days'::interval,
81+
-- Fall back to 6 months ago if there are no template
82+
-- usage stats so that we don't delete the data before
83+
-- it's rolled up.
84+
NOW()-'6 months'::interval
85+
)
86+
FROM
87+
template_usage_stats
88+
)
89+
AND created_at< (
90+
-- Delete at most in batches of 3 days (with a batch size of 3 days, we
91+
-- can clear out the previous 6 months of data in ~60 iterations) whilst
92+
-- keeping the DB load relatively low.
93+
SELECT
94+
COALESCE(MIN(created_at)+'3 days'::interval, NOW())
95+
FROM
96+
workspace_agent_stats
97+
);
7098

7199
-- name: GetDeploymentWorkspaceAgentStats :one
72100
WITH agent_statsAS (

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp