Session timeout when using pool & parallel for

[BenCGI]

Hi guys,

I am using sequelize in my AWS Lambda. I checked out https://sequelize.org/docs/v6/other-topics/aws-lambda/ and it works well -> when I do not do long running transactions.

What I mean is the following, I have:

      this.sequelize = new Sequelize({
        ...connnectionSettings,
        dialect: 'postgres',
        pool: {
          max: 15,
          min: 0,
          idle: 0,
          acquire: 4000,
          evict: 30000,
        },
      });

Works when I do, e.g.
const [createdOrFoundEntity] = await SomeModel.upsert(entity);

But when I go for something that takes longer, e.g.

aLotEntities = [...several hundred...];
await Promise.all(
      aLotEntities.map(async (entity) => {
        const [createdOrFoundEntity] = await SomeModel.upsert(entity);
        if (createdOrFoundEntity) {
          // ToDo...
        }
      })

It runs into timeout. If I understand correctly what happens:

• 15 parallel upserts are getting executed parallel
• But all other hundreds upserts are also being called (in parallel)
• And now it works until the first upsert call, is running into a timeout -> 4 secs to get a connection (but we only have 15 available, so the wait will be longer)
• The whole upserting is taking lets say 30 secs -> so the last upsert need to wait 29,9 secs to get a connection

So yes, it cannot get a connection in 4 secs, but this is just because all available connections are in use and not because the DB is not answering.

What to do about it? Currently I put acquire as high as I know the longest transaction runs. But this is especially bad, if my DB is not available...

I would prefer, if I could distinguish "wait for connection" or "wait for free connection"...I hope it is clear what I mean. Does sequelize have something like this?

[ephys]

If you have hundreds of them, have you considered either chunking the data to call upsert in batches of 20, or using your own async queue for this operation?

It's not going to improve how fast it goes but it would solve the connection acquire timeout

[BenCGI]

Hi ephys, thanks for the quick reply. Yeah your idea is something that can be done and maybe we need to introduce it.

But I had hoped that sequelize could distinguish between "waiting to establish connection" & "waiting to get a connection from the (exhausted) pool"...because I think these are two different topics.

So there is nothing that I could use to prevent this (except paying attention that I throttle the request according to the pool size)?

[ephys]

It does have both timeouts:

• The pool.acquire option is used to configure the timeout used when acquiring a connection from the pool
• The timeout option used when establishing a connection is also configurable in most dialects, but we do not have a unified option for it (something we should likely add). You can use the dialectOptions option to configure the connector library, like below:

new Sequelize({
  dialect: 'postgres',
  // options accepted by https://node-postgres.com/apis/client
  dialectOptions: {
    connectionTimeoutMillis: 10000,
  },
})

Each dialect uses different options

If you let me know which connector library you are using, I can look up what the configuration should look like

[BenCGI]

Thanks that was exactly what I was looking for...so I can put the acquire option high, but my lambda will still fail quickly when the DB is not available (by setting connectionTimeoutMillis to a low value)!