MySQL datasource Net write timeout behaviour

Hello Dremio Team,

I configured MySQL 8.x as external datasource in Dremio 13.x community version and i need to know the fallowing cases,

  1. As i observed when reflection created over datasource table dremio not using Net write timeout(net_write_timeout) from advanced options insted of it’s using net_write_timeout from global varibles of MySQL server and impact allways happens when change the net_write_timeout in server side not in dremio client side.

  2. Dremio will maintain any connection pool for datasource because MySQL server showing around 4 connections(sleep + query status) with Dremio server in processlist. and what was interval for ideal connection timeout.

  3. Can we check the session varibles of datasource in Dremio.

Thanks,
Ajay Babu Maguluri.

Hello Team,

Please update.

Thanks,
Ajay Babu Maguluri.

@AjayBabuM

Currently Dremio does not have an idle connection timeout. net_write_timeout and net_read_timeout are needed only if your reflection refresh fails with a time out. Is that what is happening? Do you have a job profile you can attach?

@balaji.ramaswamy

Thanks for reply.

  1. Please fine the job profile attached, yes this issue was facing when reflection refresh happens, when increase the net_write_timeout in mysql global varible at server side this issue was not facing and impact allways happens when change the net_write_timeout in server side not in dremio client side.35a82fc4-1d42-497a-afa8-1660e34544a9.zip (33.7 KB)

    MySQL_net_write_timeout_config

  2. Dremio will maintain any connection pool for datasource because MySQL server showing around 3 connections(sleep + query status) with Dremio server in processlist.

  3. Can we check the session varibles of datasource in Dremio.

Thanks,
Ajay Babu Maguluri.

Hello Team,

Waiting for update.

Thanks,
Ajay Babu Maguluri.

@AjayBabuM

Can we try to decrease “Record fetch size” to 50 and try?

@balaji.ramaswamy

  1. Tried this not working. I saw same query in community "Server has closed the connection" while creating reflection for PostgreSQL table any update for @jalandhar request?

  2. Issue still facing when change the net_write_timeout at dremio advanced options at data source level but not facing when change the net_write_timeout global varible at MySQL server side.

Thanks,
Ajay Babu Maguluri.

@AjayBabuM

How any records is the query returning?

@balaji.ramaswamy

Query returns 15 Million records and size was
139 MB in dremio footprint and raw size as in file was 387 MB of data.

Thanks,
Ajay Babu Maguluri.

@AjayBabuM Can you test by writing an aggregation query so you group the results and return less rows, does it still happen?

@balaji.ramaswamy

It’s requirement to make reflection with raw data.So we need raw data in reflection that will be exposed to client.This issue can simulate on your test bench.

@AjayBabuM

I agree you need raw reflection, I am just narrowing down the issue. You can also restrict the VDS to only return 1 Million rows, then try raw reflection on it, if that succeeds then try 5 million then 10 million, we will know where we are breaking and then find the limit