MySQL datasource Net write timeout behaviour

1

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.

2

Hello Team,

Please update.

Thanks,
Ajay Babu Maguluri.

3

@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?

4

@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)

    Dremio_net_write_timeout_config
    Dremio_net_write_timeout_config840×575 62.1 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.

    Linx_Processlist
    Linx_Processlist864×111 46.5 KB
    MySQL_Processlist
    MySQL_Processlist967×52 24.1 KB

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

Thanks,
Ajay Babu Maguluri.

5

Hello Team,

Waiting for update.

Thanks,
Ajay Babu Maguluri.

6

@AjayBabuM

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

7

@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.

8

@AjayBabuM

How any records is the query returning?

9

@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.

10

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

11

@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.

12

@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