#Low performance and high pool.acquire errors

1 messages · Page 1 of 1 (latest)

toxic quest
#

hey @bitter whale, a couple of things that might help narrow it down:

  1. can you run this on your DB and share the result?

EXPLAIN ANALYZE
SELECT id, snapshot FROM transactions
WHERE tenant_id = '<any_tenant_id>'
ORDER BY id DESC LIMIT 1;

there's a known missing index on that table that gets worse as tenants grow, curious if that's what you're hitting

  1. are you sending snap_token: "" (empty) on your check requests? that triggers an extra DB query on every single check.

  2. what's your average number of concurrent check requests hitting the service?

bitter whale
#

Hey @toxic quest ,

First of all many thanks for the reply, its kind and we appreciate 🙏

  1. I ran the query for the tenant we see using most in our multitenant in the POC
    EXPLAIN ANALYZE
    SELECT id, snapshot FROM transactions
    WHERE tenant_id = 'c58f2d3a-9122-437b-9f9b-09c06f23c039'
    ORDER BY id DESC LIMIT 1;

result:
"Limit (cost=0.41..1.89 rows=1 width=29) (actual time=0.057..0.057 rows=1 loops=1)"
" -> Index Scan Backward using pk_transaction on transactions (cost=0.41..19490.37 rows=13157 width=29) (actual time=0.056..0.056 rows=1 loops=1)"
" Filter: ((tenant_id)::text = 'c58f2d3a-9122-437b-9f9b-09c06f23c039'::text)"
"Planning Time: 0.085 ms"
"Execution Time: 0.074 ms"

  1. No, we send always the latest value from the latest data write.

  2. On data datadog the API cuncurrent numbers are ~2 RPS, but when i cross reference to what i see on the postgres server logs side the database is authenticating an average of ~23.5 new connections per second (~1,400/min), with peaks hitting ~42 connections per second (~2,500/min).

For reference the DB we are using is Azure Burstable, B2ms, 2 vCores, 8 GiB RAM, 32 GiB storage.

Thanks!!