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?

score 6 · Accepted Answer · answered May 29 '18 at 16:28

6

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

answered May 29 '18 at 16:28

AbhinavD

6,892
5
30
40

appreciate for answering this old question. so pool size is a way to saving costs from forking new process every time. sequelize doc has a guide code for connection pool size max:5, but this is anyway guide code. so which max pool size is appropriate for medium size application? (not meaning large services like facebook etc) maybe 100? – jwkoo May 29 '18 at 16:40
even if I use connection pool size, if I set connection pool size for very large number and less user, there will be difference whether setting pool size or not. @AbhinavD – jwkoo May 29 '18 at 16:42
3

It is very hard to say what is the right number.Depends on the ratio of read/write queries( as write locks the row), your hardware etc. AS mentioned in the doc I shared, there is `the "Knee"` beyond which the db performance will go down if you increase the pool size. Also, when you install db, the db config itself tells it what is the max it can handle. You would have to change that and your app pool size to make it work. – AbhinavD May 29 '18 at 16:45
thank you so much! can you check my last comment if I am properly get your words in the previous question(sequelize)? – jwkoo May 29 '18 at 16:48
1

I think you got it. Since I never use `sync`, I always create the foreign key in the model also – AbhinavD May 29 '18 at 16:51

Lin Du · Answer 2 · 2020-08-06T10:01:47.640

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

Does this mean that if you have pool.max = 5, and you have 50 users hitting your DB at the same time, 45 of them will have to wait for the first 5 to complete? — VikR, Sep 16 '22 at 19:55

sequelize - connection pool size

2 Answers2