O PolarDB for MySQL coleta estatísticas de execução por operador para consultas processadas pelo mecanismo In-Memory Column Index (IMCI). Essas estatísticas — contagens reais de linhas, custos reais e tempo total por operador — são armazenadas junto com o plano EXPLAIN. Use esses dados para identificar quais operadores consomem mais tempo e mensurar o impacto das otimizações.
Pré-requisitos
Antes de começar, verifique se você tem:
Um cluster PolarDB for MySQL executando a versão 8.0.1.1.42 ou posterior. Para verificar sua versão, consulte Consultar uma versão do mecanismo.
Ativar profiling
Defina o parâmetro de sessão imci_analyze_query como ON para ativar o profiling. Após a execução de uma consulta, os dados de profiling correspondentes ficam disponíveis em information_schema.imci_sql_profiling.
|
Parâmetro |
Nível |
Padrão |
Descrição |
|
|
Sessão |
OFF |
Captura estatísticas de execução por operador para a consulta IMCI mais recente. Defina como ON para ativar. |
A tabela imci_sql_profiling armazena dados apenas da instrução SQL mais recente executada via IMCI. Consulte essa tabela imediatamente após a conclusão da consulta alvo.
Como funciona
Quando imci_analyze_query está definido como ON, o PolarDB registra as seguintes métricas para cada operador no plano de execução IMCI:
|
Coluna |
Descrição |
|
|
ID do operador, correspondente à saída do EXPLAIN |
|
|
Tipo de operador (por exemplo, Hash Join, Table Scan) |
|
|
Nome da tabela, quando aplicável |
|
|
Número real de linhas produzidas pelo operador |
|
|
Custo real |
|
|
Tempo total decorrido neste operador |
|
|
Condições de junção, chaves de agrupamento e uso de recursos (CPU/memória) |
Interpretar a saída de profiling
Use os padrões abaixo para diagnosticar problemas de desempenho:
|
Sinal |
Causa provável |
Ação |
|
O valor de |
Operador gargalo |
Concentre a otimização neste operador |
|
|
Erro na estimativa de contagem de linhas; pode indicar uma junção explosiva (linhas de saída excedem muito as de entrada) |
Revise as condições de junção; verifique se há filtros ou estatísticas ausentes |
|
A linha 1 ( |
Pressão de memória |
Reduza o paralelismo ou processe os dados em lotes menores |
Para ordenar os operadores por tempo de execução e localizar o gargalo diretamente:
/*ROUTE_TO_LAST_USED*/SELECT ID, Operator, Name, `A-Rows`, `Execution Time(s)`
FROM information_schema.imci_sql_profiling
ORDER BY `Execution Time(s)` DESC;Exemplos
Exemplo de consulta simples
Este exemplo aplica profiling em uma consulta de junção e agregação sobre um conjunto de dados TPC-H (Transaction Processing Performance Council Benchmark H).
SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;Etapa 1: Revise o plano de execução.
EXPLAIN SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;Saída de exemplo:
+----+------------------------+----------+--------+----------+---------------------------------------------------------------+
| ID | Operator | Name | E-Rows | E-Cost | Extra Info |
+----+------------------------+----------+--------+----------+---------------------------------------------------------------+
| 1 | Select Statement | | | | IMCI Execution Plan (max_dop = 1, max_query_mem = unlimited) |
| 2 | └─Hash Groupby | | 6 | 51218.50 | Group Key: lineitem.l_shipmode |
| 3 | └─Compute Scalar | | 1869 | 50191.00 | |
| 4 | └─Hash Join | | 1869 | 31000.00 | Join Cond: lineitem.l_orderkey = orders.o_orderkey |
| 5 | ├─Table Scan | lineitem | 2000 | 0.00 | |
| 6 | └─Table Scan | orders | 2000 | 0.00 | |
+----+------------------------+----------+--------+----------+---------------------------------------------------------------+Etapa 2: Ative o profiling e execute a consulta.
SET imci_analyze_query = ON;
SELECT l_shipmode, COUNT(*) FROM lineitem, orders WHERE l_orderkey = o_orderkey GROUP BY l_shipmode;Saída de exemplo:
+------------+----------+
| l_shipmode | COUNT(*) |
+------------+----------+
| REG AIR | 283 |
| SHIP | 269 |
| FOB | 299 |
| RAIL | 289 |
| TRUCK | 314 |
| MAIL | 274 |
| AIR | 272 |
+------------+----------+
7 rows in set (0.05 sec)Etapa 3: Recupere os dados de profiling.
Use a dica /*ROUTE_TO_LAST_USED*/ para direcionar esta consulta ao mesmo nó onde a consulta IMCI foi executada. Sem ela, o PolarProxy pode rotear a solicitação para um nó diferente e não retornar resultados.
/*ROUTE_TO_LAST_USED*/SELECT * FROM information_schema.imci_sql_profiling;Saída de exemplo:
+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+
| ID | Operator | Name | A-Rows | A-Cost | Execution Time(s) | Extra Info |
+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+
| 1 | Select Statement | | 0 | 52609.51 | 0 | IMCI Execution Plan (max_dop = 1, real_dop = 1, max_query_mem = unlimited, real_query_mem = unlimited) |
| 2 | └─Hash Groupby | | 7 | 52609.5 | 0.002 | Group Key: lineitem.l_shipmode |
| 3 | └─Compute Scalar | | 2000 | 51501 | 0 | |
| 4 | └─Hash Join | | 2000 | 31000 | 0.007 | Join Cond: lineitem.l_orderkey = orders.o_orderkey |
| 5 | ├─Table Scan | lineitem | 2000 | 0 | 0.001 | |
| 6 | └─Table Scan | orders | 2000 | 0 | 0 | |
+----+------------------------+----------+--------+----------+-------------------+--------------------------------------------------------------------------------------------------------+A coluna Extra Info exibe a condição de junção para o operador Hash Join e a chave de agrupamento para o operador Hash Groupby. Na primeira linha, a coluna Extra Info mostra o uso real de CPU e memória (real_dop, real_query_mem) ao lado dos valores estimados (max_dop, max_query_mem), permitindo comparar os dados para detectar pressão sobre os recursos.
Etapa 4: (Opcional) Agregar métricas de profiling.
Execute consultas de agregação diretamente em imci_sql_profiling. Por exemplo, para obter o tempo total de execução:
/*ROUTE_TO_LAST_USED*/SELECT SUM(`Execution Time(s)`) AS TOTAL_TIME FROM information_schema.imci_sql_profiling;Saída de exemplo:
+----------------------+
| TOTAL_TIME |
+----------------------+
| 0.010000000000000002 |
+----------------------+
1 row in set (0.00 sec)A duração total de execução da instrução SQL é de 10 ms.
Exemplo de consulta complexa
Este exemplo demonstra como usar dados de profiling para identificar um gargalo, aplicar uma otimização e verificar a melhoria. A consulta é executada em um conjunto de dados TPC-H SF100 (fator de escala 100).
SELECT
c_name,
sum(l_quantity)
FROM
customer,
orders,
lineitem
WHERE
o_orderkey IN (
SELECT
l_orderkey
FROM
lineitem
WHERE
l_partkey > 18000000
)
AND c_custkey = o_custkey
AND o_orderkey = l_orderkey
GROUP BY
c_name
ORDER BY
c_name
LIMIT 10;Diagnosticar o gargalo
Etapa 1: Revise o plano de execução estimado.
EXPLAIN SELECT
c_name,
sum(l_quantity)
FROM
customer,
orders,
lineitem
WHERE
o_orderkey IN (
SELECT
l_orderkey
FROM
lineitem
WHERE
l_partkey > 18000000
)
AND c_custkey = o_custkey
AND o_orderkey = l_orderkey
GROUP BY
c_name
ORDER BY
c_name
LIMIT 10;Saída de exemplo:
+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+
| ID | Operator | Name | E-Rows | E-Cost | Extra Info |
+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+
| 1 | Select Statement | | | | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
| 2 | └─Limit | | 10 | 7935739.96 | Offset=0 Limit=10 |
| 3 | └─Sort | | 10 | 7935739.96 | Sort Key: c_name ASC |
| 4 | └─Hash Groupby | | 1503700 | 7933273.99 | Group Key: customer.C_NAME |
| 5 | └─Hash Right Semi Join | | 54010545 | 7865930.26 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
| 6 | ├─Table Scan | lineitem | 59785766 | 24001.52 | Cond: (L_PARTKEY > 18000000) |
| 7 | └─Hash Join | | 538776190 | 5488090.33 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 8 | ├─Hash Join | | 181006430 | 668535.99 | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY |
| 9 | │ ├─Table Scan | customer | 15000000 | 600.00 | |
| 10 | │ └─Table Scan | orders | 150000000 | 6000.00 | |
| 11 | └─Table Scan | lineitem | 600037902 | 24001.52 | |
+----+----------------------------------+----------+-----------+------------+---------------------------------------------------------------+O Hash Join no ID 7 apresenta um custo estimado de 5.488.090 — aproximadamente 70% do custo total da consulta (7.935.739). Esse operador consome uma quantidade significativa de recursos de CPU, o que indica um grande conjunto de resultados intermediários. Ative o profiling para confirmar se este é realmente o gargalo.
Etapa 2: Ative o profiling e execute a consulta.
SET imci_analyze_query = ON;
SELECT
c_name,
sum(l_quantity)
FROM
customer,
orders,
lineitem
WHERE
o_orderkey IN (
SELECT
l_orderkey
FROM
lineitem
WHERE
l_partkey > 18000000
)
AND c_custkey = o_custkey
AND o_orderkey = l_orderkey
GROUP BY
c_name
ORDER BY
c_name
LIMIT 10;Saída de exemplo:
+--------------------+-----------------+
| c_name | sum(l_quantity) |
+--------------------+-----------------+
| Customer#000000001 | 172.00 |
| Customer#000000002 | 663.00 |
| Customer#000000004 | 174.00 |
| Customer#000000005 | 488.00 |
| Customer#000000007 | 1135.00 |
| Customer#000000008 | 440.00 |
| Customer#000000010 | 625.00 |
| Customer#000000011 | 143.00 |
| Customer#000000013 | 1032.00 |
| Customer#000000014 | 564.00 |
+--------------------+-----------------+
10 rows in set (21.37 sec)Etapa 3: Recupere os dados de profiling.
/*ROUTE_TO_LAST_USED*/SELECT * FROM information_schema.imci_sql_profiling;Saída de exemplo:
+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
| ID | Operator | Name | A-Rows | A-Cost | Execution Time(s) | Extra Info |
+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
| 1 | Select Statement | | 0 | 8336856.67 | 0 | |
| 2 | └─Limit | | 10 | 8336856.67 | 0.002 | Offset=0 Limit=10 |
| 3 | └─Sort | | 0 | 8336856.67 | 2.275 | Sort Key: c_name ASC |
| 4 | └─Hash Groupby | | 9813586 | 8320763.22 | 160.083 | Group Key: customer.C_NAME |
| 5 | └─Hash Right Semi Join | | 239598134 | 7994854.23 | 98.174 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
| 6 | ├─Table Scan | lineitem | 60013756 | 24001.52 | 3.28 | Cond: (L_PARTKEY > 18000000) |
| 7 | └─Hash Join | | 600037902 | 5156677.35 | 301.503 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 8 | ├─Hash Join | | 150000000 | 629777.96 | 97.201 | Join Cond: customer.C_CUSTKEY = orders.O_CUSTKEY |
| 9 | │ ├─Table Scan | customer | 15000000 | 600 | 3.321 | |
| 10 | │ └─Table Scan | orders | 150000000 | 6000 | 0.241 | |
| 11 | └─Table Scan | lineitem | 600037902 | 24001.52 | 0.661 | |
+----+----------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+O Hash Join no ID 7 levou 301,503 s — cerca de metade do tempo total da consulta. Suas entradas são a tabela lineitem (600 milhões de linhas, ID 11) e o resultado do Hash Join ID 8 (150 milhões de linhas). A junção de dois conjuntos grandes força o mecanismo a construir uma tabela hash extremamente extensa, o que domina o tempo de execução.
O plano de execução também revela que o filtro o_orderkey IN (...) é avaliado como um Hash Right Semi Join (ID 5) somente após a conclusão da junção pesada no ID 7. Se o otimizador converter essa subconsulta IN em uma semi-junção e a antecipar antes do ID 7, será possível filtrar as linhas de orders mais cedo e reduzir o conjunto de resultados intermediários alimentado no ID 7.
Aplicar a otimização
Etapa 4: Ative o pushdown de semi-junção baseado em custo.
SET imci_optimizer_switch = 'semijoin_pushdown=on';Etapa 5: Verifique o novo plano de execução.
EXPLAIN SELECT
c_name,
sum(l_quantity)
FROM
customer,
orders,
lineitem
WHERE
o_orderkey IN (
SELECT
l_orderkey
FROM
lineitem
WHERE
l_partkey > 18000000
)
AND c_custkey = o_custkey
AND o_orderkey = l_orderkey
GROUP BY
c_name
ORDER BY
c_name
LIMIT 10;Saída de exemplo:
+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+
| ID | Operator | Name | E-Rows | E-Cost | Extra Info |
+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+
| 1 | Select Statement | | | | IMCI Execution Plan (max_dop = 32, max_query_mem = unlimited) |
| 2 | └─Limit | | 10 | 2800433.74 | Offset=0 Limit=10 |
| 3 | └─Sort | | 10 | 2800433.74 | Sort Key: c_name ASC |
| 4 | └─Hash Groupby | | 14567321 | 2776544.58 | Group Key: customer.C_NAME |
| 5 | └─Hash Join | | 57918330 | 2631846.75 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 6 | ├─Hash Join | | 14567321 | 1014041.92 | Join Cond: orders.O_CUSTKEY = customer.C_CUSTKEY |
| 7 | │ ├─Hash Right Semi Join | | 12071937 | 906226.67 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
| 8 | │ │ ├─Table Scan | lineitem | 59785766 | 24001.52 | Cond: (L_PARTKEY > 18000000) |
| 9 | │ │ └─Table Scan | orders | 150000000 | 6000.00 | |
| 10 | │ └─Table Scan | customer | 15000000 | 600.00 | |
| 11 | └─Table Scan | lineitem | 600037902 | 24001.52 | |
+----+----------------------------------------+----------+-----------+------------+---------------------------------------------------------------+A semi-junção agora foi movida para o ID 7, filtrando as linhas de orders antes da junção custosa. O custo total estimado cai de 7.935.739 para 2.800.433.
Verificar a melhoria
Etapa 6: Execute novamente a consulta e colete o profiling.
SELECT
c_name,
sum(l_quantity)
FROM
customer,
orders,
lineitem
WHERE
o_orderkey IN (
SELECT
l_orderkey
FROM
lineitem
WHERE
l_partkey > 18000000
)
AND c_custkey = o_custkey
AND o_orderkey = l_orderkey
GROUP BY
c_name
ORDER BY
c_name
LIMIT 10;Saída de exemplo:
+--------------------+-----------------+
| c_name | sum(l_quantity) |
+--------------------+-----------------+
| Customer#000000001 | 172.00 |
| Customer#000000002 | 663.00 |
| Customer#000000004 | 174.00 |
| Customer#000000005 | 488.00 |
| Customer#000000007 | 1135.00 |
| Customer#000000008 | 440.00 |
| Customer#000000010 | 625.00 |
| Customer#000000011 | 143.00 |
| Customer#000000013 | 1032.00 |
| Customer#000000014 | 564.00 |
+--------------------+-----------------+
10 rows in set (13.74 sec)O tempo de execução caiu de 21,37 s para 13,74 s — uma redução de aproximadamente 40%.
Etapa 7: Confirme com os dados de profiling.
/*ROUTE_TO_LAST_USED*/SELECT * FROM information_schema.imci_sql_profiling;Saída de exemplo:
+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
| ID | Operator | Name | A-Rows | A-Cost | Execution Time(s) | Extra Info |
+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+
| 1 | Select Statement | | 0 | 4318488.35 | 0 | |
| 2 | └─Limit | | 10 | 4318488.35 | 0.002 | Offset=0 Limit=10 |
| 3 | └─Sort | | 0 | 4318488.34 | 3.076 | Sort Key: c_name ASC |
| 4 | └─Hash Groupby | | 9813586 | 4302394.89 | 163.149 | Group Key: customer.C_NAME |
| 5 | └─Hash Join | | 239598134 | 3976485.91 | 151.253 | Join Cond: orders.O_ORDERKEY = lineitem.L_ORDERKEY |
| 6 | ├─Hash Join | | 49393149 | 1321335.54 | 55.392 | Join Cond: orders.O_CUSTKEY = customer.C_CUSTKEY |
| 7 | │ ├─Hash Right Semi Join | | 49393149 | 954805.16 | 52.552 | Join Cond: lineitem.L_ORDERKEY = orders.O_ORDERKEY |
| 8 | │ │ ├─Table Scan | lineitem | 60013756 | 24001.52 | 2.791 | Cond: (L_PARTKEY > 18000000) |
| 9 | │ │ └─Table Scan | orders | 150000000 | 6000 | 0.152 | |
| 10 | │ └─Table Scan | customer | 15000000 | 600 | 0.028 | |
| 11 | └─Table Scan | lineitem | 600037902 | 24001.52 | 0.642 | |
+----+----------------------------------------+----------+-----------+------------+-------------------+----------------------------------------------------+O gargalo anterior (Hash Join ID 7, 301,503 s) foi eliminado. O pushdown da semi-junção no ID 7 agora filtra orders para aproximadamente 49 milhões de linhas antes da junção no ID 5, reduzindo o tamanho da entrada da junção e o tempo total de execução.