Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

OLTP type fast path without prepare #32

Closed
amcintyre99 opened this Issue · 81 comments

2 participants

@amcintyre99

Any chance of getting functions that avoid the SQLPrepare call each time?

They would consist of:
1 - call for SQLPrepare - to prepare hStmt up front
2 - call for SQLBindParameter/SQLExecute using the hStmt from the prepare

Would be used by high volume guys to reduce cpu. Like with billions of inserts a day for example...

@wankdanker
Collaborator

This is a good idea. Should be very doable. Time is the only issue for getting it done.

If you could provide a some mock-javascript on how you see it working, that would be great. Here is what I am thinking

var stmt = db.prepare('insert into data (col1, col2) values (?,?)');

/* other stuff here */

function someServerRequestHandler(req, res) {
    stmt.bind([req.form.col1, req.form.col2]);
    stmt.execute(function (err, data) {
        res.end('done');
    });
}
@amcintyre99

I'm a javascript/node noobie so my mock code probably wouldn't compile. :-)

Yours looks great to me. And I'll be glad to test this anytime you want (or you have time). With some measurable volume for cpu comparison using db2 v10.

Thanks!!

@amcintyre99

And having all three calls separate (prepare/bind/execute) would be the best for sure!!

So:
prepare once
bind once (as long as data types never change)
execute many

@wankdanker
Collaborator

The groundwork for this has been laid out (https://github.com/wankdanker/node-odbc/tree/statements-and-connections-oh-my). You can experiment by installing that branch:

npm install git://github.com/wankdanker/node-odbc.git#statements-and-connections-oh-my

Then the code would look something like:

var odbc = require('odbc');

var db = new odbc.ODBC();

db.createConnection(function (err, conn) {
    conn.open('connectinon string', function (err) {
        conn.createStatement(function (err, stmt) {
            stmt.prepare('select ? as test', function (err) {
                stmt.bindSync(['testing']);

                stmt.execute(function (err, result) {
                    result.fetchAll(function (err, data) {
                        console.log(data);
                        //then you could stmt.bindSync() again and stmt.execute().
                    });
                });
            });
        });
    }); 
});

I only tested this one time and it worked, but I may have broken something since. :) I will likely modify the API so that you don't have to go through all this just to prepare, bind and execute and make it more like I originally mentioned above. If you get a chance to test and report back anything, that would be super helpful.

Thanks,

Dan

@amcintyre99

Finally got to this.

On 8.23 and installed with your command and all existing code works without any changes. Now on to changing the code to split out the prepare and bind. I'll let you know... and thanks!!

Got these messages during the install, in case any are bad...

npm install git://github.com/wankdanker/node-odbc.git#statements-and-connections-oh-my
npm WARN engine odbc@0.5.1: wanted: {"node":">=0.10.0"} (current: {"node":"v0.8.23","npm":"1.2.18"})

odbc@0.5.1 preinstall /home/andrew/dma/prd/node_modules/odbc
node-gyp configure build

make: Entering directory /home/andrew/dma/prd/node_modules/odbc/build'
CXX(target) Release/obj.target/odbc_bindings/src/odbc.o
../src/odbc.cpp: In static member function ‘static v8::Handle<v8::Value> ODBC::New(const v8::Arguments&)’:
../src/odbc.cpp:119:7: warning: unused variable ‘ret’ [-Wunused-variable]
../src/odbc.cpp: In static member function ‘static void ODBC::UV_CreateConnection(uv_work_t*)’:
../src/odbc.cpp:171:7: warning: unused variable ‘ret’ [-Wunused-variable]
../src/odbc.cpp: In static member function ‘static v8::Handle<v8::Value> ODBC::CreateConnectionSync(const v8::Arguments&)’:
../src/odbc.cpp:218:13: warning: unused variable ‘ret’ [-Wunused-variable]
../src/odbc.cpp: In static member function ‘static v8::Local<v8::Array> ODBC::GetAllRecordsSync(HENV, HDBC, HSTMT, uint16_t*, int)’:
../src/odbc.cpp:745:1: warning: no return statement in function returning non-void [-Wreturn-type]
CXX(target) Release/obj.target/odbc_bindings/src/odbc_connection.o
../src/odbc_connection.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCConnection::QuerySync(const v8::Arguments&)’:
../src/odbc_connection.cpp:776:14: warning: ‘params’ may be used uninitialized in this function [-Wuninitialized]
CXX(target) Release/obj.target/odbc_bindings/src/odbc_statement.o
../src/odbc_statement.cpp: In static member function ‘static void ODBCStatement::UV_Bind(uv_work_t*)’:
../src/odbc_statement.cpp:641:21: warning: ‘ret’ may be used uninitialized in this function [-Wuninitialized]
../src/odbc_statement.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCStatement::BindSync(const v8::Arguments&)’:
../src/odbc_statement.cpp:536:3: warning: ‘ret’ may be used uninitialized in this function [-Wuninitialized]
CXX(target) Release/obj.target/odbc_bindings/src/odbc_result.o
../src/odbc_result.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCResult::CloseSync(const v8::Arguments&)’:
../src/odbc_result.cpp:486:3: warning: variable ‘closeOption’ set but not used [-Wunused-but-set-variable]
SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node
SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node: Finished
COPY Release/odbc_bindings.node
make: Leaving directory
/home/andrew/dma/prd/node_modules/odbc/build'
npm http GET https://registry.npmjs.org/bindings
npm http 304 https://registry.npmjs.org/bindings
odbc@0.5.1 node_modules/odbc
└── bindings@1.0.0

