node-pool and mysql. Nested queries. When should I acquire and release client?

[niska]

What is the most efficient way to use the pool module with mysql? I'm not sure when I should release the connection. My questions are in the code below. 

var http = require('http'), poolModule = require('generic-pool');

// create dbpool here using mysql module

http.createServer(function (req, res) {

dbpool.acquire(function(client) {

client.query(

'SELECT ID FROM MAINTABLE',

function (err, maintable) {

if (err) {

console.error(err.stack);

// 1. SHOULD I RELEASE HERE?

return;

}

// 2. SHOULD I RELEASE HERE?

for (var i=0; i< maintable.length; i++) {

// 3. DO I NEED TO ACQUIRE NEW CLIENT HERE. OR CAN I REUSE OLD CLIENT?

client.query(

'SELECT * FROM ANOTHERTABLE WHERE MYID = ?',

[maintable.ID]

function (err, anothertable) {

if (err) {

console.error(err.stack);

return;

}

// another loop here

// this one takes up to several seconds to complete! If possible I want to release the client so that it can be used by others...

}

);

}

// 4. SHOULD I RELEASE HERE?

...

... 

[JeffreyM]

A few things...  First, the need to use a connection pool is mitigated (in most cases) by the fact that Node is not multi-threaded.  Second, I would give some consideration to reworking your queries to avoid querying within a loop.  The comment "this one takes up to several seconds to complete" should raise serious concerns with the current design.  Not being familiar with your schema, I cannot offer specific advice, but... I suspect you could JOIN "MAINTABLE" with "ANOTHERTABLE" in such a way that would avoid multiple queries or, at the very lease, a query within an O(n) loop.

That said, releasing the connection (or returning it to a pool) within the two error check blocks makes sense.

if(err) {

   // release or return to pool here

   console.error(err.stack);

}

[niska]

On Feb 23, 8:39 pm, JeffreyM <jeffrey.mcdonn...@gmail.com> wrote:
> A few things...  First, the need to use a connection pool is mitigated (in
> most cases) by the fact that Node is not multi-threaded.

As you can see I have a http server running. If every request to the
server needs a connection, doesn't that mean that a connection pool
will help? Do you recommend creating a new connection for every
request?

> Second, I would
> give some consideration to reworking your queries to avoid querying within a

> loop.  The comment "*this one takes up to several seconds to complete"* should

> raise serious concerns with the current design.  Not being familiar with
> your schema, I cannot offer specific advice, but... I suspect you could JOIN
> "MAINTABLE" with "ANOTHERTABLE" in such a way that would avoid multiple
> queries or, at the very lease, a query within an O(n) loop.

The "thing" that takes time is actually a http request using the
request module by mikeal. The url is stored in the table. I get about
10 urls from the database using a select query. And in the resulting
loop I make a http request for each url.

Even if I make a join, it seems to me that I still have a problem with
an open connection until all requests to the urls are done. Or is
there a better way?

BTW: What is a "O(n) loop"?

[JeffreyM]

Using your example, each HTTP connection will use a SQL connection, however each will use the same SQL connection.  This is due to the single threaded nature of Node - a good thing!  I also use the generic-pool to maintain the "lifetime" of my SQL connection instance.  The primary benefit that I've found is that the pool allows me to release the connection to the pool without closing it.  Future request from the pool will return an already open connection.

My rough (untested, unreliable, untrustworthy) take on your existing example...

var dbPool = ..., // create generic pool

      var self = this;

http.createServer(function (req, res) {

  self.dbPool.acquire(function(client) {

    client.query(

      'SELECT at.url FROM MAINTABLE mt JOIN ON ANOTHERTABLE at WHERE mt.id = at.id ',

      function (err, result) {

        if (err) { return self.dbPool.release(client); }

        result.forEach(function(url) {

          self.dbPool.release(client);

          // do something with the url

        });

      }

    );

  });

});

 

Cheers

Jeffrey M.

[Felix Geisendörfer]

> First, the need to use a connection pool is mitigated (in most cases) by the fact that Node is not multi-threaded.

What do you mean by this? You need to either use a connection pool, or use one connection per query, if you want to execute multiple queries at once.

--- fg

[JeffreyM]

Poorly worded on my part... Mitigated as it pertained to the code in the original question - not to infer that there is no value in using a connection pool.  In my case, I use one simply to reuse an existing (open) connection within a module. 

JM

[Pranav Sheth]

hello niska,

can u please tell me what solution u have approached.

i am also having same problem as yours.

thanks

pranav