Objection.js: knex pool exhausted, recommended knex pool settings

[bryanbrunt]

My app which is game recently was made available on Steam, and we're seeing more users.

After one week of availability we started seeing knex connection pool issues. We're using the latest knex and objection.js with postgresql. The knex connection pool was exhausted and queries failed. We don't use transactions, just plain objection model queries.

Do objection users have a recommended knex pool min and max size settings for a production system?

It seems odd that the default knex pool size is so low. The Postgresql default connection limit is 100.

Why would knex have such a low default connection pool limit? I think it is 10.

I hope this isn't off topic. I see some history about a potential tarn.js variant of knex by the objection author. Has development of that variant been stopped?

[kibertoad]

@bryanbrunt Having larger connection pool can actually harm your performance. Most likely you have some problem with long-running queries that could actually be made worse by larger connection limits. This is a recommended reading: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

On unrelated note, can I reach you somewhere somehow (have a question regarding developing games on node.js)? I don't think you are on gitter; I can send you my email if that would be a preferred option :)

[koskimas]

@bryanbrunt @kibertoad Is right, a bigger pool will only hurt your performance. You probably simply have too slow queries, or too small db server. Another reason could be that you leak connections somehow, but since you don't use transactions, that's unlikely.

Knex currently does use tarn. I don't believe knex leaks connections since it's very widely used.

[koskimas]

We can continue the discussion here, but this isn't really objection-related so I'll close this.

[kibertoad]

@bryanbrunt Could you ping me on gitter? I could help with reviewing db structure/queries/indexes etc to see what's the problem :)

[bryanbrunt]

Sorry for the delay on getting back to this issue. And I understand that it isn't objection-related, but when a default config seems completely unsuited to a production situation, couldn't objection.js recommend changing it? A limit of 10 connections for knex is just way to low.

I set the connection limit in the knex file to this and we haven't seen this issue again:

pool: { min: 5, max: 30 }

kibertoad: you can reach me at [email protected]

The game is here: www.denizenseven.com

Thanks!

[bryanbrunt]

Is the knex documentation out of date?

It says that it uses generic-pool, not tarn.

https://knexjs.org/

[kibertoad]

@bryanbrunt It is fixed in master, but will only be rebuilt after 0.16.0 is released.

[bryanbrunt]

yeah, the knex 0.5.12 package.json says:

"tarn": "^1.1.4",

[kibertoad]

Yup, I meant documentation; pool itself was changed quite a bit of time before that.

[bryanbrunt]

Is the knexfile.js pool setting identical for tarn?

pool: { min: 5, max: 30 }

Perhaps this setting does nothing on knex 0.5.12?

[kibertoad]

See https://github.com/vincit/tarn.js/ for supported params.

I think min and max are correct params. Have you tried debugging into initialization function to make sure that your parameters are actually being passed? There was a similar issue https://github.com/tgriesser/knex/issues/2892 that boiled down to parameters not actually being set correctly.

[kibertoad]

0.5.12 is a veeeeeery old version, though, it actually may not support it. Do you really want to use it?

[bryanbrunt]

The latest version of knex is: 0.15.2.

It's the release that everyone who performs an npm i knex receives.

I haven't tried debugging to see these params are being passed. This is kind of discouraging. Is there updated knex documentation somewhere on how to configure tarn?

[kibertoad]

Right, so you've meant 0.15.2. Yup, that configuration should be correct for it.
As I've said, https://github.com/vincit/tarn.js/ has documentation for parameters supported by tarn. Knex just passes params as-is to it.
If you pasted your config, I could try reproducing the issue.

[bryanbrunt]

Thank you anyway, but the issue hasn't shown itself since the update to the config.

It's impossible to say what caused it actually.

A question: in a pool with 10 limit and 30 simultaneous queries. The pool will gracefully block the extra queries and it shouldn't throw these type of errors? I assume that the pool won't just exhaust and refuse to deal queries above the limit.

[kibertoad]

@bryanbrunt Pool will block queries until timeout is reached, which by default is 30000 ms (controlled by param createTimeoutMillis)

[gambhirpulkit]

@kibertoad I read above thread. I'm using knex 0.16.0 but still the issue of pool full is coming Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

I've taken free tier RDS and set up the right security groups and running knex migrate:latest --env production from my ec2 server.

Can you help, please?

[sozakir]

Hi, I am also facing the Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?.

I am using pool values as { min: 0, max: 5 }

Is there any recommended pool size for SQL server in Knex. Or we should avoid setting a pool
and use the default one. {min: 2, max: 10}. As suggested by @kibertoad increasing pool size is not
always is a better idea.

Any suggestion. @bryanbrunt @kibertoad

[elhigu]

@sozakir usually your error happend because of having a bug in your application code. Recommended pool size depends completely on your DB server / application. For light use min: 0 max: 10 is usually reasonable. If your DB server is not constraining amount of connections to 5 there is no reason to set it that low.

[koskimas]

@sozakir That error usually happens when you start a transaction and then run a query WITHOUT that transaction while the transaction is running, creating a deadlock.

So consider that you have a pool with max 1 connections and you do this:

await knex.transaction(trx => {
  // This doesn't use `trx`.
  await knex('foo').select('bar')
})

What happens is that knex.transaction line acquires one database connection (the only one) and then knex('foo').select('bar') waits until it gets a connection, which it never does because the transaction has taken the connection and will never release it because we are waiting for the knex('foo').select('bar') to finish.

This happens with multiple connections easily too. It's just easiest to explain using one connection. And while the example is using knex the exact same thing happens with objection if you forget to always use the transaction when it's started.

One easy way to catch these errors in tests is to set the pool max: 1 so that it always happens. Another good idea is to not install the knex instance globally using Model.knex(knex). That way if you forget to provide the transaction for a query, it explodes.

[sozakir]

@elhigu I agree with you setting the Pool size to 5 won't be a reasonable approach. I am thinking of using { min: 0 max: 10 } and try out new values.

[sozakir]

@koskimas I am not using transactions in my queries. I will debug and check with pool size 1. Thanks for the suggestions.