R
Railway•15mo ago
LucasReinaldo

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?
46 Replies
Percy
Percy•15mo ago
Project ID: 63ec8277-ce73-4ab7-8034-0c7e79c9c74b
LucasReinaldo
LucasReinaldo•15mo ago
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 🤔
Brody
Brody•15mo ago
at what stage of your application lifecyle does it connect to the database?
LucasReinaldo
LucasReinaldo•15mo ago
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 :/
Brody
Brody•15mo ago
what I'm really asking is does your app try to connect to the database right when it starts?
LucasReinaldo
LucasReinaldo•15mo ago
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
Brody
Brody•15mo ago
would you happen to be utilising the private network?
LucasReinaldo
LucasReinaldo•15mo ago
nope
Brody
Brody•15mo ago
is it enabled? if so, try disabling it
LucasReinaldo
LucasReinaldo•15mo ago
disabled now
Brody
Brody•15mo ago
okay let me know how that goes
LucasReinaldo
LucasReinaldo•15mo ago
why is that? like, why disabling ?
Brody
Brody•15mo ago
private networking initialisation times can interfere with calls/connection attempts
LucasReinaldo
LucasReinaldo•15mo ago
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 ?
Brody
Brody•15mo ago
you'd want to increase the timeout
LucasReinaldo
LucasReinaldo•15mo ago
cause I've bumped to 500 and seems to be working haha timeout is 300
Brody
Brody•15mo ago
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
LucasReinaldo
LucasReinaldo•15mo ago
np
Brody
Brody•15mo ago
mildpanic all good now?
LucasReinaldo
LucasReinaldo•15mo ago
just making sure that, I want to keep the connection_limit as low as possible and increase the timeout, that's the recommendation ?
Brody
Brody•15mo ago
I'd recommend keeping the private networking disabled so you don't need to set the timeout higher
LucasReinaldo
LucasReinaldo•15mo ago
got it i'll keep an eye on
Brody
Brody•15mo ago
but from my tests the initialisation time takes about 2 seconds
LucasReinaldo
LucasReinaldo•15mo ago
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
Brody
Brody•15mo ago
I mean at first glance that seems more like a code issue?
LucasReinaldo
LucasReinaldo•15mo ago
even with 700 pooled connections? like, seems weird
Brody
Brody•15mo ago
it is indeed weird
LucasReinaldo
LucasReinaldo•14mo ago
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 🤔
Brody
Brody•14mo ago
honestly no clue, I have no experience with prisma but the team does, and you are pro after all 😉
LucasReinaldo
LucasReinaldo•14mo ago
yep, maybe I should contact support thanks dude appreciate
Greg Schier
Greg Schier•14mo ago
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
Brody
Brody•14mo ago
that would like eval to 64 even on trial
Greg Schier
Greg Schier•14mo ago
Okay, adjust as needed then if that's too much We're going to fix this eventually... 😅
LucasReinaldo
LucasReinaldo•14mo ago
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
Greg Schier
Greg Schier•14mo ago
Do you have any metrics on how many prisma connections are being used?
LucasReinaldo
LucasReinaldo•14mo ago
# 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
# 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
Greg Schier
Greg Schier•14mo ago
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?
LucasReinaldo
LucasReinaldo•14mo ago
datasource db url the directurl is only for migrations
Greg Schier
Greg Schier•14mo ago
So DATABASE_URL, not DATABASE_LAMBDA_URL?
LucasReinaldo
LucasReinaldo•14mo ago
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
Greg Schier
Greg Schier•14mo ago
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
LucasReinaldo
LucasReinaldo•14mo ago
🤔
Greg Schier
Greg Schier•14mo ago
Curious what this output is from, and if it's possible to run if it happens again
LucasReinaldo
LucasReinaldo•14mo ago
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
Greg Schier
Greg Schier•14mo ago
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
LucasReinaldo
LucasReinaldo•14mo ago
yeah :/
Want results from more Discord servers?
Add your server