Prisma•3mo ago

Connections are over connection limit

I deployed my NextJS + Prisma on a dedicated server then I noticed that the connections on my Postgres database is higher than the connection limit. I set my connection limit to 30 (?connection_limit=30) but the connections go to around 70 - 80. You can see it from the graph. I already made sure if the data from the graph is correct by using this query.

SELECT
  client_addr,
  COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY client_addr
ORDER BY connection_count DESC;

SELECT
  client_addr,
  COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY client_addr
ORDER BY connection_count DESC;

And, here is how I initiate the Prisma client.

import { PrismaClient } from "@prisma/client";
import { updatePrismaMetrics } from "./monitor/database";

const prismaClientSingleton = () => {
  const prisma = new PrismaClient();

  setInterval(async () => {
    updatePrismaMetrics(prisma);
  }, 10000);
  return prisma;
};

declare const globalThis: {
  prismaGlobal: ReturnType<typeof prismaClientSingleton>;
} & typeof global;

export const db = globalThis.prismaGlobal ?? prismaClientSingleton();

globalThis.prismaGlobal = db;

import { PrismaClient } from "@prisma/client";
import { updatePrismaMetrics } from "./monitor/database";

const prismaClientSingleton = () => {
  const prisma = new PrismaClient();

  setInterval(async () => {
    updatePrismaMetrics(prisma);
  }, 10000);
  return prisma;
};

declare const globalThis: {
  prismaGlobal: ReturnType<typeof prismaClientSingleton>;
} & typeof global;

export const db = globalThis.prismaGlobal ?? prismaClientSingleton();

globalThis.prismaGlobal = db;

Another weird thing is that I extracted metric from the Prisma to Statsd but The data doesn't match with data from Postgres database. The Prisma idle connection is less than data from Postgres. You can see from the graph. So, all I want to know is 1. How can connections go over limit? 2. Why are Prisma metric and Postgres data different?

Solution:

In the end, it's not problem with Pisma. It's problem with our PM2 config. We set instances to "max" which is too much. I reduced the instances number and it works now. Thank you for support. @Nurul (Prisma)

Jump to solution

13 Replies

Tauhoo•3mo ago

Here is how I send data to Statsd

export async function updatePrismaMetrics(prisma: PrismaClient) {
  if (client === null) return;
  const metrics = await prisma.$metrics.json();
  for (const { key, value } of metrics.counters) {
    client.gauge(`prisma_${host.replaceAll("_", ".")}_${key}`, value);
  }
  for (const { key, value } of metrics.gauges) {
    client.gauge(`prisma_${host.replaceAll("_", ".")}_${key}`, value);
  }
}

export async function updatePrismaMetrics(prisma: PrismaClient) {
  if (client === null) return;
  const metrics = await prisma.$metrics.json();
  for (const { key, value } of metrics.counters) {
    client.gauge(`prisma_${host.replaceAll("_", ".")}_${key}`, value);
  }
  for (const { key, value } of metrics.gauges) {
    client.gauge(`prisma_${host.replaceAll("_", ".")}_${key}`, value);
  }
}

I use node-statsd to send data

Nurul•3mo ago

Hey 👋 Can you share the output you get when running this SQL Query?

SELECT application_name, usename, state, COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY application_name, usename, state
ORDER BY connection_count DESC;

SELECT application_name, usename, state, COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY application_name, usename, state
ORDER BY connection_count DESC;

Are there any other external services connected to your database or is prisma the only one?

Tauhoo•3mo ago

Here the query result @Nurul (Prisma)

Tauhoo•3mo ago

The result from my query

SELECT
  client_addr,
  COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY client_addr
ORDER BY connection_count DESC;

SELECT
  client_addr,
  COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY client_addr
ORDER BY connection_count DESC;

Tauhoo•3mo ago

I also checked each activity one by one to see if idle activities really have the sql statements from server too.

Tauhoo•3mo ago

I only have a server container connect to the database.

Nurul•3mo ago

Thank you for running the query and sharing the output with me. To confirm, there is only a single PrismaClient instance in your application, right? You are not instantiating Prisma Client twice. Ideally, this shouldn't happen. Let me ask the ORM team to get additional insights

Tauhoo•3mo ago

@Nurul (Prisma) Thank you very much. I will try adding counter in the prismaClientSingleton function to make sure that the code didn't run more than one time too then.

mike•3mo ago

Hey im having a similar issue, Id really appreciate if you guys can fix this 😢

Tauhoo•3mo ago

I count the initiation. I notice that at the start of my container, Prisma instance created multiple times.

Tauhoo•3mo ago

prismaClientSingleton is called multiple times and globalThis.prismaGlobal always be undefined on prod. I tried changing the code and add more log.

import { PrismaClient } from "@prisma/client";
import { countPismaInitiation, updatePrismaMetrics } from "./monitor/database";
import { getLogger } from "./api/logging";

declare global {
  var prismaGlobal: PrismaClient | undefined;
}

const logger = getLogger("Prisma");

const prismaClientSingleton = () => {
  logger.info(
    "Getting Prisma Client typeof prismaGlobal: " + typeof global.prismaGlobal,
  );

  if (global.prismaGlobal !== undefined) return global.prismaGlobal;
  logger.info("Creating Prisma Client");
  const prisma = new PrismaClient();
  countPismaInitiation();
  setInterval(async () => {
    updatePrismaMetrics(prisma);
  }, 10000);

  global.prismaGlobal = prisma;
  return prisma;
};

export const db = prismaClientSingleton();

import { PrismaClient } from "@prisma/client";
import { countPismaInitiation, updatePrismaMetrics } from "./monitor/database";
import { getLogger } from "./api/logging";

declare global {
  var prismaGlobal: PrismaClient | undefined;
}

const logger = getLogger("Prisma");

const prismaClientSingleton = () => {
  logger.info(
    "Getting Prisma Client typeof prismaGlobal: " + typeof global.prismaGlobal,
  );

  if (global.prismaGlobal !== undefined) return global.prismaGlobal;
  logger.info("Creating Prisma Client");
  const prisma = new PrismaClient();
  countPismaInitiation();
  setInterval(async () => {
    updatePrismaMetrics(prisma);
  }, 10000);

  global.prismaGlobal = prisma;
  return prisma;
};

export const db = prismaClientSingleton();

global.prismaGlobal still always be undefined on prod. But, on local, it has value after being set.

Solution

Tauhoo•3mo ago

Gaming

Programming

Connections are over connection limit