@amcintyre99

Something wrong when I try and call db.prepare. Maybe those install messages are an issue? but again, this version works with my original code.

var stmt = db.prepare(sqlc)  
              ^

TypeError: Object # has no method 'prepare'
at /home/andrew/dma/prd/p1qrun.js:175:19
at Database.open (/home/andrew/dma/prd/node_modules/odbc/lib/odbc.js:63:14)

I just looked more closely at this msg from the install:
npm WARN engine odbc@0.5.1: wanted: {"node":">=0.10.0"} (current: {"node":"v0.8.23","npm":"1.2.18"})

Do I have to be on 10.0 to test this? was hoping to put that off for a few more days...

@wankdanker
Collaborator

Hey! Thanks for the feedback. The version you have installed does not have the prepare method exposed as you are using it. Try installing with:

npm install git://github.com/wankdanker/node-odbc.git#v0.5

A bunch of those warnings should be gone and there is a lot more progress in the v0.5 branch including the prepareSync method. See https://github.com/wankdanker/node-odbc/tree/v0.5#preparesyncsql

In v0.5 there are/will be synchronous and asynchronous versions of every method. To get a stmt object the way you have shown above you will need to use synchronous version of the prepare method.

var stmt = db.prepareSync(sqlc);

or asynchronously...

db.prepare(sqlc, function (err, stmt) {
    //do stuff  
});

You do not have to use v0.10 for this. I have only been testing v0.10 though, so there may be bugs with other versions. If npm does not let you install it because the package.json file says >0.10.0, then you can probably force it with npm install -f ...

Good luck and let me know how it goes. :)

@amcintyre99

I'm having an issue with bindSync not working. It seems to work a few times when I start my code but then it fails. The prepareSync call up front worked fine.

And I'm such a javascript noob I can't figure out how to return the failure. All I can get is true or false so far...

But it does work in that INSERTs are getting to the database.

Should I use bind instead of bindSync?

@amcintyre99

same thing with bind... it works a few times, then starts failing but I'm not sure why... once the bind starts failing, the execute doesn't do anything...

and by works, it is INSERTing records... so if I could just debug bind

@wankdanker
Collaborator

Two things: 1. The execute methods return result objects which should probably be closed before binding and executing again next time. 2. If you are using the asynchronous methods, make sure you do things within the proper callbacks.

Async example (untested) :

db.prepare(sql, function (err, stmt) {
    if (err) console.log(err.message);

    stmt.bind([42], function (err) {
        if (err) console.log(err.message);  

        stmt.execute(function (err, result) {
            if (err) console.log(err.message);

            result.closeSync(); 
        });
    });
});

or synchronously (not good if you are doing this in a web server because it blocks)

var stmt = db.prepareSync(sql);
stmt.bindSync([42]);
var result = stmt.executeSync();
result.closeSync();
//repeat bindSync, executeSync, closeSync
@wankdanker
Collaborator

Also, just to note, you can mix and match the usage of async or sync method calls. So you might prepareSync(), bindSync() but execute() so that the database io occurs asynchronously.

Example (untested):

var stmt = db.prepareSync(sql);
stmt.bindSync([42]);

stmt.execute(function (err, result) {
    result.closeSync();
});
@amcintyre99

