[Production] cadence server issues with heavy load

[raj-jakasaniya]

We have Java microservice + Cadence library deployment (banzai-charts) connecting to MySQL database for persistence.
banzai-charts

Observation : cadence history or cadence matching creating new connection every time for any action.

1. Observing 504 in cadence UI
2. MySQL has too many tw / time_wait stuck connections (Which should not have been created in first place).

Steps taken : Added below config keys for connection pooling, but that didnt stop history or matching service from creating new mysql connections

          maxConns: 100
          maxIdleConns: 100
          maxConnLifetime: "5m"

Ideal behaviour : cadence history or matching service should maintain mysql/sql connection pooling for efficient thread handling.

Issues observed in different cadence services :

• Cadence-history

{error: InternalServiceError{Message: AppendHistoryNodes operation failed. Failed to start transaction. Error: Error 1135: Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug}, level: error, logging-call-at: persist…
> {error: InternalServiceError{Message: GetWorkflowExecution: failed. Error: InternalServiceError{Message: getChildExecutionInfoMap operation failed. Error: Error 1135: Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent…

• Cadence-frontend

{error: code:deadline-exceeded message:call to procedure "uber.cadence.history.v1.HistoryAPI::QueryWorkflow" of service "cadence-history" from caller "cadence-frontend" timed out, level: error, logging-call-at: workflowHandler.go:3966, msg: Frontend request timedout, service: cadence-frontend, stack
> {error: shard closed, level: error, logging-call-at: workflowHandler.go:3976, msg: Uncategorized error, service: cadence-frontend, stacktrace: github.com/uber/cadence/common/log/loggerimpl.(*loggerImpl).Error /cadence/common/log/loggerimpl/logger.go:131 github.com/uber/cadence/service/frontend.(

• Cadence-web
  Error: read ECONNRESET at TCP.onStreamRead (internal/stream_base_commons.js:209:20)
  {}
  2023-07-05 19:25:02.730 IST
  Error: read ECONNRESET at TCP.onStreamRead (internal/stream_base_commons.js:209:20)
  {}
  2023-07-05 19:25:28.705 IST
  Error: read ECONNRESET at TCP.onStreamRead (internal/stream_base_commons.js:209:20)

Is cadence history or matching service expected to form almost new connection for processing signal and putting that connection time_wait state for future updates, shouldnt it be optimised enough to use connection pooling and also how can we control number of connections to mysql ?

Urgent help needed in solving this issue.