pgbouncer

Hey folks, I have a supabase and Nextjs (+ prisma) project deployed in Railway, what's the best option to set the connection pool string since railway creates an server from nextjs functions?

63ec8277-ce73-4ab7-8034-0c7e79c9c74b
my plan allows me to have

No. of direct connections - 240
No. of pooler connections - 700

but I keep getting connection timeout issues 🤔

at what stage of your application lifecyle does it connect to the database?

right after the packages are built if that's what you asking?
I believe using lambda functions the connection_limit should be as low as possible due the number of requests, but with railway I am just not sure what would be the right approach tbh :/

what I'm really asking is does your app try to connect to the database right when it starts?

I'd say so, the prisma instance is global, in a normal lambda it would instantiate at the moment the request is received, but since Railway wraps it all in a server my guess is that it is, yes

would you happen to be utilising the private network?

nope

is it enabled?
if so, try disabling it

disabled now

okay let me know how that goes

why is that?
like, why disabling ?

private networking initialisation times can interfere with calls/connection attempts

okay
aside from that, I can follow prisma docs and change the connection_limit to as low as possible
so prisma won't try to manage it, instead pgbouncer will
right ?

you'd want to increase the timeout

cause I've bumped to 500 and seems to be working haha
timeout is 300

well you do get 8 vcpu so I don't even know if litestar can make use of multiple cpus
this is multiple cpus, not multiple cores btw
whoops
ignore that

np

so all good now?

just making sure that, I want to keep the connection_limit as low as possible and increase the timeout, that's the recommendation ?

I'd recommend keeping the private networking disabled so you don't need to set the timeout higher

got it
i'll keep an eye on

but from my tests the initialisation time takes about 2 seconds

like the project is running fine, then I get a lot of data in a short period and my connection limit goes to shit and keeps timing out

I mean at first glance that seems more like a code issue?

even with 700 pooled connections? like, seems weird

it is indeed weird

should I call prisma.$disconnect at the end of the function, I reviewed all api endpoints and in all of then I am either returning success or error, so there is always a return response.json 🤔

honestly no clue, I have no experience with prisma
but the team does, and you are pro after all 😉

yep, maybe I should contact support
thanks dude
appreciate

Just chiming in here. The connection limit should not be as low as possible for Railway because you only have 1 server. If you set connection limit to 1, your code will constantly be waiting for that single connection. I would stick with Prisma's default connection limit which I think is num_cpu * 2 or something

that would like eval to 64
even on trial

Okay, adjust as needed then if that's too much
We're going to fix this eventually... 😅

yep, the low as possible is in a lambda environment, in railway I've bumped a few times.

the db allows me to:

No. of direct connections - 240
No. of pooler connections - 700

and I also have inngest (https://www.inngest.com/) that I run some fan-out/batch inserts and updates also deployed in railway

Do you have any metrics on how many prisma connections are being used?

# HELP prisma_client_queries_total Total number of Prisma Client queries executed
# TYPE prisma_client_queries_total counter
prisma_client_queries_total 1130

# HELP prisma_datasource_queries_total Total number of Datasource Queries executed
# TYPE prisma_datasource_queries_total counter
prisma_datasource_queries_total 5349

# HELP prisma_pool_connections_closed_total Total number of Pool Connections closed
# TYPE prisma_pool_connections_closed_total counter
prisma_pool_connections_closed_total 18

# HELP prisma_pool_connections_open Number of currently open Pool Connections
# TYPE prisma_pool_connections_open counter
prisma_pool_connections_open 40

# HELP prisma_client_queries_active Number of currently active Prisma Client queries
# TYPE prisma_client_queries_active gauge
prisma_client_queries_active 0

# HELP prisma_client_queries_wait Number of queries currently waiting for a connection
# TYPE prisma_client_queries_wait gauge
prisma_client_queries_wait 0

# HELP prisma_pool_connections_busy Number of currently busy Pool Connections (executing a database query)
# TYPE prisma_pool_connections_busy gauge
prisma_pool_connections_busy 0

# HELP prisma_pool_connections_idle Number of currently unused Pool Connections (waiting for the next pool query to run)
# TYPE prisma_pool_connections_idle gauge
prisma_pool_connections_idle 400

# HELP prisma_pool_connections_open Number of currently open Pool Connections
# TYPE prisma_pool_connections_open gauge
prisma_pool_connections_open -18

# HELP prisma_pool_connections_opened_total Total number of Pool Connections opened
# TYPE prisma_pool_connections_opened_total gauge
prisma_pool_connections_opened_total 40

this only for the past 30mins

It says you don't have any active connections (usually the case if you don't have any long-running operations). Which variable in your project are you using to configure prisma?

datasource db url
the directurl is only for migrations

So DATABASE_URL, not DATABASE_LAMBDA_URL?

DATABASE_LAMBDA_URL I was using to test the inngest environment, they say to share a single instance of prisma within the step function but don't say anything about datasource

see here: https://www.inngest.com/docs/reference/middleware/examples#prisma-in-function-context
DATABASE_URL yes

It does seem like everything is correctly setup from a Railway perspective. I'm not sure there's much else I can do to help here unfortunately. My guess is that there's some code executing a bunch of concurrent DB operations that's exhausting the Prisma pool. We've had this internally before when running DB operations inside a Promise.all

🤔

Curious what this output is from, and if it's possible to run if it happens again

I'll review my Promise.all, like I do get a lot of data every minute or so and 15mins, but I already batch the inserts and updates
anyway, if it happens again I'll copy the metrics from prisma

Thanks! It does seem like a Prisma/code/config issue FWIW since you mentioned your provider said you're not even close to the limit

yeah :/