Node-mysql2 question about performance - process.nextTick()

FiveM Resource Development Discussion
, ,
1

Hello!

Somebody of you may know that current wrappers (ghmattimysql/fivem-mysql-async) uses node-mysql which has some issue with MySQL 8`s authentication as mentioned in this issue

Recommended node-mysql2 offers better performance and new features listed in readme at it´s repo. Therefore I attempt to make completely new middleware wrapper using it.

Source code can be found here.

After some testing [commit] we get more performance, about 3-times but strange bug came in. If the export was called after any Wait() or more generally inside coroutine then query/callback times was very very random (1ms, 15ms, 50ms, 100ms). Old wrappers could not reproduce this (always about 50ms). So i thought it was something with thread affinity.

After implementing setImmediate() inside callbacks [commit] performance gets even worse! (100+ ms).

So I was searching everywhere on discord, forum and found some discussion about implementing mongodb wrapper and about process.nextTick().

Without any hard core studying I removed setImmediate() and add process.nextTick() at the end of every export. [commit - read description]

Finally achieved some improvement.

afternexttick
Measured using os.clock() - node-mysql2

finaloxmysql
finaloxmysql704×157 5.15 KB

node-mysql2 first query/prepared query/after Wait vs ghmattimysql

ghmatti
Measured using os.clock() - ghmattimysql

So i would like to ask few questions:

  • Why process.nextTick() improved performance a lot? and how?

  • Is it good practice to invoke it at the end of export?

  • Why there is random query times if we invoke bare callback? Without setImmediate() or process.nextTick()

I hope you understand what I am talking about.

Thank you for your time spent reading this :smiling_face_with_three_hearts: and looking forward to your answers!

3 Likes
2

Instead of using this raw Node.js API to schedule stuff from a libuv tick (potentially risky), try just using ScheduleResourceTick - FiveM Natives @ Cfx.re Docs so that a normal game tick from timer.js (such as setImmediate) will run as soon as possible.

3 Likes
3

Never saw this native, thanks!

So I tried this inside export:

ScheduleResourceTick(GetCurrentResourceName());
setImmediate(async () => callback(await execute(query, parameters)));

Results were a bit unsteady: (every 2. - 4. execute) FIXED AT THE END
Sometimes it was:

[      script:oxmysql] SELECT * FROM items: 0 ms
[script:linden_invent] mysql2: 0.99999999997635ms

And sometimes

[      script:oxmysql] SELECT * FROM items: 1 ms
[script:linden_invent] mysql2: 34.999999999854ms

If i put Wait() before querying the peak maximize to about 64ms + query time from JS.

[      script:oxmysql] SELECT * FROM items: 21 ms
[script:linden_invent] mysql2: 84.00000000006ms

Using raw Node.js API

execute(query, parameters).then(callback);
process._tickCallback();

Results were always the same, no matter if it contains Wait():

[      script:oxmysql] SELECT * FROM items: 1 ms
[script:linden_invent] mysql2: 0.99999999997635ms

It is always approx. 0.9999...ms + query time from JS.

Yes it should be still faster and more reliable mysql2 middleware, but can that latency be somehow fixed when using ScheduleResourceTick() ?

UPDATE - FIXED ?
After removing setImmediate and just executing callback in promise callback:

ScheduleResourceTick(GetCurrentResourceName());
execute(query, parameters).then(result => callback(result));

Performance should be steady now, as same as using `process._tickCallback()