mysql: Driver blocking node event loop

When using the driver to connect to mysql over our network, the node event loop blocks until all of the results are returned. This does not occur when using the instance of mysql running on my local machine or with the JDBC mysql driver over the network so it seems like there’s some interaction between the driver and our network.

I’ve posted the test code I’m using below, but am not really sure about the next steps to debug the problem so any suggestions would be appreciated.

mySql  = require('mysql')
conn  =  mySql.createConnection 
    host         : ''
    port         : '4930'
    user         : ''
    password     : ''
    database     : ''
    insecureAuth : true

conn.connect()
conn.query  "SELECT * from users", (err, rows) =>
    if err
        console.error err.message
    else
        delta = new Date() - last
        console.error('EVENT LOOP BLOCKED FOR', delta, "ms")
        console.log rows.length
    throw "done"
conn.end()

last = new Date()
setInterval( ->
    delta = new Date() - last
    if (delta > 2)
        console.error('EVENT LOOP BLOCKED FOR', delta, "ms")
    last = new Date()
, 1)

About this issue

Original URL
State: open
Created 12 years ago
Comments: 27 (6 by maintainers)

Most upvoted comments

lol

+13

hugorodrigues on Jul 24, 2012

I’m no longer involved with this project, but decided to take a quick look since it seems interesting.

As far as I can tell, my theory from 2012 is probably correct and there seems to be a scheduling issue in the node core, as demonstrated by the code here:

https://gist.github.com/felixge/d16ee6b128af7256862bf83fe2f34d8d#file-blocked-by-loop-js https://gist.github.com/felixge/d16ee6b128af7256862bf83fe2f34d8d#file-blocked-by-net-client-txt

Firing a 100 million loop iteration every 1s seems to block the event loop for ~60ms. That seems reasonable.

Firing the same loop in a busy socket’s 'data' callback however blocks the event loop for up to 1800ms. This seems unreasonable because the network callback gets scheduled 30x in a row while the timer has to wait.

So if your application requires fair scheduling between network and timer events, I’d suggest you raise an issue with the node.js core to get their feedback on this.

It might also be possible to implement some sort of cooperative scheduling in node-mysql by yielding back to the event loop after a certain amount of time. But if that’s what it takes to get reasonable scheduling in node, I’d suggest to ditch it for Go 🙈.

felixge on Feb 18, 2020

@dougwilson are you sure?

I just modified my example to use socket.pause() and setImmediate and was able to reduce the timer starvation from ~1800ms at a time to ~80ms:

https://gist.github.com/felixge/d16ee6b128af7256862bf83fe2f34d8d#file-blocked-by-net-js

https://gist.github.com/felixge/d16ee6b128af7256862bf83fe2f34d8d#file-blocked-by-net-improved-js

The flamegraph obviously shows why the callback is eating a lot of CPU time, but it doesn’t show scheduling issues. By using setImmediate we’re able to coerce the scheduler from it’s default behavior of executing queued I/O events up to the internal callback limit to instead schedule evenly between I/O and timer events.

See https://nodejs.org/en/docs/guides/event-loop-timers-and-nexttick/ (via brmichel)

felixge on Feb 18, 2020