Partilhar via


Consultas de diagnóstico úteis no Azure Cosmos DB para PostgreSQL

APLICA-SE A: Azure Cosmos DB para PostgreSQL (alimentado pela extensão de banco de dados Citus para PostgreSQL)

Localizando qual nó contém dados para um locatário específico

No caso de uso multilocatário, podemos determinar qual nó de trabalho contém as linhas para um locatário específico. O Azure Cosmos DB para PostgreSQL agrupa as linhas de tabelas distribuídas em fragmentos e coloca cada fragmento em um nó de trabalho no cluster.

Suponha que os locatários do nosso aplicativo são lojas e queremos descobrir qual nó de trabalhador contém os dados para ID de loja = 4. Em outras palavras, queremos encontrar o posicionamento para o estilhaço contendo linhas cuja coluna de distribuição tem valor 4:

SELECT shardid, shardstate, shardlength, nodename, nodeport, placementid
  FROM pg_dist_placement AS placement,
       pg_dist_node AS node
 WHERE placement.groupid = node.groupid
   AND node.noderole = 'primary'
   AND shardid = (
     SELECT get_shard_id_for_distribution_column('stores', 4)
   );

A saída contém o host e a porta do banco de dados de trabalho.

┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename  │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│  102009 │          1 │           0 │ 10.0.0.16 │     5432 │           2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘

Localizando qual nó hospeda um esquema distribuído

Os esquemas distribuídos são automaticamente associados a grupos de colocation individuais, de modo que as tabelas criadas nesses esquemas são convertidas em tabelas distribuídas colocalizadas sem uma chave de estilhaço. Você pode encontrar onde reside um esquema distribuído juntando-se citus_shards com citus_schemas:

select schema_name, nodename, nodeport
  from citus_shards
  join citus_schemas cs
    on cs.colocation_id = citus_shards.colocation_id
 group by 1,2,3;
 schema_name | nodename  | nodeport
-------------+-----------+----------
 a           | localhost |     9701
 b           | localhost |     9702
 with_data   | localhost |     9702

Você também pode consultar citus_shards diretamente filtrando até o tipo de tabela de esquema para ter uma listagem detalhada para todas as tabelas.

select * from citus_shards where citus_table_type = 'schema';
   table_name   | shardid |      shard_name       | citus_table_type | colocation_id | nodename  | nodeport | shard_size | schema_name | colocation_id | schema_size | schema_owner
----------------+---------+-----------------------+------------------+---------------+-----------+----------+------------+-------------+---------------+-------------+--------------
 a.cities       |  102080 | a.cities_102080       | schema           |             4 | localhost |     9701 |       8192 | a           |             4 | 128 kB      | citus
 a.map_tags     |  102145 | a.map_tags_102145     | schema           |             4 | localhost |     9701 |      32768 | a           |             4 | 128 kB      | citus
 a.measurement  |  102047 | a.measurement_102047  | schema           |             4 | localhost |     9701 |          0 | a           |             4 | 128 kB      | citus
 a.my_table     |  102179 | a.my_table_102179     | schema           |             4 | localhost |     9701 |      16384 | a           |             4 | 128 kB      | citus
 a.people       |  102013 | a.people_102013       | schema           |             4 | localhost |     9701 |      32768 | a           |             4 | 128 kB      | citus
 a.test         |  102008 | a.test_102008         | schema           |             4 | localhost |     9701 |       8192 | a           |             4 | 128 kB      | citus
 a.widgets      |  102146 | a.widgets_102146      | schema           |             4 | localhost |     9701 |      32768 | a           |             4 | 128 kB      | citus
 b.test         |  102009 | b.test_102009         | schema           |             5 | localhost |     9702 |       8192 | b           |             5 | 32 kB       | citus
 b.test_col     |  102012 | b.test_col_102012     | schema           |             5 | localhost |     9702 |      24576 | b           |             5 | 32 kB       | citus
 with_data.test |  102180 | with_data.test_102180 | schema           |            11 | localhost |     9702 |     647168 | with_data   |            11 | 632 kB      | citus

Localizando a coluna de distribuição de uma tabela

Cada tabela distribuída tem uma "coluna de distribuição". (Para obter mais informações, consulte Modelagem de Dados Distribuídos.) Pode ser importante saber qual é a coluna. Por exemplo, ao ingressar ou filtrar tabelas, você pode ver mensagens de erro com dicas como "adicionar um filtro à coluna de distribuição".

