Prisma•4mo ago

Setting Connection limit in gcp cloud run and cloud sql

What is the best practice when setting the connection limit of prisma in gcp cloud run? * I am using postgres from cloud sql * I have a master database with 14 cpu and 20GB ram * 3 replicas each with 10 cpus and 20GB ram , each can handle 500 connections * cloud run with multiple instances , each instance with 2 GiB memory and 2 cpus how can I know what is the best connection limit to set , as I noticed increasing them is disconnecting cloud run from the sql sometimes ps : I am using replicas extension

const prisma = new PrismaClient().$extends(
  readReplicas({
    url: DATABASE_URL_REPLICAS
  })
);

const prisma = new PrismaClient().$extends(
  readReplicas({
    url: DATABASE_URL_REPLICAS
  })
);

14 Replies

jonfanz•4mo ago

what problem are you running into exactly? If you have an error message or error log that will help us point you in the right direction.

Mohammad Orabi•4mo ago

Thank you @Jon Harrell will provide all info in a second Hello , we are trying to make a correct pg bouncer configuration deployed on a vm to work with cloud run , prisma , and cloud sql postgres * Goal : Handle 2000 concurrent users * Cloud run * max of 10 instances , each instance has a max of 200 concurrent request * Cloud Sql
* master database and 3 replicas , each with a max connection of 500 , total of 2000 together * latest pg bouncer config we are trying * max-client-connection =2000 * default_pool_size= 500. (assuming 2000/4 databases) * max-db-connections 400 (each of our databases can handle 500 max connections) * prisma is using a pgbouncer url with 40 connection_limit , (assuming 500 default pool size / 10 number of instances -10 ) * using load tests we are reaching 500 r/s , created virtual users 829 and started to see errors * prisma errors in application level is " Can't reach database server at pgbouncer ip and port * cloud sql error , connection to client clost we just need some help on how and based on what we should do our numbers , much appreciated ,

jonfanz•4mo ago

Couple questions: - are you sure there are no more than 10 application servers? - how are you handling routing to a replica vs primary db? - do you have monitoring on the pgbouncer instance? Anything of note there? So far this doesn’t sound like a Prisma issue and is instead a pgbouncer/db issue. GCP support will probably be able to help you more than we can

Mohammad Orabi•4mo ago

* yes as shown in gcp insights we are max of 10 * via prisma extension

const prisma = new PrismaClient().$extends(
  readReplicas({
    url: DATABASE_URL_REPLICAS
  })
);

const prisma = new PrismaClient().$extends(
  readReplicas({
    url: DATABASE_URL_REPLICAS
  })
);

* no issues shown on pg bouncer , on sql error it is connection to client lost and in prisma cant reach database we are suspecting that mainly our issue is here

jonfanz•4mo ago

Is DATABASE_URL_REPLICAS a list of connection strings? Are those connection strings for more pgbouncer instances or direct urls?

Mohammad Orabi•4mo ago

yes a list of connection strings , pg bouncers also not directs so 3 pg bouncer urls connected to 1 pg bouncer we noticed the master is making writes and reads the replicas are only doing reads

jonfanz•4mo ago

That's odd. I'm not sure that's intended. Maybe you have other instances of Prisma Client somewhere? - what version of Prisma and Prisma Client are you using? - what version of the read replicas extension are you using? - are there errors always coming from a specific database? Primary? replica? totally random?

Mohammad Orabi•4mo ago

* latest 5.14 * 0.3.0 * most of the time it starts with the primary then replicas start to also fail "other instances of Prisma Client somewhere" I am only exporting 1 code level but yes in cloud run as serverless , each instance has its own client

jonfanz•4mo ago

Okay. So far it sounds like Prisma queries are timing out or are otherwise failing to reach your db. I don't think that's a Prisma problem, but is more likely a db problem. I would reach out to GCP support to see if you can get more insights into your infrastructure

Mohammad Orabi•4mo ago

Okay thank you very much for your time and help Hello @Jon Harrell sorry for the mention , we just did some config and tests and we think we are so close to a solution we just need 1 extra prisma bit and this is our last question here since its the only prisma related thing sorry and thanks in advance lets say *25 containers each container has a prisma client *pg bouncer has 100 default-pool-size per database , and max-db-connections per database is 400 * what should be the number of connection_limit in the prisma string is it * 100 default pool size /25 num of containers = 4 * or same as default pool so also 100 regardless of containers * or unrelated to default-pool-size , its based on 400 max db -connections /25= 16 * or some other formula

jonfanz•4mo ago

I’m not sure how you have it set up, but pgbouncer’s max_pool_size is the max number of connections to a given db for a given user. So if all your app instances are using the same db user, then they will all share the same pool. If you’re worried about client connections (connections from an app instance to pgbouncer) then tweak max_client_conn. On the Prisma side, I would just use the default connection_limit. Another option would be to set everything on the pgbouncer side to default and then set Prisma’s connection_limit to 1. See how things go and then increase as needed.

Mohammad Orabi•4mo ago

yes all sharing same db user max_client_conn is currently 2000 so if all instances all using the same pool , then we can go with default pool size of 2000 max client connection / 25 max num of instances =80 or we can set it as 400 to be equal to max db connections per database? since all same user and prisma start with 1 and increase ?

jonfanz•4mo ago

I think you're mixing up the different values. Prisma to PgBouncer: How many total client connections do you want? If it's 2000 and you have 25 max instances, then I would set the prisma connection_limit to something like 50. Thats 50 * 25 + extra connections just in case. PgBouncer to Database: How many connections can each db handle? If it's 400, then set PgBouncer's max_pool_size to something smaller than that, again, so you have extra connections. I'd start with 200 and go from there.

Mohammad Orabi•4mo ago

appreciate your time and help mate thank you very much

Gaming

Programming

Setting Connection limit in gcp cloud run and cloud sql