Sorry, can't close the database connection each time. I'm executing millions of these inserts a day. I only open it once.

I must be misunderstanding something...

@amcintyre99

I'm leaving out that this code does a setTimeout to call itself again after 1 sec.

I do the open (and now prepare) up front just once then call the function. The db.query, that does INSERTs, (and now bind/execute) I put in the body of the function, then the setTimeout happens, then the function, etc. etc... this technique has been working great for months.

I passed the "stmt" var (from the prepare) on the function call right after the open and then in the setTimeout in the function so that it stays available.

Confused? I am...

@wankdanker
Collaborator
@amcintyre99

I have no "result" from the execute. It's an INSERT and I only return "err". I can add "result" during testing but I've learned to not use it for production with just pure INSERTs because it adds overhead since there's nothing to return.

Also, this particular code is node.js http client code long running, all day, once a second.

Other code I have that will really benefit from this "separating out the prepare" is some streaming code that receives millions of XML packets a day. And each packet runs a complex XML INSERT that could really stand to only be prepared once.

@amcintyre99

I added result to execute then result.closeSync in the execute cb.

Same thing. Runs at least once, then continues to look like its running (logging) but no INSERTs after the first set in the database.

@wankdanker
Collaborator
@wankdanker
Collaborator
@amcintyre99

Wish I could share the code but it's hitting a secure server with ssl/security keys imbedded... and they don't have a test endpoint/account...

In retesting now it's consistent that the first time it works (INSERTs done) but the 2nd thru n times the bind returns false for some reason. This is with both bindSync/execute and bind cb/execute.

Its probably me trying to pass "stmt" from the prepare each time in the recursive calls. Did I mention I'm a js noob?

Rough code is here and I'd appreciate any help:
https://gist.github.com/amcintyre99/5478705

@wankdanker
Collaborator
@wankdanker wankdanker referenced this issue from a commit in wankdanker/node-odbc
@wankdanker wankdanker Fix internal handling of canFreeHandle on ODBCResult::New()
ODBCResult instances which are created from an ODBCStatement instance
should not be allowed to destroy themselves (or free the hSTMT to be
more specific) because the ODBCStatement can still be used to do stuff
with that hSTMT. Only ODBCResult instances that are created from an
ODBCConnection instance are allowed to actually destroy the hSTMT.

Previously, we were not properly derefencing the canFreeHandle flag
passed to ODBCResult::New() so it was evaluating to true in every case.
This was causing the ODBCResult::CloseSync() method to always destroy
the handle and thus cause the behaviour described in #32.
0855800
@wankdanker
Collaborator

I made a test out of the code that you supplied. You can see it here: https://github.com/wankdanker/node-odbc/blob/master/test/test-prepareSync-multiple-execution.js

I believe I found out what the problem was and this test now passes. Reinstall with

npm install git://github.com/wankdanker/node-odbc.git#v0.5

and try again.

@amcintyre99

Hey great and thanks! I'll try it this evening as soon as I can...

One thing, your setTimeout is inside execute's cb... mine is right after the execute (not in the cb) so that the next http request can happen within a second and not wait on the database cb to complete. Any gotchas with that?

@wankdanker
Collaborator

Yeah, currently, there would be a gotcha. That is that the synchronous function calls are not queued, whereas the the asynchronous calls are.

If setTimeout is called outside of the cb and your insert query took 3 seconds (for whatever reason) to execute, that means that recursive would be called another 3 times during that duration. Each time it would call bindSync which would not queue up the bind values. So when the next queued execute() occurred, it would only actually insert the values from the last call to bindSync.

If you change your code to call bind asynchronously, then you should be good to go:

...
stmt.bind([vals], function (err) { 
    stmt.execute(function (err, result) {
        result.closeSync();
    });
});
...

Also, I have recently implemented executeNonQuery which you may want to try out so that you do not have to call result.closeSync().

In the example above it would look like:

...
stmt.bind([vals], function (err) { 
    stmt.executeNonQuery(function (err, numRowsAffected) {
        console.log(numRowsAffected);
    });
});
...
@amcintyre99

