20

I quite don't understand how connectionLimit in mysql module works.

I have created a connectionPool in following way:

  var connPool = mysql.createPool({
        host: config.get('database.host'),
        user: config.get('database.user'),
        password: config.get('database.password'),
        database: config.get('database.dbname'),
        connectionLimit: 5
  });

Now, what does limit 5 do here?

Here's what I think:

Suppose there are 5 users using my REST API for selecting some data from a table. Now in my API, I get a connection from pool, do stuff and then release the connection.

So now, suppose there are 12 users who send request to this API concurrently. So first 5 of them should get access to the data and other 7 will get error / no response. They will have to request again to get the data.

Is this like so or something else?

Vikas
  • 720
  • 1
  • 9
  • 30

1 Answers1

22

Is this like so or something else?

It's something else.

If connectionLimit is 10, the maximum number of connections that the driver will make to the database is 10.

Assuming the default driver options, if all of those connections are active (that is, they have been acquired from the pool to perform a query, but they haven't yet been released), then the next request for a connection (pool.getConnection()) will be queued: the driver will wait until one of the active connections gets released back into the pool. Your user will only notice a bit of a delay, but they won't get an error or no response; just a delayed response.

All this can be fine-tuned using the options documented here. For instance, if you prefer that instead of waiting, the driver returns an error, set waitForConnections : false.

Or, if you want the driver to return an error if, say, there are 25 requests queued to receive a connection, set queueLimit : 25.

robertklep
  • 198,204
  • 35
  • 394
  • 381
  • Okay. So Suppose I have 200 concurrent requests on my server, should I go for `connectionLimit: 200` or waiting is fine as long as there's not a big queue? (For example, setting something like `connectionLimit: 100` and `queueLimit: 100`) – Vikas Aug 05 '17 at 14:48
  • 2
    It depends, of course, but if 200 concurrent requests is going to be common on your server, than I'd say setting `connectionLimit` to that number seems to make the most sense. – robertklep Aug 05 '17 at 14:50
  • 1
    Wouldn't it be bad for my mysql server, setting 200 connections! (Default is 10). Can node mysql handle this? – Vikas Aug 05 '17 at 14:52
  • Please reply my last query – Vikas Aug 05 '17 at 14:59
  • I just checked, and by default, MySQL only allows for about 150 concurrent connections, so setting it to 200 will not work unless you increase that number. But like I said, "it depends". Try with 50 and see if all requests get serviced within an acceptable amount of time. If not, try 100, etc. – robertklep Aug 05 '17 at 15:00
  • Okay. Now what I'm thinking to use `connectionPool.query()` as it automatically releases connection. Now if the limit on pool is 5, would pool.query also follow the same rules for limit? That is, pool.query, too, will be in queue if there are already 5 concurrent requests? – Vikas Aug 08 '17 at 04:38
  • `pool.query` is just a convenience function to release the connection once the query has been performed (instead of having to release it manually). All the other rules/limitations still apply. – robertklep Aug 08 '17 at 05:38
  • So whatever be the output of `pool.query` like err, fields or rows, I don't need to release it? Is there any possibility that it would stay unreleased? – Vikas Aug 08 '17 at 06:00
  • 1
    I think the only possibility of it not being released automatically is because of a bug in the `mysql` module, which isn't likely. – robertklep Aug 08 '17 at 06:01
  • Hi @robertkelp could you please answer this question as well? https://stackoverflow.com/questions/45458151/is-this-right-way-to-release-the-connection – Vikas Aug 14 '17 at 05:26
  • 1
    @VikasKumar the comments there make sense: use `pool.query()` instead of `pool.getConnection/connection.release`. – robertklep Aug 14 '17 at 06:26
  • But I would also like to know about my other queries there. Could you please give some idea? – Vikas Aug 16 '17 at 04:26