New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Limit the amount of connections at once #175

Closed
nathggns opened this Issue Jan 6, 2014 · 23 comments

Comments

Projects
None yet
6 participants
@nathggns
Contributor

nathggns commented Jan 6, 2014

While I'm aware that Bookshelf doesn't actually handle connecting to the database, it would still be nice to limit the amount of concurrent connections.

For example, my app is an Heroku, which limits the allowed connections to 20, and it causes my app to crash with pg error if I have more than 20 concurrent users (tested via siege).

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 6, 2014

Member

Hey - it sort of does, in that Knex does... if you pass a pool object to the initialize config, you can set the max/min connection count.

Bookshelf.initialize({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
    charset  : 'utf8'
  }
  pool: {
    max: 20,
    min: 0
  }
});

The default should be 10 / 2 for postgres & mysql though... are you seeing the app currently crashing with bookshelf code when you're running this load testing? If so, it might be a sign there's something else wrong (either with the pooling or something else).

Member

tgriesser commented Jan 6, 2014

Hey - it sort of does, in that Knex does... if you pass a pool object to the initialize config, you can set the max/min connection count.

Bookshelf.initialize({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test',
    charset  : 'utf8'
  }
  pool: {
    max: 20,
    min: 0
  }
});

The default should be 10 / 2 for postgres & mysql though... are you seeing the app currently crashing with bookshelf code when you're running this load testing? If so, it might be a sign there's something else wrong (either with the pooling or something else).

@johanneslumpe

This comment has been minimized.

Show comment
Hide comment
@johanneslumpe

johanneslumpe Jan 6, 2014

Contributor

Actually from what I see in the source, the config is set to have a maximum of 10 connections in the pool. But you should be able to actually pass a pool config when initializing Bookshelf. Something along those lines:

Bookshelf.DB = Bookshelf.initialize({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'root',
    password : '',
    database : 'somedb',
    charset  : 'utf8'
  },
  pool : {
    min: 2,
    max: 5
  }
});

This is untested, but from looking at the source this should work, as Bookshelf passes the config down to Knex and it again passes the config down to the client, omitting the client property. The client then passes an object to the pool constructor which has been extended with the client's pool defaults and config.pool.

Contributor

johanneslumpe commented Jan 6, 2014

Actually from what I see in the source, the config is set to have a maximum of 10 connections in the pool. But you should be able to actually pass a pool config when initializing Bookshelf. Something along those lines:

Bookshelf.DB = Bookshelf.initialize({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'root',
    password : '',
    database : 'somedb',
    charset  : 'utf8'
  },
  pool : {
    min: 2,
    max: 5
  }
});

This is untested, but from looking at the source this should work, as Bookshelf passes the config down to Knex and it again passes the config down to the client, omitting the client property. The client then passes an object to the pool constructor which has been extended with the client's pool defaults and config.pool.

@nathggns

This comment has been minimized.

Show comment
Hide comment
@nathggns

nathggns Jan 6, 2014

Contributor

I can see that it should work but my app shouldn't crash until there is more than 20 concurrent database connections, which that max should make impossible. Yet, we see crashes under load testing.

Sent from my iPhone

On 6 Jan 2014, at 21:21, Johannes Lumpe notifications@github.com wrote:

Actually from what I see in the source, the config is set to have a maximum of 10 connections in the pool. But you should be able to actually pass a pool config when initializing Bookshelf. Something along those lines:

Bookshelf.DB = Bookshelf.initialize({
client: 'mysql',
connection: {
host : '127.0.0.1',
user : 'root',
password : '',
database : 'somedb',
charset : 'utf8'
},
pool : {
min: 2,
max: 5
}
});
This is untested, but from looking at the source this should work, as Bookshelf passes the config down to Knex and it again passes the config down to the client, omitting the client property. The client then passes an object to the pool constructor which has been extended with the client's pool defaults and config.pool.


Reply to this email directly or view it on GitHub.

Contributor

nathggns commented Jan 6, 2014

I can see that it should work but my app shouldn't crash until there is more than 20 concurrent database connections, which that max should make impossible. Yet, we see crashes under load testing.

Sent from my iPhone

On 6 Jan 2014, at 21:21, Johannes Lumpe notifications@github.com wrote:

Actually from what I see in the source, the config is set to have a maximum of 10 connections in the pool. But you should be able to actually pass a pool config when initializing Bookshelf. Something along those lines:

Bookshelf.DB = Bookshelf.initialize({
client: 'mysql',
connection: {
host : '127.0.0.1',
user : 'root',
password : '',
database : 'somedb',
charset : 'utf8'
},
pool : {
min: 2,
max: 5
}
});
This is untested, but from looking at the source this should work, as Bookshelf passes the config down to Knex and it again passes the config down to the client, omitting the client property. The client then passes an object to the pool constructor which has been extended with the client's pool defaults and config.pool.


Reply to this email directly or view it on GitHub.

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 6, 2014

Member

Is the crash due to too many concurrent database connections?

Member

tgriesser commented Jan 6, 2014

