Investigate long queries on Temporal DB

Community Support
,
1

Hello. We use Temporal with Postgresql DB. Temporal version is 1.20.3, DB version is 15.3. Before DB we use PGBouncer, it uses “session” mode. Sometimes we see on growing query execution on DB. Could you help what we can investigate?

Example of metric “Persistance latency”.

image
image1920×1007 144 KB

Config of persistance layer.

persistence:
  defaultStore: default
  visibilityStore: visibility
  numHistoryShards: 8192
  datastores:
    default:
      sql:
        pluginName: postgres12
        databaseName: temporal
        connectAddr: ***
        connectProtocol: "tcp"
        user: ***
        password: ***
        maxConnLifetime: 1h
        maxConns: 20
        maxIdleConns: 20
        tls:
          enabled: ***
          caFile: ***
          enableHostVerification: ***
    visibility:
      sql:
        pluginName: postgres12
        databaseName: temporal_visibility
        connectAddr: ***
        connectProtocol: "tcp"
        user: ***
        password: ***
        maxConnLifetime: 1h
        maxConns: 2
        maxIdleConns: 2
        tls:
          enabled: ***
          caFile: ***
          enableHostVerification: ***
2

Count of transitions is low.

image
image2708×1402 250 KB

3

up - the question is actual