[ANN] mysql2, node-mysql compatible mysql driver

[Andrey]

Hi all,

https://npmjs.org/package/mysql2 - fast, node-mysql compatible mysql library.

Protocol implementation is written from scratch, some functionality reused from node-mysql (pool, constants)

Tested with node 0.6+, implemented in JavaScript, does not require mysql runtime.

Additional features:

  - SSL and compression support,

  - prepared statements

  - server-side protocol. Example of mysql->mysql proxy - https://gist.github.com/sidorares/5609893

  - speed. On my box I get 2M rows per second compared to 400k rows/sec with node-mysql and 800k rows/sec with mariasql drivers.

https://github.com/sidorares/node-mysql2

It's still work in progress, feel free to report bugs/benchmarks/node-mysql incompatible behaviour.

Regards,

Andrey

[alessioalex]

Congrats, that sounds awesome!

[Ben Noordhuis]

On Mon, May 20, 2013 at 3:50 AM, Andrey <andrey....@gmail.com> wrote:
> - speed. On my box I get 2M rows per second compared to 400k rows/sec with
> node-mysql and 800k rows/sec with mariasql drivers.

Andrey, how come it's that much faster?

Are you sure that you are benchmarking the same thing? Are settings
like connection pooling, auto-commit mode, connection character set,
etc. all identical across benchmarks?

[Andrey]

Ben,

2M result I mentioned is probably not very accurate (but real) - it's 500k rows query with single numerical field, and parseInt(buffer.toString()) seems to be much slower than manual int parsing ( https://github.com/sidorares/node-mysql2/blob/master/lib/packets/packet.js#L183 ) Here is simple benchmark manual vs builtin parseInt from buffer: https://gist.github.com/sidorares/5612620 - there is 10x times difference.

Inner loop row parser is compiled dynamically based on field types - https://github.com/sidorares/node-mysql2/blob/master/lib/compile_text_parser.js

Removing Function.bind() also helped to save 10-20% couple of times. De-framer stream to packets does not buffer incoming data and in most cases executes parser on incoming chunk without copying.

Results on 2 DigitalOcean small servers (512M Ubuntu 12.10 server 32bit, 1 CPU) using https://github.com/mscdex/node-mysql-benchmarks

150k insert+select

Results (init time in seconds, other values in ops/s):

module,init,escapes,inserts,selects

===================================

C,1.009,6790961,1728,299671

PHP,0.014,1501147,1632,209946

mysql,0.022,674157,1369,89074

mysql2,0.02,691882,1467,340909

mysql-libmysqlclient,0.017,1570681,1721,142315

mysql-native,0.02,0,1503,199734

mariasql,0.013,1662971,1594,282486

500k inserts + select

Results (init time in seconds, other values in ops/s):

module,init,escapes,inserts,selects

===================================

C,0.021,6809424,1696,340689

PHP,0.021,1499440,1655,206863

mysql,0.035,705799,0,0

mysql2,0.024,690131,1512,384911

mysql-libmysqlclient,0.031,1860465,1569,161290

mysql-native,0.028,0,1445,187829

mariasql,0.029,1392434,1594,257467

I definitely interested to see more real life benchmarks. 

[Ben Noordhuis]

Thanks, that makes sense. So basically node-mysql is leaving some
obvious optimizations on the table?

By the way, I noticed that you're using vm.runInNewContext(). Is
there a reason for that? You could probably reduce memory usage a
little by running the generated code in the same context.

[Andrey]

Thanks, that makes sense.  So basically node-mysql is leaving some
obvious optimizations on the table?

yes

 

 

By the way, I noticed that you're using vm.runInNewContext().  Is
there a reason for that?  You could probably reduce memory usage a
little by running the generated code in the same context.

Thanks, Ben, I replaced it with vm.runInThisContext(). Basically I want to generate function as soon as I have type data. `new Function(str)` was slower than vm.runInNewContext(str) for some reason (need to double check that).