v3.5.0: Server OOM killed when fetching archived workflows

[vanny96]

Pre-requisites

• I have double-checked my configuration
• I can confirm the issue exists when I tested with :latest
• I have searched existing issues and could not find a match for this bug
• I'd like to contribute the fix myself (see contributing guide)

What happened/what did you expect to happen?

When the Workflow Archive feature is enabled, Argo Workflows starts querying the db while trying to fetch all of the workflows.
In case there is high number of workflows persisted (experienced this with >100000 workflows in argo_archived_workflows) on the DB the applications seems to struggle and it can lead to a considerable increase of of RAM needed to run.

Sometimes this is preceeded by the attached log, which indicates a pretty clear issue: there is no limit set on the query.

While the workflowArchiveServer interfaces seems to have some logic to handle limits, such logic seems to have been disabled by the following snippet at server/workflow/workflow_server.go#228

	// Search whole with Limit 0.
	// Reset the Continue "0" to prevent archive workflow pagination.
	options.Continue = "0"
	options.Limit = 0
	archivedWfList, err := s.wfArchiveServer.ListArchivedWorkflows(ctx, &workflowarchivepkg.ListArchivedWorkflowsRequest{
		ListOptions: options,
		NamePrefix:  "",
		Namespace:   req.Namespace,
	})

Here the limit is hardcoded to 0, which leads to no limit to be set at all.

Requests

• Set the limit based on the "Results per page" set on the UI.
• Add an option to disable the rendering of archived workflows from the web server, making the call to the DB optional

Version

3.5.0

Paste a small workflow that reproduces the issue. We must be able to run the workflow; don't enter a workflows that uses private images.

Not relevant

Logs from the workflow controller

Query:          SELECT "workflow" FROM "argo_archived_workflows" WHERE (("clustername" = $1 AND "instanceid" = $2) AND "namespace" = $3 AND not exists (select 1 from argo_archived_workflows_labels where clustername = argo_archived_workflows.clustername and uid = argo_archived_workflows.uid and name = 'workflows.argoproj.io/controller-instanceid')) ORDER BY "startedat" DESC
	Arguments:      []interface {}{"default", "", "namespace"}
	Stack:          
		fmt.(*pp).handleMethods@/usr/local/go/src/fmt/print.go:673
		fmt.(*pp).printArg@/usr/local/go/src/fmt/print.go:756
		fmt.(*pp).doPrint@/usr/local/go/src/fmt/print.go:1211
		fmt.Append@/usr/local/go/src/fmt/print.go:289
		log.(*Logger).Print.func1@/usr/local/go/src/log/log.go:261
		log.(*Logger).output@/usr/local/go/src/log/log.go:238
		log.(*Logger).Print@/usr/local/go/src/log/log.go:260
		github.com/argoproj/argo-workflows/v3/persist/sqldb.(*workflowArchive).ListWorkflows@/go/src/github.com/argoproj/argo-workflows/persist/sqldb/workflow_archive.go:172
		github.com/argoproj/argo-workflows/v3/server/workflowarchive.(*archivedWorkflowServer).ListArchivedWorkflows@/go/src/github.com/argoproj/argo-workflows/server/workflowarchive/archived_workflow_server.go:136
		github.com/argoproj/argo-workflows/v3/server/workflow.(*workflowServer).ListWorkflows@/go/src/github.com/argoproj/argo-workflows/server/workflow/workflow_server.go:232
		github.com/argoproj/argo-workflows/v3/pkg/apiclient/workflow._WorkflowService_ListWorkflows_Handler.func1@/go/src/github.com/argoproj/argo-workflows/pkg/apiclient/workflow/workflow.pb.go:1826
		github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.RatelimitUnaryServerInterceptor.func5@/go/src/github.com/argoproj/argo-workflows/util/grpc/interceptor.go:65
		github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/[email protected]/chain.go:25
		github.com/argoproj/argo-workflows/v3/server/auth.(*gatekeeper).UnaryServerInterceptor.func1@/go/src/github.com/argoproj/argo-workflows/server/auth/gatekeeper.go:98
		github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/[email protected]/chain.go:25
		github.com/argoproj/argo-workflows/v3/util/grpc.glob..func1@/go/src/github.com/argoproj/argo-workflows/util/grpc/interceptor.go:45
		github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/[email protected]/chain.go:25
		github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.PanicLoggerUnaryServerInterceptor.func4@/go/src/github.com/argoproj/argo-workflows/util/grpc/interceptor.go:26
		github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/[email protected]/chain.go:25
		github.com/grpc-ecosystem/go-grpc-middleware/logging/logrus.UnaryServerInterceptor.func1@/go/pkg/mod/github.com/grpc-ecosystem/[email protected]/logging/logrus/server_interceptors.go:31
		github.com/argoproj/argo-workflows/v3/server/apiserver.(*argoServer).newGRPCServer.ChainUnaryServer.func6.1.1@/go/pkg/mod/github.com/grpc-ecosystem/[email protected]/chain.go:25
	Error:          upper: slow query
	Time taken:     0.21139s
	Context:        context.Background

Logs from in your workflow's wait container

Not relevant

[eduardodbr]

probably duplicate of #12025

[caelan-io]

Correct, please see #12025 and proposed fix in #12736

[agilgur5]

• I can confirm the issue exists when I tested with :latest

Version

3.5.0

• Set the limit based on the "Results per page" set on the UI.

You're on an old version, this was fixed/reverted in 3.5.1 by #12068.

Please fill out the issue template properly; that is why it asks if you've tested with :latest with a checkbox.

Correct, please see #12025 and proposed fix in #12736

There are still other performance & correctness issues (#11715 -- which is caused by only having "Results per page" limit) with 3.5.x, which #12736 aims to solve.

[caelan-io]

Thanks, @agilgur5 !