Slow JDBC_SUB_SCAN when Dremio is on cluster

I had a dremio single node and a query on a mysql DB with lots of joins would usually take less than 5 seconds. Now with a multi-node architeture the same query takes more than 3 minutes with most of the time spent on JDBC_SUB_SCAN> “setup time”.

Does anybody know why?

582a8e96-916d-472a-b19c-95e1650a6fc6.zip (35,6,KB)

@matheusfba
Do you have the profile for the 5 secs run as well

@matheusfba

What happens if you run the same query from mysql shell from the Dremio executor?

Thanks
@balaji.ramaswamy

Sure.
8c9561ec-18cf-4fd2-a205-1b5cabc1d30d.zip (20,0,KB)

I’ve tested it and it took less than 5 seconds as well

Hi @matheusfba

I just looked at both the profiles, and though the query is same, but the query ran with two different configurations:

5 Secs:

{code}
[ {
“kind” : “STRING”,
“type” : “QUERY”,
“name” : “planner.query_results_store_path”,
“string_val” : “__jobResultsStore”
}, {
“kind” : “BOOLEAN”,
“type” : “QUERY”,
“name” : “planner.output_limit_enable”,
“bool_val” : true
}, {
“kind” : “STRING”,
“type” : “QUERY”,
“name” : “planner.store_query_results”,
“string_val” : “PATH_AND_ATTEMPT_ID”
}, {
“kind” : “LONG”,
“type” : “SYSTEM”,
“name” : “limits.single_field_size_bytes”,
“num_val” : 32000
}, {
“kind” : “BOOLEAN”,
“type” : “SYSTEM”,
“name” : “planner.enable_decimal_data_type”,
“bool_val” : true
}, {
“kind” : “BOOLEAN”,
“type” : “SYSTEM”,
“name” : “planner.verbose_profile”,
“bool_val” : true
} ]
{code}

3 Mins:
{code}
[ {
“kind” : “STRING”,
“type” : “QUERY”,
“name” : “planner.query_results_store_path”,
“string_val” : “__jobResultsStore”
}, {
“kind” : “BOOLEAN”,
“type” : “QUERY”,
“name” : “planner.output_limit_enable”,
“bool_val” : true
}, {
“kind” : “STRING”,
“type” : “QUERY”,
“name” : “planner.store_query_results”,
“string_val” : “PATH_AND_ATTEMPT_ID”
}, {
“kind” : “LONG”,
“type” : “SYSTEM”,
“name” : “exec.queue.large”,
“num_val” : 2
}, {
“kind” : “BOOLEAN”,
“type” : “SYSTEM”,
“name” : “exec.queue.memory.enable”,
“bool_val” : true
}, {
“kind” : “LONG”,
“type” : “SYSTEM”,
“name” : “exec.queue.small”,
“num_val” : 10
}, {
“kind” : “LONG”,
“type” : “SYSTEM”,
“name” : “exec.queue.threshold”,
“num_val” : 5000000
}, {
“kind” : “LONG”,
“type” : “SYSTEM”,
“name” : “limits.single_field_size_bytes”,
“num_val” : 6254008
}, {
“kind” : “BOOLEAN”,
“type” : “SYSTEM”,
“name” : “planner.verbose_profile”,
“bool_val” : true
}, {
“kind” : “LONG”,
“type” : “SYSTEM”,
“name” : “reflection.queue.small”,
“num_val” : 1
}, {
“kind” : “BOOLEAN”,
“type” : “SYSTEM”,
“name” : “store.hive.use_stats_in_metastore”,
“bool_val” : true
} ]
{code}

Have you done any changes after the 5 Sec.

@Venugopal_Menda

I made these changes to test, but before that the configurations were the same. I reverted the changes and ran the same query again. The result was the same (3 minutes).

Here is the profile after i reverted the changes.
fe3c0ae8-0257-4436-bb1f-14bbabe6634b.zip (35,6,KB)

@matheusfba

Sorry my bad ! we had to run the pushdown sql, can you please run the below sql from the Dremio coordinator and once from the executor via mysql shell ?

