Hi guys, I would like to ask for some recommendation. We are running Pactbroker in Kubernetes. Currently we are using AWS RDS with these parameters

Instance class: db.t3.medium
vCPU: 2
RAM: 4 GB
Engine version: 13.18

but lately we have been having problems with SQL queries timeouts

Exception: Sequel::DatabaseError: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

We are preparing to set up DB cleanup, but at the same time we are considering whether we should increase RDS resources. Do you have any experience with what Instance class would be suitable?

DB cleanup will give you the best bang for your buck, 100%. Deleting old branches (search docs.pact.io) is also a great tactic - these will be the biggest reasons for your slow queries. Do you have a sense of size/magnitude of your Pact rollout - approximate number of integrations/applications?

Currently we have 83 integrations

⬆️ db size is 643MB

are you running the cleanup via cron inside your running container, or have you tried from the external source (running a pact broker with an entrypoint of clean) alternatively are you able to login into the database and run commands directly?

Currently we have 83 integrations

…

db size is 643MB (edited)

ok, that’s not a huge roll out, we have customers with much larger data sets/magnitude. There are some queries that are highly dependent on data volumes. I think the clean will help, however we need to get over this hump first

First, we tried increasing the RDS

databaseInstanceType

to

db.t4g.large

(we originally had

db.t3.medium

), but that didn’t help. We managed to get the cleanup running with this config, thanks for your help thankyou

- name: PACT_BROKER_DATABASE_STATEMENT_TIMEOUT
  value: '300000'
- name: PACT_BROKER_METRICS_SQL_STATEMENT_TIMEOUT
  value: '600'
- name: PACT_BROKER_DATABASE_POOL_TIMEOUT
  value: '360'
- name: PACT_BROKER_SQL_LOG_WARN_DURATION
  value: '60'

eep. But it’s now cleaning? In the clean logs, it should print out how many records it’s deleting each time. If the number of records = the number of records in the max in the clean configuration then you know you need to keep running it (I’m just on mobile, so don’t have the docs handy) Once you get to 0, you’re now at “baseline” clean. You can run the job regularly and monitor how many records it deletes - basically, you want to ensure you clean regularly enough that you aren’t accumulating faster than you’re deleting. Will be keen to see if it makes any differences to query performance for you

Hi Matt, thanks for advice. This is what we currently do. The initial cleanup is done and now we have cleanup once a day and monitor how many records are deleted.