Is the crash due to too many concurrent database connections?

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 6, 2014

Member

Or, what other info is there along with the crash that might be helpful in tracking down the issue?

Member

tgriesser commented Jan 6, 2014

Or, what other info is there along with the crash that might be helpful in tracking down the issue?

@nathggns

This comment has been minimized.

Show comment
Hide comment
@nathggns

nathggns Jan 6, 2014

Contributor

Yes, too many concurrent database connections. I've been digging into it, but I'm just getting more and more confused. The pool never has more than 10 connections, but the limit in the db is set to 20. There shouldn't be any errors.

Contributor

nathggns commented Jan 6, 2014

Yes, too many concurrent database connections. I've been digging into it, but I'm just getting more and more confused. The pool never has more than 10 connections, but the limit in the db is set to 20. There shouldn't be any errors.

@nathggns

This comment has been minimized.

Show comment
Hide comment
@nathggns

nathggns Jan 6, 2014

Contributor

Maybe the app is retaining connections even when they're supposed to have ended.

Contributor

nathggns commented Jan 6, 2014

Maybe the app is retaining connections even when they're supposed to have ended.

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 6, 2014

Member

How does this load testing work? You're sure you're only ever calling .initialize once in your application? Are you spinning up multiple instances of the app? The total number of connections to the database needs to be adjusted for instances of the app running... so if you are running a heroku app with 3 dynos, each maxed at 10 connections, then you're potentially creating up to 30 connections to the same database and you need to adjust the numbers accordingly.

Member

tgriesser commented Jan 6, 2014

How does this load testing work? You're sure you're only ever calling .initialize once in your application? Are you spinning up multiple instances of the app? The total number of connections to the database needs to be adjusted for instances of the app running... so if you are running a heroku app with 3 dynos, each maxed at 10 connections, then you're potentially creating up to 30 connections to the same database and you need to adjust the numbers accordingly.

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 6, 2014

Member

Yeah, that's likely, I think with heroku there's a shutdown window of like 30 seconds for an app where it might still have resources around... if you call bookshelf.knex.client.pool.destroy() it should clear them all immediately (if you want to do that on a specified shutdown signal or something)

Member

tgriesser commented Jan 6, 2014

Yeah, that's likely, I think with heroku there's a shutdown window of like 30 seconds for an app where it might still have resources around... if you call bookshelf.knex.client.pool.destroy() it should clear them all immediately (if you want to do that on a specified shutdown signal or something)

@nathggns

This comment has been minimized.

Show comment
Hide comment
@nathggns

nathggns Jan 6, 2014

Contributor

Nah, limiting it to 1 cluster was the first thing I did to try and debug this.

I've set my local PG connection limit to 20 to try and debug this.

I've noticed that getRawConnection inside the knex client is getting called far more than 10 times too, and destroyRawConnection is never called.

Contributor

nathggns commented Jan 6, 2014

Nah, limiting it to 1 cluster was the first thing I did to try and debug this.

I've set my local PG connection limit to 20 to try and debug this.

I've noticed that getRawConnection inside the knex client is getting called far more than 10 times too, and destroyRawConnection is never called.

@nathggns

This comment has been minimized.

Show comment
Hide comment
@nathggns

nathggns Jan 6, 2014

Contributor

This has little to do with heroku and more to do connection limits. I've managed to reproduce it locally with a low connection limit.

I'm just sending a high amount of concurrent web requests using siege.

Contributor

nathggns commented Jan 6, 2014

This has little to do with heroku and more to do connection limits. I've managed to reproduce it locally with a low connection limit.

I'm just sending a high amount of concurrent web requests using siege.

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 6, 2014

Member

Are you calling Bookshelf.initialize (or Knex.initialize) more than once? That's the only think I can think of for why it would be called more than 10 times.

Member

tgriesser commented Jan 6, 2014

Are you calling Bookshelf.initialize (or Knex.initialize) more than once? That's the only think I can think of for why it would be called more than 10 times.

@nathggns

This comment has been minimized.

Show comment
Hide comment
@nathggns

nathggns Jan 6, 2014

Contributor

Nope, just the once.

On 6 January 2014 23:06, Tim Griesser notifications@github.com wrote:

Are you calling Bookshelf.initialize (or Knex.initialize) more than once?
That's the only think I can think of for why it would be called more than
10 times.


Reply to this email directly or view it on GitHubhttps://github.com//issues/175#issuecomment-31697273
.

Thanks,
Nathaniel Higgins
http://nath.is
@nathggns http://twitter.com/nathggns

Contributor

nathggns commented Jan 6, 2014

Nope, just the once.

On 6 January 2014 23:06, Tim Griesser notifications@github.com wrote:

Are you calling Bookshelf.initialize (or Knex.initialize) more than once?
That's the only think I can think of for why it would be called more than
10 times.


Reply to this email directly or view it on GitHubhttps://github.com//issues/175#issuecomment-31697273
.

Thanks,
Nathaniel Higgins
http://nath.is
@nathggns http://twitter.com/nathggns

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 6, 2014