Still have same problem of everything works 1st time (INSERT is in db) but then on 2nd time thru n, both stmt.bind (and inside it's cb the stmt.execute) seem to fail but neither return anything in their "err" object. By fail I mean no INSERTs occurred.

@amcintyre99

On another box with node 10.5 on it the npm install string gets these msgs:

npm install git://github.com/wankdanker/node-odbc.git#v0.5

odbc@0.5.5 preinstall /home/andrew/dma/prd/node_modules/odbc
node-gyp configure build

make: Entering directory /home/andrew/dma/prd/node_modules/odbc/build'
CXX(target) Release/obj.target/odbc_bindings/src/odbc.o
../src/odbc.cpp: In static member function ‘static v8::Handle<v8::Value> ODBC::GetColumnValue(SQLHSTMT, Column, uint16_t*, int)’:
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_min’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_hour’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_mday’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_mon’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_year’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_wday’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_yday’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_isdst’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_gmtoff’ [-Wmissing-field-initializers]
../src/odbc.cpp:392:32: warning: missing initializer for member ‘tm::tm_zone’ [-Wmissing-field-initializers]
../src/odbc.cpp: In static member function ‘static v8::Local<v8::Array> ODBC::GetAllRecordsSync(HENV, HDBC, HSTMT, uint16_t*, int)’:
../src/odbc.cpp:794:1: warning: no return statement in function returning non-void [-Wreturn-type]
CXX(target) Release/obj.target/odbc_bindings/src/odbc_connection.o
../src/odbc_connection.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCConnection::CloseSync(const v8::Arguments&)’:
../src/odbc_connection.cpp:452:1: warning: no return statement in function returning non-void [-Wreturn-type]
../src/odbc_connection.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCConnection::Query(const v8::Arguments&)’:
../src/odbc_connection.cpp:1334:1: warning: ‘sql’ may be used uninitialized in this function [-Wuninitialized]
../src/odbc_connection.cpp:592:22: note: ‘sql’ was declared here
../src/odbc_connection.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCConnection::QuerySync(const v8::Arguments&)’:
../src/odbc_connection.cpp:1334:1: warning: ‘sql’ may be used uninitialized in this function [-Wuninitialized]
../src/odbc_connection.cpp:874:22: note: ‘sql’ was declared here
CXX(target) Release/obj.target/odbc_bindings/src/odbc_statement.o
CXX(target) Release/obj.target/odbc_bindings/src/odbc_result.o
../src/odbc_result.cpp: In static member function ‘static v8::Handle<v8::Value> ODBCResult::FetchSync(const v8::Arguments&)’:
../src/odbc_result.cpp:363:19: warning: unused variable ‘args’ [-Wunused-variable]
CXX(target) Release/obj.target/odbc_bindings/src/dynodbc.o
SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node
SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node: Finished
COPY Release/odbc_bindings.node
make: Leaving directory
/home/andrew/dma/prd/node_modules/odbc/build'
npm http GET https://registry.npmjs.org/bindings
npm http 304 https://registry.npmjs.org/bindings
odbc@0.5.5 node_modules/odbc
└── bindings@1.0.0

@wankdanker
Collaborator

The messages you get from npm install on your secondary box are compiler warnings for various things. I am not concerned with those at the moment and do not believe that they are affecting you.

I can see on your second box, that odbc@0.5.5 was definitely installed which includes the fix that I described earlier. Is that the version that is installed on the box where your testing failed?

You can look at the contents of package.json or run npm version from within the node-odbc install directory to determine the installed version.

If you have the sqlite3 libraries installed, you an run npm test and execute all of the tests. If test-prepareSync-multiple-execution succeeds then a difference between your test and mine is likely causing the problem.

@amcintyre99

Yes, it's failing on your latest version, that I just installed a few minutes ago.

If bind and/or execute are failing, why are there err objects empty?

On the bindSync version (that I won't use) I was getting an error message (with try/catch) but the e.message field was hex:
{ state: '\u001fur�',
error: '[node-odbc] Error in ODBCStatement::BindSync',
message: '�x9\b@�j\t\u0004' }

@wankdanker
Collaborator

Hmmm. Weird. That is a very good question. I experienced a similar error in the try/catch earlier, but fixed with 0855800 which should prevent destroying an hSTMT when it shouldn't be. That garbled message indicates to me that the hSTMT has already been destroy with SQLFreeHandle somehow.

If you enable debugging by modifying bindings.gyp and define 'DEBUG' under 'defines' like this:

...
 'defines' : [
    'DEBUG'
],
...

Then execute npm rebuild and try your tests again. If you gist the debug log, that might be helpful to know the sequence of events that is happening internally.

@amcintyre99

Ok, did that but dumb question... where did the log go?

@wankdanker
Collaborator

Oh, the log just dumps to stdout. :)

@amcintyre99

nope, no log - hmmmm

@amcintyre99

I wasn't doing the npm rebuild right..

https://gist.github.com/amcintyre99/5485701

@amcintyre99

The 1st execute inserted rows ok... the 2nd execute did not insert any rows... and no err object

@wankdanker
Collaborator

Thanks for that log. It is super helpful and I see the problem: https://gist.github.com/amcintyre99/5485701#file-gistfile1-txt-L36, canFreeHandle should be 0. And it's the way I'm passing values around. I should have a fix for this tomorrow if I don't get to it tonight.

@wankdanker
Collaborator

I just pushed ac63fb2, go ahead and try to install it with:

npm install git://github.com/wankdanker/node-odbc.git#v0.5

It is my best guess as to what was going wrong. If you have trouble again, please post the debug log again.

@amcintyre99

Yes!! u da man!!

Let me try it again on x64 db2 v10.

@amcintyre99

Looking good. No storage or task creep.

I am going to push this out for tomorrow and I'll let you know the amount of db2 cpu decrease. Given my volume, I'm expecting a little. :-)

