[Question] Is parsing results from mysql blocking?

[sandinmyjoints]

When mysql returns results from a query, is parsing those results from mysql's wire protocol into a JS object blocking, or non-blocking?

[sidorares]

it's blocking for every incoming packet right now but I would not rely on this - maybe in the future we'll have worker thread parsing the binary data.

Note that typical response is composed of results from multiple packets, 1 per each field + 1 per row, there is idle time in between

Is there any particular problem that requires knowing this answer? Can you give more context?

[sandinmyjoints]

Thanks for the response! I'm investigating behavior of an application that sometimes returns large (by my standards anyway :) results set of ~20K rows of 20-30 columns each. Sometimes we see problems with this application, and one theory I had was that handling these queries is blocking the event loop for too long. But if node-mysql2 yields between packets and there's a packet per row and field, then that seems unlikely.

In general, are there any recommendations you'd have for handling results sets of that size?

[sandinmyjoints]

I used the blocked-at package and it identifies something in either sequelize or mysql2 as blocking in these cases: naugtur/blocked-at#5 (comment)

[sidorares]

would it be easy for you to switch to native promises instead of Bluebird to see any difference?

garbage collector can block execution even when everything else is async and yields offten

for your "~20K rows of 20-30 columns each" streaming might be helpful, in that case mysql2 does not concatenate every row into 20k length array and it's up to you how to manage every row

[sandinmyjoints]

Do you think switching to native promises would reduce blocking? Or just improve the stack trace? It's not something we can trivially do since we're on sequelize 5 and it uses bluebird, but 6 uses native promises so when we upgrade we'll be using them.

GC seems a strong candidate for these pauses. Does parsing the mysql response generate lots of intermediate objects -- could that be a reason GCs could run and take a while?

Streaming makes a lot of sense for handling this magnitude of results. We may go that route. Also possible we will adjust the product such that we work with smaller chunks of data at a time.

[sidorares]

GC seems a strong candidate for these pauses. Does parsing the mysql response generate lots of intermediate objects -- could that be a reason GCs could run and take a while?

yeah, could be the case. Can you run your with profiler enabled?

[sidorares]

Do you think switching to native promises would reduce blocking?

no, just trying to factor out what can contribute