CODEWITHSUNDEEP
postgresqlsequelize.jsamazon-rds
sezanzeb
sezanzeb

Reputation: 1139

SequelizeConnectionAcquireTimeoutError out of nowhere

We suddenly started getting SequelizeConnectionAcquireTimeoutError logs 2 or 3 weeks ago, and I don't know what I can do about it anymore.

So apparently there is a pool of connections and when it is full all operations afterwards time out, because they can't get one. I therefore changed the config to:

  dialectOptions: {
    statement_timeout: 60000,
    idle_in_transaction_session_timeout: 60000
  },

  pool: {
    min: 0,
    max: 10,  // increased from 5
  },

And I upgraded the database server (AWS RDS) from 1 to 2 cores and postgres v9 to v12

But it's still happening. And there is too much going on to quickly figure out which query might be stuck, which is made even harder by how random it appears. How would the dialectOptions not prevent that anyway? That error is usually being spammed for hours until I restart the server in the morning

We don't run any complicated queries, it's really super basic REST style stuff, with a few joins here and there. I have already looked into the only complicated transaction driven chunk of code and I'm pretty sure it completes the transactions. Within try/catch and at the end of our functions we ensure the transaction is not idling around forever via:

if (transaction && !transaction.finished) {
  await transaction.rollback()
}

Is that correct or is it not what we think it does?

For people with the same problem: there is this github issue which might help and that has gotten a lot of comments https://github.com/sequelize/sequelize/issues/10858

Sequelize v6.3.5

update: the dialectOptions did nothing, I tried to set that stuff manually like that:

await Sequelize.query("SET statement_timeout = '1ms';")
await Sequelize.query("SET idle_in_transaction_session_timeout = '1ms';")
console.log('statement_timeout', await Sequelize.query('SHOW statement_timeout;'))
console.log('idle_in_transaction_session_timeout', await Sequelize.query('SHOW idle_in_transaction_session_timeout;'))

but it gets reset to 0 again shortly afterwards. There is no usage of those settings anywhere in our code except for the lines above and the dialectOptions.

setting the connection pool to exactly 1 connection makes that setting to be kept, which implies that each new connection gets the default value instead

Upvotes: 4

Views: 4427

Answers (1)

sezanzeb
sezanzeb

Reputation: 1139

The pg module also had to be updated to support the statement_timeout and idle_in_transaction_session_timeout dialectOptions. They are applied to all connections now.

I verified that by logging those two settings to the console in one of our api endpoints:

console.log('statement_timeout', await Sequelize.query('SHOW statement_timeout;'))
console.log('idle_in_transaction_session_timeout', await Sequelize.query('SHOW idle_in_transaction_session_timeout;'))

which would previously log 0 (default, disabled) all the time instead. Furthermore, setting the timeout to 1ms correctly cancels the queries now. I guess that will help to figure out which queries or transactions are stuck thanks to the sql property of the thrown errors.

However, the native driver doesn't support it (see https://github.com/brianc/node-postgres/pull/2323), so native of the Sequelize options is set to false now.

Upvotes: 5

Related Questions