node-ibm_db: blocking behavior

using ibm_db 1.0.1 I’m experiencing some nasty blocking behavior that is easily missed with smaller database sizes - but when I scale it up, I notice that requests come in and are handled sequentially, and the node process is entirely blocked until the request queue is completed.

Actually, I can see that the connection pool size grows and shrinks appropriately with this:

 setInterval(function() {
  try {debug('current pool size: %s', JSON.stringify(pool.availablePool['[object Object]'].length));} catch(e){}
  }, 500);

But the interval is blocked while there are active connections / queries being run (like long-running queries - upwards of 40-60seconds).

Is this behavior expected?

I found, that, so far, the only way around this was to add clustering of my node process which is not an acceptable solution.

Update: To be more clear - this blocking behavior is only apparent when concurrency increases beyond 1.

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 1
Comments: 34 (18 by maintainers)

Commits related to this issue

fix for issue #210 — committed to ibmdb/node-ibm_db by bimalkjha 7 years ago
2.0.0 * fix warning and installer update (bimalkjha) * latest node binaries for windows and nodejs version < 0.12.x support for node-ibm-db discontinued. (#228) (Rohit Pandey) * For issue #227 (bi... — committed to ibmdb/node-ibm_db by bimalkjha 7 years ago

Most upvoted comments

@kevindashgit Yes, to scale well for more than 4 parallel queries you should bump UV_THREADPOOL_SIZE accordingly. The absolute max for UV_THREADPOOL_SIZE is 128.

If 4 queries are running, each query get executed by default 4 working threads, so nearly same time. If 8 queries are running, each thread out of 4 default uv thread will have execute 2 queries simultaneously. So, time would be double for each 8 queries.

expected: 15s, 15s, 15s, 15s, 30s, 30s, 30s, 30s

In above expectation, you think that until execution of 4 queries finishes, next set of 4 query should not get control of the worker libuv thread. But, it is not the case. Here, each uv thread executes 2 queries simultaneously and hence return result after 15+30=45 second. Because fetching of combine data from server took 45 second.

To be more clear: ODBC API SQLFetch() retrieves only 1 row for each call from server. So, if a query returns 100 rows, ibm_db need to call SQLFetch() 100 times until server tell that there is no more data. Each call of SQLFetch() gets the control of worker libuv thread and releases it. So, if 2 SQL returns 2 rows, the fetch request served by the uv thread would be in following sequence (assuming 8 queries are executed by 4 uv threads, so each thread executes 2 query i.e. thread1 executes 2 query):

Fetch row1 for query1 - thread1 took 1s
Fetch row1 for query2 - thread1 took 1s
Fetch row2 for query1 - thread1 took 1s
Fetch row2 for query2 - thread1 took 1s
-- Both query1 and query2 got result simultaneously after 4 sec.

Similarly for 16 queries using UV_THREADPOOL_SIZE=4, would return result after 8 sec each. Hope, it explains. Btw, you would observe same behaviour with ibm_db@1.0.0 too. Thanks.

bimalkjha on Feb 15, 2017

reverting back to 1.0.0 solved our problem

huineng on Jan 20, 2017

I agree, as of today IBM_DB2 is no longer useable or needs to be patched by cluster i run 2 queries in 2 different tabs, i know query one takes 10 seconds and query 2 takes 2 seconds… i know this running separately

running them from the tabs (first long then fast one) the fast one will only be executed once the slow one has finished

our query is as the standard one and pooling makes no difference

huineng on Jan 20, 2017