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

Commit1df93be

Browse files
committed
feat(coderd/database): keep only 1 day ofworkspace_agent_stats after rollup
1 parent28754a7 commit1df93be

File tree

6 files changed

+148
-16
lines changed

6 files changed

+148
-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: 37 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1506,13 +1506,47 @@ 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+
*/
1531+
15091532
now:=dbtime.Now()
1510-
sixMonthInterval:=6*30*24*time.Hour
1511-
sixMonthsAgo:=now.Add(-sixMonthInterval)
1533+
varlimit time.Time
1534+
// MAX
1535+
for_,stat:=rangeq.templateUsageStats {
1536+
ifstat.StartTime.After(limit) {
1537+
limit=stat.StartTime.AddDate(0,0,-1)
1538+
}
1539+
}
1540+
// COALESCE
1541+
iflimit.IsZero() {
1542+
limit=now.AddDate(0,-6,0)
1543+
}
15121544

15131545
varvalidStats []database.WorkspaceAgentStat
15141546
for_,stat:=rangeq.workspaceAgentStats {
1515-
ifstat.CreatedAt.Before(sixMonthsAgo) {
1547+
fmt.Println(stat.CreatedAt,limit)
1548+
ifstat.CreatedAt.Before(limit) {
1549+
fmt.Println("delete")
15161550
continue
15171551
}
15181552
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: 20 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: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -90,7 +90,26 @@ ORDER BY
9090
dateASC;
9191

9292
-- name: DeleteOldWorkspaceAgentStats :exec
93-
DELETEFROM workspace_agent_statsWHERE created_at< NOW()- INTERVAL'180 days';
93+
DELETEFROM
94+
workspace_agent_stats
95+
WHERE
96+
created_at< (
97+
SELECT
98+
COALESCE(
99+
-- When generating initial template usage stats, all the
100+
-- raw agent stats are needed, after that only ~30 mins
101+
-- from last rollup is needed. Deployment stats seem to
102+
-- use between 15 mins and 1 hour of data. We keep a
103+
-- little bit more (1 day) just in case.
104+
MAX(start_time)-'1 days'::interval,
105+
-- Fall back to 6 months ago if there are no template
106+
-- usage stats so that we don't delete the data before
107+
-- it's rolled up.
108+
NOW()-'6 months'::interval
109+
)
110+
FROM
111+
template_usage_stats
112+
);
94113

95114
-- name: GetDeploymentWorkspaceAgentStats :one
96115
WITH agent_statsAS (

0 commit comments

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