SELECT t11.id0 AS idLinhaContratacao, t11.competencia_id, t11.* AS custoTotalDaLinha, t11.*6 AS custoTotalDaLinhaSemHonorarios, t11.*7 AS custoTotalDaLinhaSemMatMed, t11.*8 AS custoTotalDaLinhaSemRecursosExternos, t11.*9 AS custoTotalDaLinhaSemDepreciacao, t11.*10 AS custoTotalDaLinhaSemHonorariosSemMatMed, t11.*11 AS custoTotalDaLinhaSemHonorariosSemRecursosExternos, t11.*12 AS custoTotalDaLinhaSemHonorariosSemDepreciacao, t11.*13 AS custoTotalDaLinhaSemMatMedSemRecursosExternos, t11.*14 AS custoTotalDaLinhaSemMatMedSemDepreciacao, t11.*15 AS custoTotalDaLinhaSemRecursosExternosSemDepreciacao, t11.*16 AS custoTotalDaLinhaSomenteHonorarios, t11.*17 AS custoTotalDaLinhaSomenteMatMed, t11.*18 AS custoTotalDaLinhaSomenteRecursosExternos, t11.*19 AS custoTotalDaLinhaSomenteDepreciacao, t11.*20 AS custoTotalDaLinhaSemDepreciacaoSemRecursosExternosSemMatMedSemHonorarios, t11.*21 AS valorSADTDaLinha, unidade.nome AS nome_unidade, unidade.tipo, unidade.estado, unidade.municipio, unidade.naturezaJuridica AS naturezajuridica, t11.ano, t11.mes
FROM (SELECT t10.id0, t10.competencia_id, competencia.ano, competencia.mes, competencia.unidade_id AS unidade_id3, t10.*, t10.*3 AS *6, t10.*4 AS *7, t10.*5 AS *8, t10.*6 AS *9, t10.*7 AS *10, t10.*8 AS *11, t10.*9 AS *12, t10.*10 AS *13, t10.*11 AS *14, t10.*12 AS *15, t10.*13 AS *16, t10.*14 AS *17, t10.*15 AS *18, t10.*16 AS *19, t10.*17 AS *20, t10.*18 AS *21
FROM (SELECT t9.id0, t9.competencia_id, t9.*, t9.*4 AS *3, t9.*5 AS *4, t9.*6 AS *5, t9.*7 AS *6, t9.*8 AS *7, t9.*9 AS *8, t9.*10 AS *9, t9.*11 AS *10, t9.*12 AS *11, t9.*13 AS *12, t9.*14 AS *13, t9.*15 AS *14, t9.*16 AS *15, t9.*17 AS *16, t9.*18 AS *17, t9.*19 AS *18
FROM (SELECT t8.id0, t8.competencia_id, t8.centroDeCusto_id0, t8.*, t8.*5 AS *4, t8.*6 AS *5, t8.*7 AS *6, t8.*8 AS *7, t8.*9 AS *8, t8.*10 AS *9, t8.*11 AS *10, t8.*12 AS *11, t8.*13 AS *12, t8.*14 AS *13, t8.*15 AS *14, t8.*16 AS *15, t8.*17 AS *16, t8.*18 AS *17, t8.*19 AS *18, t8.*20 AS *19
FROM (SELECT t7.id0, t7.competencia_id, t7.centroDeCusto_id0, t7.especialidade_id, (custodocentroprodutivo./ * t7.quantidade) AS *, (custodocentroprodutivo./3 * t7.quantidade) AS *5, (custodocentroprodutivo./4 * t7.quantidade) AS *6, (custodocentroprodutivo./5 * t7.quantidade) AS *7, (custodocentroprodutivo./6 * t7.quantidade) AS *8, (custodocentroprodutivo./7 * t7.quantidade) AS *9, (custodocentroprodutivo./8 * t7.quantidade) AS *10, (custodocentroprodutivo./9 * t7.quantidade) AS *11, (custodocentroprodutivo./10 * t7.quantidade) AS *12, (custodocentroprodutivo./11 * t7.quantidade) AS *13, (custodocentroprodutivo./12 * t7.quantidade) AS *14, (custodocentroprodutivo./13 * t7.quantidade) AS *15, (custodocentroprodutivo./14 * t7.quantidade) AS *16, (custodocentroprodutivo./15 * t7.quantidade) AS *17, (custodocentroprodutivo./16 * t7.quantidade) AS *18, (custodocentroprodutivo./17 * t7.quantidade) AS *19, (custodocentroprodutivo./18 * t7.quantidade) AS *20
FROM (SELECT t6.id0, t6.competencia_id, t6.centroDeCusto_id0, t6.competencia_id0, t6.quantidade, t6.especialidade_id
FROM (SELECT t5.id0, t5.competencia_id, t5.centroDeCusto_id0, t5.competencia_id0, t5.quantidade, t5.especialidade_id, centrodecusto_unidadedeproducao0.unidadeDeProducao_id AS unidadeDeProducao_id1
FROM (SELECT t4.id0, t4.competencia_id, t4.centroDeCusto_id0, t4.competencia_id0, producaodaespecialidade.quantidade, producaodaespecialidade.especialidade_id
FROM (SELECT t3.id0, t3.competencia_id, t3.id2, t3.centroDeCusto_id0, t3.competencia_id0
FROM (SELECT t2.id0, t2.competencia_id, producao.id AS id2, producao.centroDeCusto_id AS centroDeCusto_id0, producao.competencia_id AS competencia_id0, producao.produto_id
FROM (SELECT t1.id0, t1.competencia_id, centrodecusto.id AS id1, centrodecusto.unidade_id AS unidade_id1
FROM (SELECT t0.unidade_id, t0.id0, centrodecusto_unidadedeproducao.centroDeCusto_id, centrodecusto_unidadedeproducao.competencia_id
FROM (SELECT t.unidade_id, t.unidadeDeProducao_id, especialidade.id AS id0
FROM (SELECT abrangenciaunidadedeproducao.unidade_id, abrangenciaunidadedeproducao.unidadeDeProducao_id, abrangenciaunidadedeproducao_especialidade.especialidades_id
FROM planisa_stage.abrangenciaunidadedeproducao
INNER JOIN planisa_stage.abrangenciaunidadedeproducao_especialidade ON abrangenciaunidadedeproducao.id = abrangenciaunidadedeproducao_especialidade.AbrangenciaUnidadeDeProducao_id) AS t
INNER JOIN (SELECT id
FROM planisa_stage.especialidade) AS especialidade ON t.especialidades_id = especialidade.id) AS t0
INNER JOIN (SELECT unidade_id, centroDeCusto_id, competencia_id, unidadeDeProducao_id
FROM planisa_stage.centrodecusto_unidadedeproducao) AS centrodecusto_unidadedeproducao ON (t0.unidadeDeProducao_id = centrodecusto_unidadedeproducao.unidadeDeProducao_id AND t0.unidade_id = centrodecusto_unidadedeproducao.unidade_id)) AS t1
INNER JOIN (SELECT id, unidade_id
FROM planisa_stage.centrodecusto) AS centrodecusto ON (t1.centroDeCusto_id = centrodecusto.id AND t1.unidade_id = centrodecusto.unidade_id)) AS t2
INNER JOIN (SELECT id, unidade_id, centroDeCusto_id, competencia_id, produto_id
FROM planisa_stage.producao) AS producao ON ((t2.unidade_id1 = producao.unidade_id AND t2.competencia_id = producao.competencia_id) AND t2.id1 = producao.centroDeCusto_id)) AS t3
INNER JOIN (SELECT id
FROM planisa_stage.ponderacaoderateio) AS ponderacaoderateio ON t3.produto_id = ponderacaoderateio.id) AS t4
INNER JOIN (SELECT quantidade, especialidade_id, producao_id
FROM (SELECT quantidade, especialidade_id, producao_id
FROM planisa_stage.producaodaespecialidade) AS producaodaespecialidade
WHERE especialidade_id IS NOT NULL) AS producaodaespecialidade ON (t4.id2 = producaodaespecialidade.producao_id AND t4.id0 = producaodaespecialidade.especialidade_id)) AS t5
INNER JOIN (SELECT centroDeCusto_id, competencia_id, unidadeDeProducao_id
FROM planisa_stage.centrodecusto_unidadedeproducao) AS centrodecusto_unidadedeproducao0 ON (t5.centroDeCusto_id0 = centrodecusto_unidadedeproducao0.centroDeCusto_id AND t5.competencia_id0 = centrodecusto_unidadedeproducao0.competencia_id)) AS t6
INNER JOIN (SELECT id
FROM planisa_stage.criterioderateio) AS criterioderateio ON t6.unidadeDeProducao_id1 = criterioderateio.id) AS t7
INNER JOIN (SELECT competencia_id, centro_id, (((custoFixoTotal + custoVariavelTotal)) / quantidadePonderada) AS /, (((((custoFixoTotal + custoVariavelTotal) - custoFixoHonorarios) - custoVariavelHonorarios)) / quantidadePonderada) AS /3, (((((custoFixoTotal + custoVariavelTotal) - custoFixoMatMed) - custoVariavelMatMed)) / quantidadePonderada) AS /4, (((((custoFixoTotal + custoVariavelTotal) - custoFixoRecursosExternos) - custoVariavelRecursosExternos)) / quantidadePonderada) AS /5, (((((custoFixoTotal + custoVariavelTotal) - custoFixoDepreciacao) - custoVariavelDepreciacao)) / quantidadePonderada) AS /6, (((((((custoFixoTotal + custoVariavelTotal) - custoFixoHonorarios) - custoVariavelHonorarios) - custoFixoMatMed) - custoVariavelMatMed)) / quantidadePonderada) AS /7, (((((((custoFixoTotal + custoVariavelTotal) - custoFixoHonorarios) - custoVariavelHonorarios) - custoFixoRecursosExternos) - custoVariavelRecursosExternos)) / quantidadePonderada) AS /8, (((((((custoFixoTotal + custoVariavelTotal) - custoFixoHonorarios) - custoVariavelHonorarios) - custoFixoDepreciacao) - custoVariavelDepreciacao)) / quantidadePonderada) AS /9, (((((((custoFixoTotal + custoVariavelTotal) - custoFixoMatMed) - custoVariavelMatMed) - custoFixoRecursosExternos) - custoVariavelRecursosExternos)) / quantidadePonderada) AS /10, (((((((custoFixoTotal + custoVariavelTotal) - custoFixoMatMed) - custoVariavelMatMed) - custoFixoDepreciacao) - custoVariavelDepreciacao)) / quantidadePonderada) AS /11, (((((((custoFixoTotal + custoVariavelTotal) - custoFixoRecursosExternos) - custoVariavelRecursosExternos) - custoFixoDepreciacao) - custoVariavelDepreciacao)) / quantidadePonderada) AS /12, (((((((((custoFixoTotal + custoVariavelTotal) - custoFixoDepreciacao) - custoVariavelDepreciacao) - custoFixoRecursosExternos) - custoVariavelRecursosExternos) - custoFixoMatMed) - custoVariavelMatMed)) / quantidadePonderada) AS /13, (((((((((custoFixoTotal + custoVariavelTotal) - custoFixoHonorarios) - custoVariavelHonorarios) - custoFixoDepreciacao) - custoVariavelDepreciacao) - custoFixoRecursosExternos) - custoVariavelRecursosExternos)) / quantidadePonderada) AS /14, (((((((((custoFixoTotal + custoVariavelTotal) - custoFixoHonorarios) - custoVariavelHonorarios) - custoFixoDepreciacao) - custoVariavelDepreciacao) - custoFixoMatMed) - custoVariavelMatMed)) / quantidadePonderada) AS /15, (((((((((custoFixoTotal + custoVariavelTotal) - custoFixoHonorarios) - custoVariavelHonorarios) - custoFixoRecursosExternos) - custoVariavelRecursosExternos) - custoFixoMatMed) - custoVariavelMatMed)) / quantidadePonderada) AS /16, (((((((((((custoFixoTotal + custoVariavelTotal) - custoFixoHonorarios) - custoVariavelHonorarios) - custoFixoMatMed) - custoVariavelMatMed) - custoFixoRecursosExternos) - custoVariavelRecursosExternos) - custoFixoDepreciacao) - custoVariavelDepreciacao)) / quantidadePonderada) AS /17, (valorSADT / quantidadePonderada) AS /18
FROM planisa_stage.custodocentroprodutivo) AS custodocentroprodutivo ON (t7.centroDeCusto_id0 = custodocentroprodutivo.centro_id AND t7.competencia_id0 = custodocentroprodutivo.competencia_id)) AS t8
LEFT JOIN (SELECT id
FROM planisa_stage.especialidade) AS especialidade0 ON (t8.especialidade_id = especialidade0.id AND t8.id0 = especialidade0.id)) AS t9
LEFT JOIN (SELECT id
FROM planisa_stage.centrodecusto) AS centrodecusto0 ON t9.centroDeCusto_id0 = centrodecusto0.id) AS t10
INNER JOIN (SELECT id, ano, mes, unidade_id
FROM planisa_stage.competencia) AS competencia ON t10.competencia_id = competencia.id) AS t11
INNER JOIN (SELECT id, nome, tipo, estado, municipio, naturezaJuridica
FROM planisa_stage.unidade) AS unidade ON t11.unidade_id3 = unidade.id

@balaji.ramaswamy

I ran your sql in 3 different scenarios:

1: dremio coordinator with one executor
2: mysql shell on dremio executor
3: from my machine to database using datagrip

All of them took 3:30 min to run.

Here is the profile from the first scenario:
af2ecc14-b87e-406b-b538-4be0eddbb60c.zip (40,7,KB)

@matheusfba

Thanks a lot for running the tests. That confirms a known behavior with the product and we are working on a fix, no ETA now but will let you know when it is fixed

Thanks
@balaji.ramaswamy