Investigate PG Query times increasing

[ericboehs]

Since ~July 15th our DB latency has started to increase. There's also been a small spike at 2p CT since July 10th:

July 10th:

Two weeks:

July 15th:

• July 15th: Thread count increased to 12
• July 16h: Redis and PG synced to thread count and sister PR

Tasks

• [x] Investigate the cause of the increase in average Postgres query time

Acceptance Criteria

• [ ] The cause for the Postgres query times increase is known
• [ ] Issues are created to address resolution

[ericboehs]

We're scaling puma threads down to 8 today to see if this helps.

Notes

• Fairly normal query times were <20ms average during the day (<10ms at night)
• Starting Thurs July 11th we started having day spikes above 20-30ms
• Wed July 17th was even worse with a 46ms spike
• This Monday (22nd), it really took off though with spikes above 130ms and night time hovering around 20ms

Timeline: July 11th: EKS Cluster Nodes: 45 bin/is_ready is deployed initial delay on readiness decreased 45->20; failure threshold increased 3->5

July 16th: EKS Cluster Nodes: 45->35 (afternoon)

July 17th: EKS Cluster Nodes: 35->30 (morning) EKS Cluster Nodes: 30->25 (afternoon)

July 18th: EKS Cluster Nodes: 25->20 (morning) EKS Cluster Nodes: 20->15 (afternoon)

July 19th: EKS Cluster Nodes: 15->10 (afternoon)

We went up with Puma threads on the 16th as. Maybe we went too high with thread count. We also increased AR and Redis connection pooling around that time.

There's a 3pm ET spike we need to look into. It's been spiking our latency every day since July 10th. I'm guessing a job was merged/deployed then. It could have also been enabled via feature flag.

[ericboehs]

Starting around the time (July 16th) we increased the connection pool/max threads to 12 (from 5), we started seeing problems with DB latency. As we tuned our nodes and pods down in size, the problems got worse.

Today we decreased threads and that helped us signficiantly. We're furthering tuning these values down to help with db latency.

Related: https://github.com/department-of-veterans-affairs/vsp-infra-application-manifests/pull/3021

[ericboehs]

It looks like a select few queries are skewing our metrics. When deselecting the top 4 offenders, our metrics don't look so bad. Not sure what to conclude from this:

The SELECT "vba_documents_upload_submissions"... show up in PgHero as a slow query but not the 526 submissions. Perhaps it's not slow enough on average to populate in PgHero.

Further investigation into these queries needs to be done (e.g. index/explain).

[ericboehs]

It appears the form526 queries are sporadic and skewing our metrics. Benefits Disablity is going to take a look. Closing for now.