As pg_dist_* tabelas no nó coordenador contêm diversos metadados sobre o banco de dados distribuído. Em particular, pg_dist_partition contém informações sobre a coluna de distribuição para cada tabela. Você pode usar uma função de utilitário conveniente para procurar o nome da coluna de distribuição a partir dos detalhes de baixo nível nos metadados. Aqui está um exemplo e sua saída:

-- create example table

CREATE TABLE products (
  store_id bigint,
  product_id bigint,
  name text,
  price money,

  CONSTRAINT products_pkey PRIMARY KEY (store_id, product_id)
);

-- pick store_id as distribution column

SELECT create_distributed_table('products', 'store_id');

-- get distribution column name for products table

SELECT column_to_column_name(logicalrelid, partkey) AS dist_col_name
  FROM pg_dist_partition
 WHERE logicalrelid='products'::regclass;

Saída de exemplo:

┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id      │
└───────────────┘

Deteção de fechaduras

Essa consulta será executada em todos os nós de trabalho e identificará bloqueios, há quanto tempo eles estão abertos e as consultas ofensivas:

SELECT run_command_on_workers($cmd$
  SELECT array_agg(
    blocked_statement || ' $ ' || cur_stmt_blocking_proc
    || ' $ ' || cnt::text || ' $ ' || age
  )
  FROM (
    SELECT blocked_activity.query    AS blocked_statement,
           blocking_activity.query   AS cur_stmt_blocking_proc,
           count(*)                  AS cnt,
           age(now(), min(blocked_activity.query_start)) AS "age"
    FROM pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity
      ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
      ON blocking_locks.locktype = blocked_locks.locktype
     AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
     AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
     AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
     AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
     AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
     AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
     AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
     AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
     AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
     AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED
     AND blocking_locks.GRANTED
    GROUP BY blocked_activity.query,
             blocking_activity.query
    ORDER BY 4
  ) a
$cmd$);

Saída de exemplo:

┌───────────────────────────────────────────────────────────────────────────────────┐
│                               run_command_on_workers                              │
├───────────────────────────────────────────────────────────────────────────────────┤
│ (10.0.0.16,5432,t,"")                                                             │
│ (10.0.0.20,5432,t,"{""update ads_102277 set name = 'new name' where id = 1; $ sel…│
│…ect * from ads_102277 where id = 1 for update; $ 1 $ 00:00:03.729519""}")         │
└───────────────────────────────────────────────────────────────────────────────────┘

Consultando o tamanho dos estilhaços

Esta consulta irá fornecer-lhe o tamanho de cada fragmento de uma determinada tabela distribuída, chamado my_distributed_table:

SELECT *
FROM run_command_on_shards('my_distributed_table', $cmd$
  SELECT json_build_object(
    'shard_name', '%1$s',
    'size',       pg_size_pretty(pg_table_size('%1$s'))
  );
$cmd$);

Saída de exemplo:

┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │                                result                                 │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│  102008 │ t       │ {"shard_name" : "my_distributed_table_102008", "size" : "2416 kB"}    │
│  102009 │ t       │ {"shard_name" : "my_distributed_table_102009", "size" : "3960 kB"}    │
│  102010 │ t       │ {"shard_name" : "my_distributed_table_102010", "size" : "1624 kB"}    │
│  102011 │ t       │ {"shard_name" : "my_distributed_table_102011", "size" : "4792 kB"}    │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘

Consultando o tamanho de todas as tabelas distribuídas

Esta consulta obtém uma lista dos tamanhos para cada tabela distribuída mais o tamanho de seus índices.

SELECT
  tablename,
  pg_size_pretty(
    citus_total_relation_size(tablename::text)
  ) AS total_size
FROM pg_tables pt
JOIN pg_dist_partition pp
  ON pt.tablename = pp.logicalrelid::text
WHERE schemaname = 'public';

Saída de exemplo:

┌───────────────┬────────────┐
│   tablename   │ total_size │
├───────────────┼────────────┤
│ github_users  │ 39 MB      │
│ github_events │ 98 MB      │
└───────────────┴────────────┘

Observe que há outras funções do Azure Cosmos DB para PostgreSQL para consultar o tamanho da tabela distribuída, consulte Determinando o tamanho da tabela.

Identificação de índices não utilizados

A consulta a seguir identificará índices não utilizados em nós de trabalho para uma determinada tabela distribuída (my_distributed_table)

