Broken out from prisma2/#556 on request from @janpio .
Similar to specs/#386 and prisma2/#1438, we have been attempting to use Prisma2 against a Digital Ocean PostgreSQL 11 database service.
Operating without pgbouncer, the application initially runs fine, but eventually runs out of connections. When this happens, the query engine also seems to spin and use all available CPU.
Since this seems to be a connection release/starvation issue, we added pgbouncer in front of Postgres. Digital Ocean offers pgbouncer-based pools with transaction, session, and statement settings. With a transactional pool in place, we receive errors with prepared statements, e.g. "prepared statement 's1599' does not exist."
We've also tested a session pool but it still ran out of connections eventually.
We are using the &connection_limit=15 argument in our connection string (without pgbouncer), but are still seeing the issue. We're currently using preview21, and planning to update to preview22 shortly. We're hoping that using the flag discussed in specs/#386 plus a transactional pool will solve the issue against a Digital Ocean provided pgbouncer/postgres.
We're also exploring moving to mysql, but it's requiring additional work to get around some of the current limitations with how Prisma2 handles strings in mysql, along with some unrelated innodb issues. Postgres is definitely our preferred technology for our project.
On recommendations from the Prisma team, we modified the client code to add the --always_force_transactions flag to the query engine invocation here: https://github.com/prisma/prisma-client-js/blob/7ee403f1b255e8506d92b675d53bf9a8b2bea097/packages/engine-core/src/NodeEngine.ts#L336
With this in place, the first query executed would work, but queries afterwards would fail with "Error in connector: Error creating a database connection." I believe this is due to the Digital Ocean pgbouncer not having the settings mentioned here: prisma/prisma-engines#403
Doing some research, it seems like these are uncommon settings, with pgbouncer defaulting them off and not recommending them. This can be worked around, but might make it difficult for Prisma2 to work with some of the major providers: pgbouncer/pgbouncer#110 (comment)
I've worked on setting up a custom pgbouncer server in front of the Digital Ocean managed Postgres, but their SSL configuration looks like this may not be an option.
We're continuing to look for workarounds and configuration changes to get this working.
Broken out from prisma2/#556 on request from @janpio .
Similar to specs/#386 and prisma2/#1438, we have been attempting to use Prisma2 against a Digital Ocean PostgreSQL 11 database service.
Operating without pgbouncer, the application initially runs fine, but eventually runs out of connections. When this happens, the query engine also seems to spin and use all available CPU.
Since this seems to be a connection release/starvation issue, we added pgbouncer in front of Postgres. Digital Ocean offers pgbouncer-based pools with transaction, session, and statement settings. With a transactional pool in place, we receive errors with prepared statements, e.g. "prepared statement 's1599' does not exist."
We've also tested a session pool but it still ran out of connections eventually.
We are using the &connection_limit=15 argument in our connection string (without pgbouncer), but are still seeing the issue. We're currently using preview21, and planning to update to preview22 shortly. We're hoping that using the flag discussed in specs/#386 plus a transactional pool will solve the issue against a Digital Ocean provided pgbouncer/postgres.
We're also exploring moving to mysql, but it's requiring additional work to get around some of the current limitations with how Prisma2 handles strings in mysql, along with some unrelated innodb issues. Postgres is definitely our preferred technology for our project.
On recommendations from the Prisma team, we modified the client code to add the --always_force_transactions flag to the query engine invocation here: https://github.com/prisma/prisma-client-js/blob/7ee403f1b255e8506d92b675d53bf9a8b2bea097/packages/engine-core/src/NodeEngine.ts#L336
With this in place, the first query executed would work, but queries afterwards would fail with "Error in connector: Error creating a database connection." I believe this is due to the Digital Ocean pgbouncer not having the settings mentioned here: prisma/prisma-engines#403
Doing some research, it seems like these are uncommon settings, with pgbouncer defaulting them off and not recommending them. This can be worked around, but might make it difficult for Prisma2 to work with some of the major providers: pgbouncer/pgbouncer#110 (comment)
I've worked on setting up a custom pgbouncer server in front of the Digital Ocean managed Postgres, but their SSL configuration looks like this may not be an option.
We're continuing to look for workarounds and configuration changes to get this working.