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