SELECT *
FROM run_command_on_shards('my_distributed_table', $cmd$
  SELECT array_agg(a) as infos
  FROM (
    SELECT (
      schemaname || '.' || relname || '##' || indexrelname || '##'
                 || pg_size_pretty(pg_relation_size(i.indexrelid))::text
                 || '##' || idx_scan::text
    ) AS a
    FROM  pg_stat_user_indexes ui
    JOIN  pg_index i
    ON    ui.indexrelid = i.indexrelid
    WHERE NOT indisunique
    AND   idx_scan < 50
    AND   pg_relation_size(relid) > 5 * 8192
    AND   (schemaname || '.' || relname)::regclass = '%s'::regclass
    ORDER BY
      pg_relation_size(i.indexrelid) / NULLIF(idx_scan, 0) DESC nulls first,
      pg_relation_size(i.indexrelid) DESC
  ) sub
$cmd$);

Saída de exemplo:

┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │                            result                                     │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│  102008 │ t       │                                                                       │
│  102009 │ t       │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"}   │
│  102010 │ t       │                                                                       │
│  102011 │ t       │                                                                       │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘

Monitorando a contagem de conexões do cliente

A consulta a seguir conta as conexões abertas no coordenador e as agrupa por tipo.

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

Saída de exemplo:

┌────────┬───────┐
│ state  │ count │
├────────┼───────┤
│ active │     3 │
│ idle   │     3 │
│ ∅      │     6 │
└────────┴───────┘

Visualizando consultas do sistema

Consultas ativas

A pg_stat_activity exibição mostra quais consultas estão sendo executadas no momento. Você pode filtrar para encontrar os que estão sendo executados ativamente, juntamente com o ID do processo de seu back-end:

SELECT pid, query, state
  FROM pg_stat_activity
 WHERE state != 'idle';

Por que as consultas estão esperando

Também podemos consultar para ver os motivos mais comuns que consultas não ociosas que estão esperando. Para uma explicação dos motivos, consulte a documentação do PostgreSQL.

SELECT wait_event || ':' || wait_event_type AS type, count(*) AS number_of_occurences
  FROM pg_stat_activity
 WHERE state != 'idle'
GROUP BY wait_event, wait_event_type
ORDER BY number_of_occurences DESC;

Exemplo de saída ao executar pg_sleep em uma consulta separada simultaneamente:

┌─────────────────┬──────────────────────┐
│      type       │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅               │                    1 │
│ PgSleep:Timeout │                    1 │
└─────────────────┴──────────────────────┘

Taxa de acerto do índice

Esta consulta irá fornecer-lhe a sua taxa de acerto de índice em todos os nós. A taxa de acerto do índice é útil para determinar a frequência com que os índices são usados ao consultar. Um valor de 95% ou superior é o ideal.

-- on coordinator
SELECT 100 * (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS index_hit_rate
  FROM pg_statio_user_indexes;

-- on workers
SELECT nodename, result as index_hit_rate
FROM run_command_on_workers($cmd$
  SELECT 100 * (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS index_hit_rate
    FROM pg_statio_user_indexes;
$cmd$);

Saída de exemplo:

┌───────────┬────────────────┐
│ nodename  │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0           │
│ 10.0.0.20 │ 98.0           │
└───────────┴────────────────┘

Taxa de acerto do cache

A maioria dos aplicativos normalmente acessa uma pequena fração de seus dados totais de uma só vez. O PostgreSQL mantém os dados acessados com frequência na memória para evitar leituras lentas do disco. Você pode ver estatísticas sobre ele na visualização pg_statio_user_tables.

Uma medida importante é a porcentagem de dados que vem do cache de memória versus o disco em sua carga de trabalho:

-- on coordinator
SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit)  AS heap_hit,
  100 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_rate
FROM
  pg_statio_user_tables;

-- on workers
SELECT nodename, result as cache_hit_rate
FROM run_command_on_workers($cmd$
  SELECT
    100 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_rate
  FROM
    pg_statio_user_tables;
$cmd$);

Saída de exemplo:

┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │   cache_hit_rate    │
├───────────┼──────────┼─────────────────────┤
│         1 │      132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘

Se você se encontrar com uma proporção significativamente inferior a 99%, então você provavelmente deseja considerar aumentar o cache disponível para seu banco de dados.

Próximos passos

  • Saiba mais sobre outras tabelas do sistema que são úteis para diagnósticos