server: perf issues when purging system.web_sessions

[aadityasondhi]

This issue was seen in: https://github.com/cockroachlabs/support/issues/1812

In summary, the customer experienced increased CPU usage when the system.web_sessions table grew large.

In the investigation, it was found that the suggested fix would be to:

1. raise the default LIMIT of 10 to something much greater.
2. remove ORDER BY random() in

   cockroach/pkg/server/purge_auth_session.go

   Lines 83 to 158 in 6971977

   	// purgeOldSessions deletes old web session records.
   	// Performs three purges: (1) one for sessions with expiration
   	// older than the purge TTL, (2) one for sessions with revocation
   	// older than the purge TTL, and (3) one for sessions that have
   	// timed out since they were last used.
   	func (s *authenticationServer) purgeOldSessions(ctx context.Context) {
   	var (
   	deleteOldExpiredSessionsStmt = `
   	DELETE FROM system.web_sessions
   	WHERE "expiresAt" < $1
   	ORDER BY random()
   	LIMIT $2
   	RETURNING 1
   	`
   	deleteOldRevokedSessionsStmt = `
   	DELETE FROM system.web_sessions
   	WHERE "revokedAt" < $1
   	ORDER BY random()
   	LIMIT $2
   	RETURNING 1
   	`
   	deleteSessionsAutoLogoutStmt = `
   	DELETE FROM system.web_sessions
   	WHERE "lastUsedAt" < $1
   	ORDER BY random()
   	LIMIT $2
   	RETURNING 1
   	`
   	settingsValues = &s.sqlServer.execCfg.Settings.SV
   	internalExecutor = s.sqlServer.internalExecutor
   	currTime = s.sqlServer.execCfg.Clock.PhysicalTime()
   	purgeTTL = webSessionPurgeTTL.Get(settingsValues)
   	autoLogoutTimeout = webSessionAutoLogoutTimeout.Get(settingsValues)
   	limit = webSessionPurgeLimit.Get(settingsValues)
   	purgeTime = currTime.Add(purgeTTL * time.Duration(-1))
   	autoLogoutTime = currTime.Add(autoLogoutTimeout * time.Duration(-1))
   	)
   	if _, err := internalExecutor.ExecEx(
   	ctx,
   	"delete-old-expired-sessions",
   	nil, /* txn */
   	sessiondata.InternalExecutorOverride{User: username.RootUserName()},
   	deleteOldExpiredSessionsStmt,
   	purgeTime,
   	limit,
   	); err != nil {
   	log.Errorf(ctx, "error while deleting old expired web sessions: %+v", err)
   	}
   	if _, err := internalExecutor.ExecEx(
   	ctx,
   	"delete-old-revoked-sessions",
   	nil, /* txn */
   	sessiondata.InternalExecutorOverride{User: username.RootUserName()},
   	deleteOldRevokedSessionsStmt,
   	purgeTime,
   	limit,
   	); err != nil {
   	log.Errorf(ctx, "error while deleting old revoked web sessions: %+v", err)
   	}
   	if _, err := internalExecutor.ExecEx(
   	ctx,
   	"delete-sessions-timeout",
   	nil, /* txn */
   	sessiondata.InternalExecutorOverride{User: username.RootUserName()},
   	deleteSessionsAutoLogoutStmt,
   	autoLogoutTime,
   	limit,
   	); err != nil {
   	log.Errorf(ctx, "error while deleting web sessions older than auto-logout timeout: %+v", err)
   	}
   	}

   as it causes extra CPU load when purging larger tables

Jira issue: CRDB-19912