bad performance for hugh insert of escaped values

[ronny332]

Hello,

my normal usage of node-mysql (great lib, don't understand me wrong!) is to combine it with squel.js what works like a charm.

Today I had to migrate some old PHP code to node where a lot of sql inserts have to be done.

Every insert object contains 5 properties, at least I have to insert 50.000 to 100.000 rows at a time (of cause it is possible to split it to smaller pieces, but as it worked on the same way in PHP I'm searching for a reason for all of this).

As example I can provide some fake queries (created by squel and send to node-mysql):

var var inserts = [];
// up to 100.000 of this objects will be at the insert array
for (var i=0; i < 100000; ++i) {
  inserts.push({a:1, b:'abc', c:33, d:'78as', e:'foo'};
}
var sql = Squel.insert().into('tableName').setFieldsRows(inserts).toParam();

// squel.js creates a normal query like "INSERT INTO tableNAME (a,b,c,d,e) VALUES (?,?,?,?,?), (?,?,?,?,?), ... for sql.text.
// sql.values is an array with the insert objects [1,'abc',22,'78as','foo',..]
mysqlCon(sql.text, sql.values, function(err, result) {
  console.log('is not reached after minutes'):
});

From node-tick-processor I picked the following (reduced) output:

   ticks parent  name
   9062   58.4%  /opt/local/bin/node
   7322   80.8%    LazyCompile: *<anonymous> /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:70
   7322  100.0%      LazyCompile: *StringReplaceGlobalRegExpWithFunction native string.js:374
   7322  100.0%        LazyCompile: *replace native string.js:221
   7322  100.0%          LazyCompile: ~SqlString.format /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:67
   7322  100.0%            LazyCompile: ~Connection.format /xxx/src/node_modules/mysql/lib/Connection.js:243
    370    4.1%    LazyCompile: ~<anonymous> /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:70
    370  100.0%      LazyCompile: *StringReplaceGlobalRegExpWithFunction native string.js:374
    370  100.0%        LazyCompile: *replace native string.js:221
    370  100.0%          LazyCompile: ~SqlString.format /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:67
    370  100.0%            LazyCompile: ~Connection.format /xxx/src/node_modules/mysql/lib/Connection.js:243
    360    4.0%    LazyCompile: *StringReplaceGlobalRegExpWithFunction native string.js:374
    334   92.8%      LazyCompile: *<anonymous> /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:70
    334  100.0%        LazyCompile: *StringReplaceGlobalRegExpWithFunction native string.js:374
    334  100.0%          LazyCompile: *replace native string.js:221
    334  100.0%            LazyCompile: ~SqlString.format /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:67
     17    4.7%      LazyCompile: *replace native string.js:221
     15   88.2%        LazyCompile: ~SqlString.format /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:67
     15  100.0%          LazyCompile: ~Connection.format /xxx/src/node_modules/mysql/lib/Connection.js:243
     15  100.0%            LazyCompile: ~Connection.query /xxx/src/node_modules/mysql/lib/Connection.js:173
      2   11.8%        LazyCompile: *SqlString.escape /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:16
      2  100.0%          LazyCompile: *<anonymous> /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:70
      2  100.0%            LazyCompile: *StringReplaceGlobalRegExpWithFunction native string.js:374
      9    2.5%      LazyCompile: ~<anonymous> /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:70
      9  100.0%        LazyCompile: *StringReplaceGlobalRegExpWithFunction native string.js:374
      9  100.0%          LazyCompile: *replace native string.js:221
      9  100.0%            LazyCompile: ~SqlString.format /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:67

   5468   35.2%  /usr/lib/system/libsystem_kernel.dylib

    581    3.7%  /usr/lib/system/libsystem_platform.dylib
    495   85.2%    LazyCompile: *<anonymous> /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:70
    495  100.0%      LazyCompile: *StringReplaceGlobalRegExpWithFunction native string.js:374
    495  100.0%        LazyCompile: *replace native string.js:221
    495  100.0%          LazyCompile: ~SqlString.format /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:67
    495  100.0%            LazyCompile: ~Connection.format /xxx/src/node_modules/mysql/lib/Connection.js:243
     31    5.3%    LazyCompile: ~<anonymous> /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:70
     31  100.0%      LazyCompile: *StringReplaceGlobalRegExpWithFunction native string.js:374
     31  100.0%        LazyCompile: *replace native string.js:221
     31  100.0%          LazyCompile: ~SqlString.format /xxx/src/node_modules/mysql/lib/protocol/SqlString.js:67
     31  100.0%            LazyCompile: ~Connection.format /xxx/src/node_modules/mysql/lib/Connection.js:243

Strange for me to see is a performance loss at about 10.000 entries. Lower values are calculated instantly, higher values are resulting in 100% cpu for minutes (I haven't waited for the completion).

Squel itself has to function to render the query as string (.toString()). There is no performance problem with the high amount of objects.

My workstation is an apple iMac i7 (model late 2013), the real testing environment is a huge dual xeon server with debian 7.0 on it. CPU power should not be the real reason for all of this ;-).

Is there anything I have to have to change at my code or is it just a limitation of node-mysql?

Thanks for any help or hint you can provide!

[dougwilson]

The code is fine, but it may be a limitation of how fast JavaScript can do this type of operation on an enormous string and array. What version of Node.js are you using, btw?

[ronny332]

Hi dougwilson, thanks for your very fast reply ;-)!

my mac is running the latest release from macports (should be 0.10.33), the testing server has 0.10.32 running.

[ronny332]

After doing some tests it really looks like there is a big bottleneck with bigger value arrays. I've just looked shortly at the sqlString code of node-mysql but the overhead of all the regular expressions and string functions is a good reason for the slower speed compared to optimized functions which can only handle one thing. Why it is jumping from under a second (for arrays with a length smaller than 20.000) to a seemingly infinite loop is still a question to me.

As a short workaround a wrote a short helper function which bypasses this issue. Of cause it does not have the library quality like node-mysql and has some dependencies, but for me it is the solution for the topic of this ticket ;-).

  /**
   * helper function to bypass the slow performance of node-mysql's query parsing functionality for huge value arrays
   * @param squelData
   * @param callback
   * @returns {*}
   */
  var escapeSquelData = function (squelData, callback) {
    var defer = Q.defer(),
      promise = defer.promise;

    process.nextTick(function () {
      var _sql = [],
        _text,
        _values = [];

      if (!_.has(squelData, 'text') || _.isEmpty(squelData.text) || !_.has(squelData, 'values') || !_.isArray(squelData.values)) {
        defer.reject(new Error('invalid input'));
        return;
      }

      _.each(squelData.values, function (value) {
        if (typeof(value) !== 'number') {
          _values.push(Mysql.escape(value));
        }
        else {
          _values.push(value + '');
        }
      });

      _text = squelData.text.split('?');

      if (_text.length - 1 !== _values.length) {
        defer.reject(new Error('amount of values does not match query'));
        return;
      }

      // make the later loop possible (same length for _text and _values)
      _values.push('');

      process.nextTick(function () {
        _.each(_text, function (text, i) {
          _sql.push(text);
          _sql.push(_values[i]);
        });

        defer.resolve(_sql.join(''));
      });
    });

    return promise.nodeify(callback);
  };

[dougwilson]

How does node-mysql compare to just using toString() instead to toParam() from Squel.js?

var var inserts = [];
// up to 100.000 of this objects will be at the insert array
for (var i=0; i < 100000; ++i) {
  inserts.push({a:1, b:'abc', c:33, d:'78as', e:'foo'};
}
var sql = Squel.insert().into('tableName').setFieldsRows(inserts).toString();

mysqlCon(sql, function(err, result) {
  console.log('is not reached after minutes'):
});

I experienced a similar issue. It was related to the size of the values array and the fact that SqlString.format was using values.shift() to iterate the values.

#1019

[dougwilson]

This should be addresses now with version 2.6.0