@wankdanker
Collaborator

Awesome. I look forward to hearing how it goes. Keep an eye on memory usage too, if you would, just in case I have introduced any memory leaks.

Also, if you haven't tried the executeNonQuery() method as I mentioned in this comment above you might want to give it a shot to squeak out some extra performance.

Good luck! :)

@amcintyre99

I plan on using executeNonQuery and bypassing the driver manager thing. Need all I can get.

@amcintyre99

Dumb question on the placement of the bind.

Would it be possible for me to move the bind to right after the prepare and avoid the bind call every time? since the vars in the array are always the same names, which I assume means storage location... they just have different values each time...

http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0002218.html

@wankdanker
Collaborator

Unfortunately, the bind call is required right now in order to get the values passed from javascript-land to c++-land. This unfortunately also means that each element in the array is evaluated for its type and the parameters are bound again. We can probably come up with an optimized way to deal with this. You would have to keep a reference around to the same exact parameters array though. It would be something like this for example...

var stmt = db.prepareSync(sql);
var params = [];
stmt.bindSync(params);

doIt('hello', 'world');

function doIt(a, b) {
  params[0] = a;
  params[1] = b;

  stmt.rebind(params, function (err) {
    stmt.executeNonQuery(function (err, count) {
      doIt('something', 'else');
    });
  });
}

I don't know though. It might be a pain to implement. We either need to have a new method to tell the C++ side to re-read the values from the params array. Or read the params array on execute* each time. Not sure at the moment which would be better. Or maybe there is a chance that the internal pointers would all actually just work out. Hmm.... Let me test.

@amcintyre99

Started getting this:
16 { state: '22005',
error: '[node-odbc] Error in some module',
message: '[unixODBC][IBM][CLI Driver][DB2/LINUXX8664] SQL0420N Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018\n' }

I don't have any DECFLOAT data types... hmmmm....

@amcintyre99

