pg performance v node-mysql-libmysqlsclient

[mgutz]

I benchmarked pg native vs node-mysql-libmysqlclient on my MBP with 16GB Ram, SSD. I created a simple test alternating between insert and select for 50,000 iterations. I'm using default postgresql and mysql install from Homebrew. The Mysql table is using InnoDB engine. pg is more than 3x slower. Results are in milliseconds

Am I doing something wrong?

ITERATIONS 50000
pg 17266
testLibMysql 5418

const ITERATIONS = 50000;

function testPg(cb) {
  var pg = require("pg").native;
  pg.connect(APP.config.postgresql.connectionString, function(err, client) {
    var start = new Date, elapsed;
    count = 0;
    async.whilst(
      function() { return count < ITERATIONS },

      function(cb) {
        count++;
        if (count % 2 === 1) {
          client.query("insert into users(user_name) values('test')", function(err, result) {
            if (count === 1) console.log(result);
            cb(err);
          });
        } else {
          client.query({text: "select * from users where id = $1", values: [1], name: 'foo'}, function(err, result) {
            if (count === 0) console.log(result);
            cb(err);
          });
        }
      },

      function(err) {
        elapsed = new Date - start;
        console.log("pg", elapsed);
        cb(err);
      }
    );
  });
}


function testLibMysql(cb) {
  var mysql = require('mysql-libmysqlclient');

  var client = mysql.createConnectionSync('localhost', 'userdev', 'dev', 'dbdev');
  var start = new Date, elapsed;

  count = 0;
  async.whilst(
    function() { return count < ITERATIONS },

    function(cb) {
      count++;
      if (count % 2 === 1) {
        client.query("insert into users(user_name) values('test')", function(err, result) {
          if (count === 1) console.log(result);
          cb(err);
        });
      } else {
        client.query("select * from users where id = 1;", function(err, result) {
          var r = result.fetchAllSync();
          if (count === 2) console.log(r);
          cb(err);
        });
      }
    },

    function(err) {
      elapsed = new Date - start;
      console.log("testLibMysql", elapsed);
      cb(err);
    }
  );
}

[mgutz]

My create table script had a typo and it was using the MyISAM storage engine. After fixing, there is about a 15% difference:

ITERATIONS 50000
pg 18236
testLibMysql 15409

[brianc]

yeah it's hard to tell with benchmarks where the problem is. It's exceptionally hard with native bindings because the boundary between what happens in C and what happens in JavaScript has a (semi)unpredictable impact on performance. Basically the act of turning a C string/whatever into a JavaScript object is a non trivial act and can influence the v8 runtime's performance. The trickiest part is tuning things for today's v8 implementation (which I cannot do since v8's internals are way over my head) could in fact turn out to be all for naught when they change the VM in the future.

tl;dr: premature optimization is the root of all evil. It's good to see the difference between pg & mysql is low though.