fix(coderd/database): reduce db load via dbpurge advisory locking

mafredri · mafredri · commit e189af3a41f6 · 2024-04-22T13:56:15.000+03:00
diff --git a/coderd/database/dbpurge/dbpurge.go b/coderd/database/dbpurge/dbpurge.go
@@ -2,11 +2,10 @@ package dbpurge
 
 import (
 	"context"
-	"errors"
 	"io"
 	"time"
 
-	"golang.org/x/sync/errgroup"
+	"golang.org/x/xerrors"
 
 	"cdr.dev/slog"
 
@@ -35,22 +34,38 @@ func New(ctx context.Context, logger slog.Logger, db database.Store) io.Closer {
 	doTick := func() {
 		defer ticker.Reset(delay)
 
-		var eg errgroup.Group
-		eg.Go(func() error {
-			return db.DeleteOldWorkspaceAgentLogs(ctx)
-		})
-		eg.Go(func() error {
-			return db.DeleteOldWorkspaceAgentStats(ctx)
-		})
-		eg.Go(func() error {
-			return db.DeleteOldProvisionerDaemons(ctx)
-		})
-		err := eg.Wait()
-		if err != nil {
-			if errors.Is(err, context.Canceled) {
-				return
+		start := time.Now()
+		// Start a transaction to grab advisory lock, we don't want to run
+		// multiple purges at the same time (multiple replicas). The purge
+		// queries themselves are run outside of the transaction.
+		if err := db.InTx(func(tx database.Store) error {
+			// Acquire a lock to ensure that only one instance of
+			// the rollup is running at a time.
+			ok, err := tx.TryAcquireLock(ctx, database.LockIDDBPurge)
+			if err != nil {
+				return err
+			}
+			if !ok {
+				logger.Debug(ctx, "unable to acquire lock for purging old database entries, skipping")
+				return nil
+			}
+
+			if err := tx.DeleteOldWorkspaceAgentLogs(ctx); err != nil {
+				return xerrors.Errorf("failed to delete old workspace agent logs: %w", err)
 			}
+			if err := tx.DeleteOldWorkspaceAgentStats(ctx); err != nil {
+				return xerrors.Errorf("failed to delete old workspace agent stats: %w", err)
+			}
+			if err := tx.DeleteOldProvisionerDaemons(ctx); err != nil {
+				return xerrors.Errorf("failed to delete old provisioner daemons: %w", err)
+			}
+
+			logger.Info(ctx, "purged old database entries", slog.F("duration", time.Since(start)))
+
+			return nil
+		}, nil); err != nil {
 			logger.Error(ctx, "failed to purge old database entries", slog.Error(err))
+			return
 		}
 	}
 
diff --git a/coderd/database/lock.go b/coderd/database/lock.go
@@ -9,6 +9,7 @@ const (
 	LockIDDeploymentSetup = iota + 1
 	LockIDEnterpriseDeploymentSetup
 	LockIDDBRollup
+	LockIDDBPurge
 )
 
 // GenLockID generates a unique and consistent lock ID from a given string.

Original file line number	Diff line number	Diff line change
`@@ -9,6 +9,7 @@ const (`
`9`	`9`	`LockIDDeploymentSetup = iota + 1`
`10`	`10`	`LockIDEnterpriseDeploymentSetup`
`11`	`11`	`LockIDDBRollup`
	`12`	`+ LockIDDBPurge`
`12`	`13`	`)`
`13`	`14`
`14`	`15`	`// GenLockID generates a unique and consistent lock ID from a given string.`