sequelize - connection pool size

Question

now i`m reading an article on http://docs.sequelizejs.com/manual/installation/getting-started.html

and cannot understand this sentences written below.

If you're connecting to the DB from multiple processes, you'll have to create one instance per process, but each instance should have a maximum connection pool size of "max connection pool size divided by number of instances". So, if you wanted a max connection pool size of 90 and you had 3 worker processes, each process's instance should have a max connection pool size of 30.

pool: {
  max: 5,
  min: 0,
  idle: 10000
}

what the connection pool size? is that meaning the max?

i am now understanding the connection-pool like this. if "max" is 5, and 3users want to get to the DB, 3 connections are allocated to the individual user.

and if 6users want to get the DB, 5connections are all allocated to the individual user, and since there is only 5 connections, the 6th user has to wait.

so i cannot make any sense of

each instance should have a maximum connection pool size of "max connection pool size divided by number of instances".

can anyone please explain about this?

Answer 1

This is a very broad question but here is very broad overview

Whenever we connection to db server, it actually forks a new process to fulfill that request. As you can expect, this is expensive. So pool allows us to keep the number of processes active in db server. max means that no matter how many request your app(node) gets, it will not open up a new process with the db server.

and if 6users want to get the DB, 5connections are all allocated to the individual user, and since there is only 5 connections, the 6th user has to wait.

In the above case, only 5 parallel request can run with the db server (not the app server)

Here is a good link to read

Answer 2

Here is an example demonstrating the effect of pool.max and pool.idle options.

Environment:

• "sequelize": "^5.21.3"
• node: v12.16.1
• PostgreSQL: 9.6

Client code:

db.ts:

const sequelize = new Sequelize({
  dialect: 'postgres',
  host: envVars.POSTGRES_HOST,
  username: envVars.POSTGRES_USER,
  password: envVars.POSTGRES_PASSWORD,
  database: envVars.POSTGRES_DB,
  port: Number.parseInt(envVars.POSTGRES_PORT, 10),
  define: {
    freezeTableName: true,
    timestamps: false,
  },
  pool: {
    max: 5,
    min: 0,
    idle: 10 * 1000,
  },
});
export { sequelize };

pool_test.ts:

import { sequelize } from '../../db';

for (let i = 0; i < 100; i++) {
  sequelize.query('select pg_sleep(1);');
}

Run a PostgreSQL server by docker container:

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
3c9c0fd1bf53        postgres:9.6        "docker-entrypoint.s…"   5 months ago        Up 27 hours         0.0.0.0:5430->5432/tcp   node-sequelize-examples_pg_1

Run the test code:

DEBUG=sequelize* npx ts-node ./pool_test.ts

Debug logs:

  sequelize:pool pool created with max/min: 5/0, no replication +0ms
  sequelize:connection:pg connection acquired +0ms
  sequelize:connection:pg connection acquired +38ms
  sequelize:connection:pg connection acquired +3ms
  sequelize:connection:pg connection acquired +0ms
  sequelize:connection:pg connection acquired +1ms
  sequelize:connection:pg connection acquired +1ms
  sequelize:pool connection acquired +97ms
  sequelize:sql:pg Executing (default): select pg_sleep(1); +0ms
Executing (default): select pg_sleep(1);
  sequelize:pool connection acquired +2ms
  sequelize:pool connection acquired +0ms
  sequelize:pool connection acquired +0ms
  sequelize:pool connection acquired +0ms
  sequelize:sql:pg Executing (default): select pg_sleep(1); +2ms
Executing (default): select pg_sleep(1);
  sequelize:sql:pg Executing (default): select pg_sleep(1); +2ms
Executing (default): select pg_sleep(1);
  sequelize:sql:pg Executing (default): select pg_sleep(1); +0ms
Executing (default): select pg_sleep(1);
  sequelize:sql:pg Executing (default): select pg_sleep(1); +0ms
Executing (default): select pg_sleep(1);
  sequelize:sql:pg Executed (default): select pg_sleep(1); +1s
  sequelize:pool connection released +1s
  sequelize:pool connection acquired +1ms
  sequelize:sql:pg Executed (default): select pg_sleep(1); +2ms
  sequelize:sql:pg Executed (default): select pg_sleep(1); +0ms
  sequelize:sql:pg Executed (default): select pg_sleep(1); +0ms
  sequelize:sql:pg Executed (default): select pg_sleep(1); +0ms
  sequelize:sql:pg Executing (default): select pg_sleep(1); +1ms
Executing (default): select pg_sleep(1);
  sequelize:pool connection released +1ms
  sequelize:pool connection released +0ms
  sequelize:pool connection released +0ms
  sequelize:pool connection released +0ms
  sequelize:pool connection acquired +1ms
  sequelize:pool connection acquired +0ms
  sequelize:pool connection acquired +0ms
  sequelize:pool connection acquired +0ms

Enter the docker container, check the connection process:

root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:51:34 AM
postgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14335  0.0  0.5 288384 11248 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45704) SELECT
postgres 14336  0.0  0.5 288384 11248 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45706) SELECT
postgres 14337  0.0  0.5 288384 11252 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45708) SELECT
postgres 14338  0.0  0.5 288384 11248 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45710) SELECT
postgres 14339  0.0  0.5 288384 11248 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45712) SELECT
postgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle

As you can see, there are 5(pool.max) connection processes.

After the connection processes IDLE 10(pool.idle) seconds. The connection processes will be destroyed.

root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:53:48 AM
postgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14352  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idle
postgres 14353  0.0  0.5 288384 11252 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idle
postgres 14354  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) SELECT
postgres 14355  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idle
postgres 14356  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) SELECT
root     14440  0.0  0.0  12784   972 pts/3    S+   09:53   0:00 grep postgres: testuser
postgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle
root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:53:49 AM
postgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14352  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idle
postgres 14353  0.0  0.5 288384 11252 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idle
postgres 14354  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) idle
postgres 14355  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idle
postgres 14356  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) idle
postgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle
root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:53:55 AM
postgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14352  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idle
postgres 14353  0.0  0.5 288384 11252 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idle
postgres 14354  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) idle
postgres 14355  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idle
postgres 14356  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) idle
root     14446  0.0  0.0  12784   932 pts/3    S+   09:53   0:00 grep postgres: testuser
postgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle
root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:53:58 AM
postgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idle
root     14449  0.0  0.0  12784   940 pts/3    S+   09:53   0:00 grep postgres: testuser
postgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle

client debug logs:

...
  sequelize:pool connection released +25ms
  sequelize:pool connection destroy +10s
  sequelize:pool connection destroy +0ms
  sequelize:pool connection destroy +0ms
  sequelize:pool connection destroy +0ms
  sequelize:pool connection destroy +1ms

If you change pool.max to 10, check the count of connection processes:

root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:56:51 AM
postgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14457  0.0  0.5 288384 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45728) SELECT
postgres 14458  0.0  0.5 288384 11252 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45730) SELECT
postgres 14459  0.0  0.5 288384 11252 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45732) SELECT
postgres 14460  0.0  0.5 288384 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45734) SELECT
postgres 14461  0.0  0.5 288384 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45736) SELECT
postgres 14462  0.0  0.5 288384 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45738) SELECT
postgres 14463  0.0  0.5 288384 11244 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45740) SELECT
postgres 14464  0.0  0.5 288388 11244 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45742) SELECT
postgres 14465  0.0  0.5 288388 11244 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45744) SELECT
postgres 14466  0.0  0.5 288388 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45746) SELECT
root     14472  0.0  0.0  12784   944 pts/3    S+   09:56   0:00 grep postgres: testuser
postgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle