AnalyticDB for PostgreSQL provides a slow query diagnostics feature - AnalyticDB: Central de Documentação do Alibaba Cloud

Pré-requisitos

Antes de começar, verifique se:

Sua instância executa o AnalyticDB for PostgreSQL V7.0 no modo de armazenamento elástico, com versão secundária do mecanismo V7.0.5.0 ou posterior. Para verificar ou atualizar a versão secundária do mecanismo, consulte Visualizar a versão secundária do mecanismo e Atualizar a versão secundária do mecanismo.
O recurso de diagnóstico de consultas lentas está ativado. Consulte Ativar ou desativar o diagnóstico de consultas lentas.

Observações de uso

Os logs de consultas lentas retêm dados dos últimos 7 dias e não registram consultas com falha.
Instruções SQL com mais de 1.024 bytes são truncadas no log.
Por padrão, o sistema registra todas as instruções SQL que levam 1 segundo ou mais, além de todas as instruções DDL. O limiar é controlado pelo parâmetro Grand Unified Configuration (GUC) slow_query_min_duration. Para garantir a estabilidade do sistema, não modifique a configuração padrão desse parâmetro.

Ativar ou desativar o diagnóstico de consultas lentas

Conecte-se ao banco de dados postgres e execute os comandos a seguir.

Para verificar o status atual:

SHOW adbpg_feature_enable_query_monitor;

Para ativar o recurso em um banco de dados específico:

-- Replace <database_name> with the name of your business database.
ALTER DATABASE <database_name> SET adbpg_feature_enable_query_monitor TO ON;

Para desativar o recurso, defina o valor como OFF.

Consultar logs de consultas lentas

Todos os exemplos abaixo consultam qmonitor.instance_slow_queries (nível de instância) ou qmonitor.host_slow_queries (nível de nó). Conecte-se ao banco de dados postgres antes de executá-los.

Para uma descrição completa de todos os campos em ambas as visualizações, consulte Visualizar campos .

Por intervalo de tempo

Consulte todas as instruções lentas executadas nos últimos 30 minutos:

SELECT
    query_start       AS "Start time",
    query_end         AS "End time",
    query_duration_ms AS "Duration (ms)",
    query_id          AS "Query ID",
    query             AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min';

Consulte todas as instruções lentas executadas em uma data específica (por exemplo, 26 de fevereiro de 2024):

SELECT
    query_start       AS "Start time",
    query_end         AS "End time",
    query_duration_ms AS "Duration (ms)",
    query_id          AS "Query ID",
    query             AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
  AND query_end   <= '2024-02-27 00:00:00';

Por consumo de recursos (nível de instância)

query_duration_ms é a soma de quatro subcampos:

Subcampo	O que mede
`optimizer_duration_ms`	Tempo para gerar o plano de execução
`lock_wait_time_ms`	Tempo de espera por locks
`queue_wait_time_ms`	Tempo de espera por uma fila de recursos
`executor_duration_ms`	Tempo para executar a consulta no mecanismo de execução

Quando uma consulta estiver lenta, verifique qual subcampo predomina para identificar o gargalo. Por exemplo, um valor alto em queue_wait_time_ms indica contenção na fila de recursos, e não um problema de execução.

Top 20 por tempo de CPU nos últimos 30 minutos:

SELECT
    (cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                         AS "Start time",
    query_end                           AS "End time",
    query_duration_ms                   AS "Duration (ms)",
    query_id                            AS "Query ID",
    query                               AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY cpu_time_ms DESC
LIMIT 20;

Top 20 por uso de memória nos últimos 30 minutos:

SELECT
    pg_size_pretty(mem_bytes) AS "Memory consumed",
    query_start               AS "Start time",
    query_end                 AS "End time",
    query_duration_ms         AS "Duration (ms)",
    query_id                  AS "Query ID",
    query                     AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY mem_bytes DESC
LIMIT 20;

mem_bytes registra o pico cumulativo de uso de memória entre os nós. Esse valor aproxima o uso real de memória e não é exato, pois a memória flutua durante a execução da consulta.

Top 10 por tempo de CPU em uma janela de tempo específica (por exemplo, das 00:00 às 12:00 em 26 de fevereiro de 2024):

SELECT
    (cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                         AS "Start time",
    query_end                           AS "End time",
    query_duration_ms                   AS "Duration (ms)",
    query_id                            AS "Query ID",
    query                               AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
  AND query_end   <= '2024-02-26 12:00:00'
ORDER BY cpu_time_ms DESC
LIMIT 10;

Por consumo de recursos (nível de nó)

Use qmonitor.host_slow_queries para diagnosticar consultas lentas em um nó específico.

Top 20 por tempo de CPU em um nó nos últimos 30 minutos:

SELECT
    (host_cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                              AS "Start time",
    query_end                                AS "End time",
    query_duration_ms                        AS "Duration (ms)",
    query_id                                 AS "Query ID",
    query                                    AS "SQL statement"
FROM qmonitor.host_slow_queries
WHERE hostname    = '<node_hostname>'
  AND query_start >= now() - interval '30 min'
ORDER BY host_cpu_time_ms DESC
LIMIT 20;

Top 20 por uso de memória em um nó nos últimos 30 minutos:

SELECT
    pg_size_pretty(host_mem_bytes) AS "Memory consumed",
    query_start                    AS "Start time",
    query_end                      AS "End time",
    query_duration_ms              AS "Duration (ms)",
    query_id                       AS "Query ID",
    query                          AS "SQL statement"
FROM qmonitor.host_slow_queries
WHERE hostname    = '<node_hostname>'
  AND query_start >= now() - interval '30 min'
ORDER BY host_mem_bytes DESC
LIMIT 20;

Substitua <node_hostname> pelo nome de host real do nó a ser diagnosticado.

Por usuário

Conte as consultas lentas por usuário nos últimos 10 minutos para identificar quais contas geram mais consultas lentas:

SELECT
    user_name AS "User",
    COUNT(1)  AS "Slow query count"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '10 min'
GROUP BY user_name
ORDER BY COUNT(1) DESC;

Por ID da consulta

Recupere os detalhes completos de uma consulta lenta específica pelo seu ID:

SELECT * FROM qmonitor.instance_slow_queries
WHERE query_id = '<query_id>';

Exportar logs de consultas lentas

Use instruções SELECT para exportar dados de qmonitor.instance_slow_queries ou qmonitor.host_slow_queries para uma tabela interna, um bucket do Object Storage Service (OSS) ou uma tabela do MaxCompute. Para métodos de exportação, consulte Data Lake Analysis.

No AnalyticDB for PostgreSQL, índices são criados para logs de consultas lentas e esses logs são particionados em query_start. Para melhor desempenho, sempre inclua query_start na cláusula WHERE para evitar varreduras completas na tabela.

Por exemplo, para exportar logs das 13:00 às 16:00 em 26 de fevereiro de 2024:

-- Add this condition to your SELECT statement.
WHERE query_start >= '2024-02-26 13:00:00'
  AND query_start <= '2024-02-26 16:00:00'

Configurar limiares

Apenas superusuários podem modificar estes itens de configuração.

`slow_query_min_duration`

Controla o tempo mínimo de execução para que uma consulta seja registrada como lenta. O padrão é 1s. Você também pode modificar este item de configuração para coletar consultas lentas que consomem menos de 1 segundo.

Se o tempo de execução for maior ou igual a este valor, a instrução SQL, o tempo de execução e as métricas relacionadas serão registrados.
Defina como -1 para desativar completamente a coleta de consultas lentas.

Para alterar o limiar de um banco de dados específico (requer superusuário):

-- Collect slow queries that take 5 seconds or longer.
ALTER DATABASE '<database_name>' SET slow_query_min_duration = '5s';

Para alterar o limiar apenas da sessão atual (qualquer usuário pode executar):

SET slow_query_min_duration = '5s';

`slow_query_plan_min_duration`

Controla o tempo mínimo de execução para que o plano de execução de uma consulta lenta seja coletado. O padrão é 10s.

Se o tempo de execução for maior ou igual a este valor, o plano de execução será coletado junto com as métricas da consulta.
Para inspeção ad-hoc de planos, use EXPLAIN; não é necessário coletar planos automaticamente.
Defina como -1 para desativar completamente a coleta de planos de execução.

Para alterar o limiar de um banco de dados específico (requer superusuário):

ALTER DATABASE '<database_name>' SET slow_query_plan_min_duration = '10s';

Para alterar o limiar apenas da sessão atual:

SET slow_query_plan_min_duration = '10s';

Visualizar campos

`qmonitor.instance_slow_queries` (nível de instância)

Campo	Tipo	Descrição
`query_id`	text	ID exclusivo da consulta.
`session_id`	integer	ID da sessão da consulta.
`db_name`	character varying(128)	Nome do banco de dados consultado.
`user_name`	character varying(128)	Nome do usuário que iniciou a consulta.
`application_name`	character varying(128)	Aplicação que iniciou a consulta.
`client_hostname`	character varying(128)	Nome de host do cliente.
`client_addr`	character varying(128)	Endereço IP do cliente.
`client_port`	character varying(32)	Porta do cliente.
`rsg_name`	character varying(128)	Grupo de recursos associado à tabela acessada pela consulta.
`query_start`	timestamptz	Hora de início da consulta.
`query_end`	timestamptz	Hora de término da consulta.
`query_duration_ms`	bigint	Tempo total da consulta em milissegundos. Igual a `optimizer_duration_ms` + `lock_wait_time_ms` + `queue_wait_time_ms` + `executor_duration_ms`.
`optimizer_duration_ms`	bigint	Tempo para gerar o plano de execução, em milissegundos. Instruções SQL complexas levam mais tempo.
`lock_wait_time_ms`	bigint	Tempo de espera por lock, em milissegundos.
`queue_wait_time_ms`	bigint	Tempo de espera por uma fila de recursos, em milissegundos.
`executor_duration_ms`	bigint	Tempo para executar a consulta no mecanismo de execução, em milissegundos.
`query`	text	Texto da instrução SQL.
`is_plpgsql`	boolean	Indica se a consulta é um procedimento armazenado PL/pgSQL.
`query_optimizer`	character varying(16)	Otimizador utilizado. Valores válidos: `ORCA`, `Planner`.
`access_tables`	text	Nome da tabela acessada pela consulta.
`result_rows`	bigint	Número de linhas retornadas. Para instruções `INSERT`, representa o número de linhas inseridas.
`num_segments`	integer	Número de nós de computação onde a consulta foi executada.
`num_slices`	integer	Número de slices no plano de execução da consulta.
`cpu_time_ms`	numeric	Tempo total de CPU em milissegundos, incluindo o nó coordenador e todos os nós de computação.
`mem_bytes`	numeric	Pico cumulativo de uso de memória por nó. Aproxima o uso real de memória; não é um valor exato.
`spill_bytes`	numeric	Valor cumulativo do número máximo de arquivos salvos nos discos dos nós de computação onde a consulta é executada. Este valor reflete aproximadamente o espaço em disco consumido pela consulta, mas não é exato.

`qmonitor.host_slow_queries` (nível de nó)

Campo	Tipo	Descrição
`hostname`	character varying(128)	Nome de host do nó.
`hostrole`	text	Função do nó. Valores válidos: `master` (nó coordenador), `segment` (nó de computação).
`query_id`	text	ID exclusivo da consulta.
`db_name`	character varying(128)	Nome do banco de dados consultado.
`user_name`	character varying(128)	Nome do usuário que iniciou a consulta.
`query_start`	timestamptz	Hora de início da consulta.
`query_end`	timestamptz	Hora de término da consulta.
`query`	text	Texto da instrução SQL.
`query_duration_ms`	bigint	Tempo de execução da consulta, em milissegundos.
`optimizer_duration_ms`	bigint	Tempo para gerar o plano de execução, em milissegundos.
`host_cpu_time_ms`	numeric	Tempo de CPU da consulta neste nó, em milissegundos.
`host_mem_bytes`	numeric	Pico de uso de memória da consulta neste nó.
`host_spill_bytes`	numeric	Máximo cumulativo de dados descarregados no disco neste nó.