Member

Actually @nathggns want to jump in #bookshelf on IRC and I can try to debug there?

Member

tgriesser commented Jan 6, 2014

Actually @nathggns want to jump in #bookshelf on IRC and I can try to debug there?

@nathggns

This comment has been minimized.

Show comment
Hide comment
@nathggns

nathggns Jan 6, 2014

Contributor

What server?

On 6 January 2014 23:07, Tim Griesser notifications@github.com wrote:

Actually @nathggns https://github.com/nathggns want to jump in
#bookshelf on IRC and I can try to debug there?


Reply to this email directly or view it on GitHubhttps://github.com//issues/175#issuecomment-31697383
.

Thanks,
Nathaniel Higgins
http://nath.is
@nathggns http://twitter.com/nathggns

Contributor

nathggns commented Jan 6, 2014

What server?

On 6 January 2014 23:07, Tim Griesser notifications@github.com wrote:

Actually @nathggns https://github.com/nathggns want to jump in
#bookshelf on IRC and I can try to debug there?


Reply to this email directly or view it on GitHubhttps://github.com//issues/175#issuecomment-31697383
.

Thanks,
Nathaniel Higgins
http://nath.is
@nathggns http://twitter.com/nathggns

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 6, 2014

Member

Freenode

Member

tgriesser commented Jan 6, 2014

Freenode

@johanneslumpe

This comment has been minimized.

Show comment
Hide comment
@johanneslumpe

johanneslumpe Jan 15, 2014

Contributor

@nathggns Has this been solved?

Contributor

johanneslumpe commented Jan 15, 2014

@nathggns Has this been solved?

@nathggns

This comment has been minimized.

Show comment
Hide comment
@nathggns

nathggns Jan 15, 2014

Contributor

No. Still trying to work this out.

Sent from my iPhone

On 15 Jan 2014, at 21:35, Johannes Lumpe notifications@github.com wrote:

@nathggns Has this been solved?


Reply to this email directly or view it on GitHub.

Contributor

nathggns commented Jan 15, 2014

No. Still trying to work this out.

Sent from my iPhone

On 15 Jan 2014, at 21:35, Johannes Lumpe notifications@github.com wrote:

@nathggns Has this been solved?


Reply to this email directly or view it on GitHub.

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jan 15, 2014

Member

It'd actually be nice if we could somehow detect in knex if the connection limit is getting hit and if so stop trying to create new connections on that instance... rather than mess up the application.

Member

tgriesser commented Jan 15, 2014

It'd actually be nice if we could somehow detect in knex if the connection limit is getting hit and if so stop trying to create new connections on that instance... rather than mess up the application.

@tgriesser tgriesser added the question label Jun 9, 2014

@tgriesser

This comment has been minimized.

Show comment
Hide comment
@tgriesser

tgriesser Jun 9, 2014

Member

Not sure what the best solution would be here, or what was causing this issue originally. If you're still having this problem, please open a ticket in knex's repo and we can discuss.

Member

tgriesser commented Jun 9, 2014

Not sure what the best solution would be here, or what was causing this issue originally. If you're still having this problem, please open a ticket in knex's repo and we can discuss.

@tgriesser tgriesser closed this Jun 9, 2014

@recursivefunk

This comment has been minimized.

Show comment
Hide comment
@recursivefunk

recursivefunk Mar 25, 2016

I'm having this issue as well. Keep in mind if you're on heroku and are using a worker process in addition to a web process and they both connect to the database you are potentially doubling your connection number. For instance, if you're configuration is maxed at 10 but two processes connect you're looking at the max of 20 overall. In addition, if you use a tool to explore your data locally, that tool will connect and count against the limit. So, two processes with a max of 10 (assuming you're at the max) and a third party tool to view data potentially puts you at 21 connections and will cause errors.

recursivefunk commented Mar 25, 2016

I'm having this issue as well. Keep in mind if you're on heroku and are using a worker process in addition to a web process and they both connect to the database you are potentially doubling your connection number. For instance, if you're configuration is maxed at 10 but two processes connect you're looking at the max of 20 overall. In addition, if you use a tool to explore your data locally, that tool will connect and count against the limit. So, two processes with a max of 10 (assuming you're at the max) and a third party tool to view data potentially puts you at 21 connections and will cause errors.

@rhys-vdw

This comment has been minimized.

Show comment
Hide comment
@rhys-vdw

rhys-vdw Mar 27, 2016

Contributor

@recursivefunk This is a discussion to be had over at https://github.com/tgriesser/knex/issues

Contributor

rhys-vdw commented Mar 27, 2016

@recursivefunk This is a discussion to be had over at https://github.com/tgriesser/knex/issues

@ariden83

This comment has been minimized.

Show comment
Hide comment
@ariden83

ariden83 Nov 28, 2016

when you do bookshelf.knex.client.pool.destroy()

how to recreate it ?

ariden83 commented Nov 28, 2016

when you do bookshelf.knex.client.pool.destroy()

how to recreate it ?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment