Nest,js Typeorm: MySQL Too many connections error-CodePudding

I built a multi-tenant microservice architecture in Nest.js, multi-tenant connection are made using TypeOrm latest Datasource API. After upgrading to latest TypeORM version we encountering the MySQL "Too many connections" error.

During searching about this I found that in their latest version they added option "PoolSize" to control number of active connections. I've added that too but the issue is still there.

Ideally, TypeOrm should close the connection once the DB operation finished or use opened connection (if any) on new request, but the connection to MySQL is keeping active but in sleep state, and on a new request it create new connection. see below:

By running show processlist; cmd on MySQL

I've created the multi-tenant connection using nest.js provider for incoming request in microservice:

databaseSource is used for initial database connection to default database and then on each tenant request we create the new DB connection.

export const databaseSource = new DataSource({
      type: process.env.DB_CONNECTION,
      host: process.env.DB_HOST,
      port: parseInt(process.env.DB_PORT, 10),
      username: process.env.DB_USERNAME,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_DATABASE,
      entities: ["src/models/entities/**/*.ts"],
      synchronize: true,
      poolSize: 10,
    });

const connectionFactory = {
  provide: CONNECTION,
  scope: Scope.REQUEST,
  useFactory: async (request: RequestContext) => {
    const tenantHost = request.data["tenantId"] || request.data;
    if (tenantHost) {
      const tenantConnection: DataSource = await getTenantConnection(
        tenantHost
      );
      return tenantConnection;
    }

    return null;
  },
  inject: [REQUEST],
};

@Global()
@Module({
  providers: [connectionFactory],
  exports: [CONNECTION],
})
export class TenancyModule {}

export async function getTenantConnection(
  tenantHost: string
): Promise<DataSource> {
  const tenantId = getTenantId(tenantHost);
  const connectionName = `${tenantId}`;

  const DBConfig: DataSourceOptions = {
      type: process.env.DB_CONNECTION,
      host: process.env.DB_HOST,
      port: parseInt(process.env.DB_PORT, 10),
      username: process.env.DB_USERNAME,
      password: process.env.DB_PASSWORD,
      database: connectionName,
      entities: ["src/models/entities/**/*.ts"],
      synchronize: true,
      poolSize: 10,
  };

  const dataSource = new DataSource(DBConfig);

  if (!dataSource.isInitialized) {
     await dataSource.initialize();
  }

  return dataSource;
}

Then once datasource initalized,I inject it into Service and used it to getRepository and performed DB operation.

I researched a lot about this some saying increase the MySQL "max_connections" limit, some saying passed the "connectionLimit" options in TypeOrm config (poolSize in latest version) but nothing works for me.

Am I doing anything wrong to create the tenant connection? Is there any way to closed the connection manually after DB operation?

CodePudding user response：

Your error doesn't have to do anything with typeorm version. It's most likely that the number of tenants have increased and the way you're creating the connection, you're going to run out of connections if not now then later.

There are a number of things that you can do to make it work. First of them all would be to limit the number of connections per tenant. The correct parameter to limit the number of connections in a pool is to use connectionLimit parameter inside extra object for typeorm versions < 0.3.10 and poolSize for typeorm versions >= 0.3.10.

TypeORM is just an ORM, it delegates underlying communication to the database to the corresponding driver. In case of mysql, it uses this mysql npm module. Whatever option that you specify in dataSourceOptions is passed onto the driver. You can see the set of available pool options. You might wanna set this number to a very small value as you're going to have multiple tenants. Perhaps keep this value configurable for every tenant. A smaller number for a not so big tenant and a larger value for a very busy one. This way you'll be able to reduce overall connections pressure on your database server.

Talking about the screenshot you've pasted with high number of connections in sleep command, this is mostly due to the pool of connections made by your application. This doesn't pose any harm unless it surpasses the max_connections variable on your mysql database server. In your case, it has happened indeed that's why the error: Too many connections.

Other options you might explore is to increase the value of the variable max_connections so that you're able to accommodate all your tenants. You might also wanna increase the server size as increasing this variable will increase RAM usage, unless of course mysql is already running on a very big machine.

Edit 1: After reading your comment, I see a probable issue at this line:

if (databaseSource.options.database === connectionName) {
  //
}

databaseSource.options.database will always be equal to process.env.DB_DATABASE when the databaseSource is first initialised. Upon any subsequent request for connection for any tenantId, this check will fail and every time a new connection pool will be created.

Edit 2: Again the issue lies within your code. You're always creating a new DataSource object without checking if there is already a connection pool for that tenant. isInitialized flag will always be false for a new object and your code will do dataSource.initialize() which will create new pool. Hint: Try to keep connection pools created in a map:

const tenantPools = {
  tenantId: dataSource
}

and before creating a new DataSource object, check if that already exists in this map.