notiz-dev/nestjs-prisma

Excessive Database Connections When Using Prisma with PgBouncer in PM2 Clustered Environment

Closed this issue · 3 comments

caferyukseloglu commented

Description

We are encountering an issue where Prisma opens an excessive number of database connections in a clustered environment managed by PM2, despite using PgBouncer for connection pooling. This behavior persists even when the application is not actively utilizing these connections, leading to an unnecessarily high number of open connections to the PostgreSQL database.

Environment

  • NestJS Version: 10.0.0
  • Nestjs Prisma Version: 0.22.0
  • PgBouncer Version: 1.21.0
  • Libevent 2.1.12-stable
  • Adns: c-ares 1.18.1
  • Tls: OpenSSL 3.0.2 15 Mar 2022
  • Systemd: yes
  • PM2 Version: 5.3.0
  • Database (PostgreSQL) Version: 14
  • Deployment Environment: Ubuntu 22

System Specs

  • CPU: 71 CORE 2x18
  • RAM 128GB
  • 2x1 TB SSD
  • 35 Instance nestjs through pm2
  • Max Connection limit in pgbouncer 600 with 100 instance on the pg which has 300 max_con

Steps to Reproduce

  1. Configure a NestJS application with Prisma and set up PgBouncer for connection pooling.
  2. Deploy the application in a clustered environment using PM2 with multiple instances (e.g., 35 instances).
  3. Observe the number of connections Prisma establishes with the PostgreSQL database.

Expected Behavior

Prisma should manage its connection pool efficiently, especially when used in conjunction with PgBouncer. The expectation is that Prisma would open a minimal and reasonable number of connections, considering PgBouncer's own pooling capabilities.

Actual Behavior

Prisma opens a connection pool with a significant number of connections for each PM2 instance, leading to a total number of database connections that far exceeds reasonable limits. This occurs even when there is no active demand for such a high number of connections from the application.

Additional Context

This issue is critical in a production environment where resource optimization is crucial. The excessive number of connections can strain the database server and affect the overall performance and stability of the application.

Any insights or recommendations on configuring Prisma and PgBouncer to work harmoniously in a PM2 clustered environment would be greatly appreciated. Additionally, if there are any known best practices or adjustments that can be made either in the NestJS-Prisma integration or in the deployment configuration to mitigate this issue, that information would be extremely valuable.

Thank you for your attention to this matter.

caferyukseloglu commented

No Connection timeout or pooling added this is default result
prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.
PM2 | prisma:info Starting a postgresql pool with 73 connections in PgBouncer mode.

After 10 min of run which idle timeout in pgbouncer is 10 sec.

this.prisma.notifications
PM2        | → 559             .create(
PM2        | Error in connector: Error querying the database: FATAL: no more connections allowed (max_client_conn)
PM2        |     at si.handleRequestError (/var/www/panel-api/node_modules/@prisma/client/runtime/library.js:125:7007)
marcjulian commented

I am not familiar with PgBouncer and I am not sure if it is related to how PrismaService and thus PrismaClient is instantiated throughout your Nest application.

Have you checked in the prisma repo if there are any related issue with PgBouncer or have you created an issue?

I am curious if the above error also occurs when the PrismaModule is globally available, indicating that there maybe an issue with injecting/creating too many PrismaServices in the Nest app. Are you using isGlobal or are you importing PrismaModule into each of your modules? How did you configure the PrismaModule?

marcjulian commented

Closing for now. Please provide additional information or link to a prisma issue to reopen.