Also started getting this (didn't get it last night testing):
/home/andrew/dma/prd/p1srun.js:2708: Uncaught TypeError: Cannot call method 'closeSync' of undefined

@amcintyre99

Had rolled in prepare/bind/execute changes on 2 nodes, both long running all day, called Q and S.

Q is working fine (the recursive code running every second) but only 23k runs per 6.5 hour day so not much volume.

The second process S is the streaming one with all the volume (3mill plus) and it's the one that started getting those errors.

I left your new odbc code in place for both. And am running with the new Q but backed off my S changes for now.

@amcintyre99

Re the SQL0420N msg.

This technote is close to explaining what's going on I think:
http://www-01.ibm.com/support/docview.wss?uid=swg21448700

From my code that works (and is working right now):
db.query(sqlc,[""+str+"",resp_ts.toString()],function(err) {

New version that failed: (but doesn't always fail - load related maybe?)
stmt.bind([""+str+"",resp_ts.toString()],function() {

@amcintyre99

Neither posted right. See this gist:
https://gist.github.com/amcintyre99/5489307

@wankdanker
Collaborator

Might those string values sometimes look like numbers? When we evaluate the values passed in the array, we use v8 to determine the type of data contained within the value. (see https://github.com/wankdanker/node-odbc/blob/v0.5/src/odbc.cpp#L563).

If by chance v8 thinks the string is actually a number we might create a number parameter for a string field. I'm not a v8 pro though, so I'm not exactly sure that is happening.

@amcintyre99

The first parm is always an xml string, well formed. The 2nd parm is always a number (bigint) converted to a string.

The db.query version is working right now. Same data as the stmt.bind version.

Will the bind return an err object yet? I can try it and see if it's getting an error prior to the execute.

@amcintyre99

More debug messages related to the SQL0420N.

https://gist.github.com/amcintyre99/5489891

@wankdanker
Collaborator

I don't see any reason why bind() would not return an error at this point. I can not create a case where it will error out yet though... If you have a test case for where it should cause an error, please provide.

Is there a reason that you convert the 2nd param (bigin) to a string? I do not use DB2 and know nothing of the function "DECFLOAT" and in what cases that it is invoked. :(

My best guess is that the second parameter in some cases is bound as a string and others as a number of some sort. If you enable the debugging, it will tell us the type that is being used during each bind() call. That might be helpful in figuring out if that is the case.

@amcintyre99

I've always bound the parms the same way. Been working for months with db.query.

Here is debug with just 1 node running. I stopped it right after the error.
https://gist.github.com/amcintyre99/5490123

@amcintyre99

This S code is a streaming app that's receiving millions of these XML packets using node http client:
response.on('data',function(data) {
db.query(...... (now replaced with bind/execute)
}

These can get queued up sometimes with hundreds of the db.query calls (doing INSERTs) per second. Any problem with hundreds of the bind/executes stacking up? all using the same "stmt" from the prepare?

@amcintyre99

Here's the debug of db.query version of S running just fine. Still your latest code.

https://gist.github.com/amcintyre99/5490249

@amcintyre99

Also, these tests during the day are on node 8.23 64bit.

@amcintyre99

I just pulled your latest 5.7 and now the new S is working fine.

@wankdanker
Collaborator

That's great, I suppose! Wish I knew which fix was the issue you were hitting.

@amcintyre99

It has to be one of the fixes you made this morning because until now I was testing with your version from last night.

@amcintyre99

I thought there was a storage leak but maybe not.

Normally with your odbc 4.4 the 25 streaming S nodes I run take right at 880m of VIRT each according to top. And they don't change all day. I log this daily is how I know.

Right now they lowest of the 25 is at 997m and the highest at 1581m of VIRT.

But it seems to be staying right at those numbers. Did something change from 4.4 that might be making these numbers larger?

The Q process (only 1 node on that 1 sec timer) stayed the same at about 965m of VIRT.

@wankdanker
Collaborator

A ton has change since the 0.4 series. There are now 4 separate classes which separate the odbc environment, connection, statements and results. One thing that will definitely increase memory usage are the result objects. Even after they are closed they may not be garbage collected by v8 for some time.

I noticed in one of your last logs that result objects were being created. You can avoid that by executing your inserts with stmt.executeNonQuery. That will only return the number of rows affected and not create a result object. That will probably save you memory.

@wankdanker
Collaborator

I have a couple comments about your results.

I would not necessarily expect the CPU usage of the database engine (db2sysc?) to change as a result of the changes to node-odbc. I would expect that it will still have the same workload in processing each insert. What I would expect is that the CPU usage of nodejs processes to decrease. Streamlining the process of bind/exec reduces the workload within the nodejs process.

I have absolutely no idea what is going on with your system and how you are running your tests, but I could imagine a situation where an optimized nodejs process could push more data to a database thus causing the database CPU to increase in the database engine while still using the same or less CPU usage within nodejs process. It might be better to get a comparison of throughput per CPU usage; like bytes inserted, inserts completed, etc. per total CPU time or per total wall clock time.

Also, in most of my cases, I never pay attention to VIRT. I only care about RES. Here are two passages from the top man page:

RES -- Resident Memory Size (KiB)
The non-swapped physical memory a task has used.

VIRT -- Virtual Memory Size (KiB)
The total amount of virtual memory used by the task. It
includes all code, data and shared libraries plus pages
that have been swapped out and pages that have been
mapped but not used.

So, the way I understand it, RES is the physical ram that is being take up by the process. Which still has increased in your tests but only by ~20M in the worst case.

Take this all with a grain of salt though. I have never used db2 and don't know what the best practices are for measuring its performance.

@amcintyre99

Avoiding 3 million prepares (aka sql compiles) should have reduced db2 cpu.

At least It always has when I've done something similar on other platforms.

@wankdanker
Collaborator

I definitely see your point. Let me think about this. I'll come up with a couple benchmarks to compare.

@wankdanker
Collaborator

I wrote some benchmarks and in the process realized some flaws in statement's bind() queuing. It's going to be best if you update to the most recent version of my v0.5 branch (v0.5.9) and modify your code to avoid the explicit bind() calls.

You can now pass the params directly to execute() or executeNonQuery().

...
stmt.execute([params], function (err, result) {
  result.closeSync();
});
...

Or

...
stmt.executeNonQuery([params], function (err, rowCount) {
  //whatever
});
...

All that aside. I saw significant increase in performance when prepareSync()ing one time and then binding/executing or passing params directly to execute.

$ node bench-prepare-not.js
100000 queries issued in 8.674 seconds, 11528/sec : Query

$ node bench-prepare-executeNonQuery.js
100000 queries issued in 3.383 seconds, 29559/sec : Prepare - ExecuteNonQuery

$ node bench-prepare-execute-closeSync.js
100000 queries issued in 3.523 seconds, 28384/sec : Prepare - Execute - CloseSync

I'm not sure what is going on in your situation. You may want to try running with valgrind and viewing the results with kcachegrind. That's what works for me. I was able to ensure that prepareSync was indeed called only one time.

Let me know how things go.

@amcintyre99

will update later today with results from live load

meanwhile, benchmarks from old server running db2 97 32bit - (10k instead of 100k)

$ node bench-prepare-not.js
10000 queries issued in 21.285 seconds, 469/sec : Query

$ node bench-prepare-executeNonQuery.js
{ state: '24000',
error: '[node-odbc] Error in some module',
message: '[unixODBC][IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000' }
Segmentation fault (core dumped)

$ node bench-prepare-execute-closeSync.js
10000 queries issued in 9.361 seconds, 1068/sec : Prepare - Execute - CloseSync

and if you would tell me the secret of putting inline code into this editor (like you are doing)...

@wankdanker
Collaborator

Ouch. I don't like the look of that Segfault! I just pushed out commit 7529f54 which may help with that error. If you want to update your code and try that benchmark again, give it a shot.

Regarding the code highlighting... In the upper right corner of the text editor, there is a link that says "Comments are parsed with GitHub Flavored Markdown". If you click on "GitHub Flavored Markdown" it will show you a cheat sheet of some of the formatting that you can do. The code stuff is in the bottom left of that cheat sheet. Basically three back-ticks (`) in a row and then the programming language. And then three more back-ticks to break out of it. For command line highlighting, I specify bash as the programming language. Hope that helps!

@amcintyre99

others still run fine but...
$ node bench-prepare-executeNonQuery.js
{ state: '24000',
error: '[node-odbc] Error in some module',
message: '[unixODBC][IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000' }
Segmentation fault (core dumped)

@amcintyre99

updated with latest daily results - unchanged
https://gist.github.com/amcintyre99/5498042

@amcintyre99

benchmarks from new server running db2 10 64bit - 100k iterations

and with your latest code installed with npm install git://github.com/wankdanker/node-odbc.git#v0.5

$ node bench-prepare-not.js
100000 queries issued in 38.876 seconds, 2572/sec : Query

$ node bench-prepare-executeNonQuery.js
{ state: '24000',
error: '[node-odbc] Error in some module',
message: '[unixODBC][IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000' }
Segmentation fault (core dumped)

$ node bench-prepare-execute-closeSync.js
100000 queries issued in 22.235 seconds, 4497/sec : Prepare - Execute - CloseSync

Your sqlite numbers are better than mine using db2. Hmmmm.....

My box is 8 core Xeon E5-2660 @ 2.2GHz. What is your chip and speed?

@wankdanker
Collaborator

Commit c982ceb in my v0.5 branch that I just pushed will fix your invalid cursor state. I was hitting it too, but only when using FreeTDS (against remote MSSQL).

Here's my cpuinfo:

 $ cat /proc/cpuinfo | grep "model name"
model name      : Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz
model name      : Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz
model name      : Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz
model name      : Intel(R) Core(TM)2 Quad CPU    Q6600  @ 2.40GHz

I am not totally sure about the internals of sqlite, but my guess is that the odbc calls bind right to the library calls. So, all the processing that is happening is within the same process and thus memory can be shared and there is no IO. It's not like there is a separate server that ODBC needs to talk to over TCP or pipe or something.

Here are my numbers against my non-local MSSQL Server:

$ node bench-prepare-not.js 
100000 queries issued in 95.572 seconds, 1046/sec : Query

$ node bench-prepare-executeNonQuery.js 
100000 queries issued in 117.266 seconds, 852/sec : Prepare - ExecuteNonQuery

$ node bench-prepare-execute-closeSync.js 
100000 queries issued in 133.676 seconds, 748/sec : Prepare - Execute - CloseSync

I find it totally weird that executing the benchmarks against non-local databases result in higher throughput when calling the normal query mechanism which does prepare, bind, execute, fetch, close.

@amcintyre99

"Deferred Prepare" may explain your non-local weird results: (does MSSQL have same feature?)
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.cli.doc/doc/c0007239.html
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0008789.html

Of course, my db is local on same box as node.

@amcintyre99

old box
$ node bench-prepare-executeNonQuery.js
10000 queries issued in 7.634 seconds, 1309/sec : Prepare - ExecuteNonQuery

new box
$ node bench-prepare-executeNonQuery.js
100000 queries issued in 20.185 seconds, 4954/sec : Prepare - ExecuteNonQuery

so ExecuteNonQuery is definitely the fastest. I'll switch to it asap.

@amcintyre99

just fyi, because you mentioned "talk to over TCP or pipe"...

with no db2cli.ini file so defaults to TCP
$ node bench-prepare-executeNonQuery.js
100000 queries issued in 29.763 seconds, 3359/sec : Prepare - ExecuteNonQuery

with db2cli.ini and Protocol = LOCAL so uses IPC
$ node bench-prepare-executeNonQuery.js
100000 queries issued in 20.172 seconds, 4957/sec : Prepare - ExecuteNonQuery

@wankdanker
Collaborator

Just for the record here are those benchmarks for local MySQL:

$ node bench-prepare-not.js
100000 queries issued in 10.528 seconds, 9498/sec : Query

$ node bench-prepare-executeNonQuery.js
100000 queries issued in 6.419 seconds, 15578/sec : Prepare - ExecuteNonQuery

$ node bench-prepare-execute-closeSync.js
100000 queries issued in 7.189 seconds, 13910/sec : Prepare - Execute - CloseSync
@amcintyre99

when I run this: (still with node 8.23 by the way)
$ node bench-prepare-executeNonQuery.js
100000 queries issued in 20.172 seconds, 4957/sec : Prepare - ExecuteNonQuery
my 8 core server only gets to about 5% cpu with always 0% wait (0.0%wa in top).

Is it possible your MySQL run is using more of your cpu somehow? what does yours max at?

Node version difference maybe? number of threads?

@wankdanker
Collaborator

It might be using more CPU; will have to check that out later. I'm on node v0.10.5 which uses a different thread pool mechanism than v0.8. Node v0.8 uses libeio and uses the default number of threads which is 4. Node v0.10 uses a custom thread pool built in libuv which supposedly dynamically allocates threads. I haven't looked at that code yet though, so not sure how it works.

All in all, threading in v0.10 should probably be better.

@amcintyre99

According to this thread I had with Ben, 8.x also used libuv:
https://groups.google.com/d/msg/nodejs/GvO8WD6GPnc/HjBVVtn3M_cJ

I was trying to implement your tip in your readme but never could figure it out.

@wankdanker
Collaborator

You are correct that v0.8 also uses libuv; it is the cross platform layer that node uses. However, the version of libuv used with v0.8 does not have it's own thread pool, it is farmed out to libeio.

Yeah, not sure how worthwhile that tip is, especially if you are only opening one connection per process. It really only makes a difference if one process opens multiple connections.

@amcintyre99

node 10.5 x64 db2 10.1 - your latest with no dynodbc

$ node bench-prepare-executeNonQuery.js
100000 queries issued in 19.872 seconds, 5032/sec : Prepare - ExecuteNonQuery

top still only showing 5% with no wait while running... not sure what that means...

@wankdanker
Collaborator

I think we got this working; at least the feature is implemented. If you have any further issues with it, let's open new issues for the specific problem.

Thanks again for your testing and benchmarking, @amcintyre99.

@wankdanker wankdanker closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.