node-mysql2 is running slower than node-mysql, config issue?

[kevinder]

Hi, I wanted to try mysql2 as a replacement for mysql for performance reasons. When I tried it in a production environment, I am actually seeing that mysql2 is slower, so I wonder if it's a config issue on my end. Are there any settings I should try changing?

Using the profiler, I didn't see anything out of the ordinary. The vast majority of time is spent in handlePacket -> TextRow -> wrap. The only other difference I notice between the two libraries is that mysql2 seems to invoke Readable.push in a very regular frequency time-wise, whereas mysql does it in an irregular pattern. Thank you!

[sidorares]

Interesting. Can you post simple example so I can profile locally? What node and mysql server versions are you using?

[testn]

I found a couple issues with it. It seems to be much slower due to

1. "typeCast" code handling where it has a compatibility layer that adds a lot of overhead due to the request to just-in-time parsing of column definition's properties (see typeCast is up to 10x slower #889)
2. the state management + some code generation seems to make the code optimization not working as expected

For the first one, I can fix it by caching column definition attribute.

[sidorares]

feel free to share to any profiling/benchmarking setup you have @testn , would love to try that myself

[testn]

I tried it using sequelize which uses typeCast quite a lot

const { Sequelize, DataTypes } = require('sequelize');
const { PerformanceObserver, performance } = require('perf_hooks');

const sequelize = new Sequelize('test', 'root', '', {
    host: 'localhost',
    dialect: 'mysql',
    query : {
        raw: true
    }
  });

const MockData = sequelize.define('MockData', 
{
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true
      },
      first_name: {
        type: DataTypes.INTEGER
      },    last_name: {
        type: DataTypes.INTEGER
      },    email: {
        type: DataTypes.INTEGER
      },    gender: {
        type: DataTypes.INTEGER
      },    ip_address: {
        type: DataTypes.INTEGER
      },
}, 
{
    tableName: "MOCK_DATA",
    timestamps: false,
});

(async function(){
  var startTime = performance.now()

    
    for (var i = 0; i<100; i++) {
        const mock = await sequelize.query("SELECT CAST(`id` AS DECIMAL(10,2)) `test`, CAST('2020-01-01 10:10:10' AS DATETIME) `birth`, `first_name`, `last_name`, `email`, `gender`, `ip_address` FROM `MOCK_DATA` AS `MockData`");
        console.assert(mock != null);
    }
    var endTime = performance.now()

    console.log(`Call to doSomething took ${endTime - startTime} milliseconds`)
    
})();

[sidorares]

thanks @testn , will post my numbers for this script. Also interesting to compare version that selects ~100 rows from a table in one query

[testn]

my test does it on 100 queries each one return 1,000 rows

[sidorares]

@testn can you share the code that populates 1000 rows?

[testn]

I generated it using https://www.mockaroo.com/

[testn]

Another finding is that buffer.slice(start,end).toString(encoding) is quite slower than buffer.toString(encoding,start,end) by about 10-15%.

[sidorares]

Another finding is that buffer.slice(start,end).toString(encoding) is quite slower than buffer.toString(encoding,start,end) by about 10-15%.

nice, that should be a very straightforward change. If you have time happy to see PR with this ( if not I'll do it )

[testn]

ok. I need to change a few things in string.js to make decode/encode accept start/end. Sounds ok to you?

[testn]

@sidorares Please take a look at #1401

[testn]

@sidorares I found another issue with the code generation. Instead of creating the parser every time, we can reuse parser object and keep generating the new object every time instead.

Doing so, potentially, can help sequelize to avoid copying the object again in raw query case and can return the result from mysql2 directly.

[sidorares]

can you elaborate? I thought it is reused for the same set of type variations

[testn]

@sidorares let me share the pull request. The performance improvement is <1% but it avoids leaking TextRow/BinaryRow object outside where it can create a memory issue