Ajuste de desempenho no Azure Cosmos DB for PostgreSQL

Artigo
04/11/2023

APLICA-SE A: Azure Cosmos DB for PostgreSQL (da plataforma da extensão de dados Citus para PostgreSQL)

A execução de um banco de dados distribuído em seu potencial completo oferece alto desempenho. No entanto, para alcançar esse desempenho é necessário fazer alguns ajustes no código do aplicativo e na modelagem de dados. Este artigo aborda algumas das técnicas mais comuns e eficazes para aprimorar o desempenho.

Pool de conexões do lado do cliente

Um pool de conexões contém conexões de banco de dados abertas para reutilização. Um aplicativo solicita uma conexão do pool quando necessário e o pool retorna uma que já está estabelecida, se possível, ou estabelece outra. Quando concluído, o aplicativo libera a conexão de volta para o pool em vez de fechá-la.

A adição de um pool de conexões do lado do cliente é uma forma fácil de aumentar o desempenho do aplicativo com alterações mínimas no código. Em nossas medidas, a execução de instruções de inserção de linha única é cerca de 24 vezes mais rápida em um cluster com o pooling habilitado.

Para obter exemplos específicos da linguagem da adição de pools no código do aplicativo, confira o guia de pilhas de aplicativos.

Observação

O Azure Cosmos DB for PostgreSQL também fornece o pooling de conexão do lado do servidor usando o pgbouncer, mas funciona principalmente para aumentar o limite de conexão do cliente. O desempenho de um aplicativo individual se beneficia mais do cliente, em vez do pool do lado do servidor. (Mas as duas formas de pool possam ser usadas de uma só vez sem danos).

Definir o escopo das consultas distribuídas

Atualizações

Ao atualizar uma tabela distribuída, tente filtrar as consultas na coluna de distribuição. Pelo menos quando faz sentido, quando os novos filtros não alteram o significado da consulta.

Em algumas cargas de trabalho, isso é fácil. As cargas de trabalho transacionais/operacionais, como aplicativos SaaS multilocatário ou a Internet das Coisas distribuem tabelas por locatário ou dispositivo. As consultas têm como escopo uma ID de locatário ou de dispositivo.

Por exemplo, no tutorial multilocatário, temos uma tabela ads distribuída por company_id. A maneira simples de atualizar um anúncio é destacá-lo desta forma:

-- slow

UPDATE ads
   SET impressions_count = impressions_count+1
 WHERE id = 42; -- missing filter on distribution column

Embora a consulta identifique exclusivamente uma linha e a atualize, o Azure Cosmos DB for PostgreSQL não sabe, no momento do planejamento, qual fragmento ela atualizará. A extensão Citus usa um ShareUpdateExclusiveLock em todos os fragmentos para garantir a segurança, o que bloqueia outras consultas que tentam atualizar a tabela.

Embora o id tenha sido suficiente para identificar uma linha, podemos incluir um filtro extra para acelerar a consulta:

-- fast

UPDATE ads
   SET impressions_count = impressions_count+1
 WHERE id = 42
   AND company_id = 1; -- the distribution column

O planejador de consultas do Azure Cosmos DB for PostgreSQL reconhece um filtro direto na coluna de distribuição e sabe exatamente qual fragmento específico bloquear. Em nossos testes, a adição de filtros na coluna de distribuição aumentou o desempenho da atualização paralela em 100x.

Junções e CTEs

Vimos como as instruções UPDATE devem ser definidas pela coluna de distribuição para evitar bloqueios de fragmentos desnecessários. Outras consultas também se beneficiam do escopo, geralmente para evitar a sobrecarga de rede de embaralhar dados desnecessariamente entre nós de trabalho.

-- logically correct, but slow

WITH single_ad AS (
  SELECT *
    FROM ads
   WHERE id=1
)
SELECT *
  FROM single_ad s
  JOIN campaigns c ON (s.campaign_id=c.id);

Podemos acelerar a consulta filtrando na coluna de distribuição, company_id, na instrução CTE e SELECT principal.

-- faster, joining on distribution column

WITH single_ad AS (
  SELECT *
    FROM ads
   WHERE id=1 and company_id=1
)
SELECT *
  FROM single_ad s
  JOIN campaigns c ON (s.campaign_id=c.id)
 WHERE s.company_id=1 AND c.company_id = 1;

Em geral, ao unir tabelas distribuídas, tente incluir a coluna de distribuição nas condições de junção. No entanto, ao unir uma tabela distribuída e uma de referência, isso não é necessário, pois o conteúdo da tabela de referência é replicado em todos os nós de trabalho.

Se parecer inconveniente adicionar os filtros extras a todas as consultas, saiba que há bibliotecas auxiliares para várias estruturas de aplicativos populares que facilitam isso. Estas são as instruções:

Registro em log de banco de dados eficiente

O registro em log de todas as instruções SQL o tempo todo adiciona sobrecarga. Em nossas medidas, o uso de mais um nível de log criterioso aprimorou as transações por segundo em 10x em comparação com o registro em log completo.

Para garantir a eficiência da operação diária, você pode desabilitar o registro em log, exceto para erros e consultas de execução prolongada anormais:

configuração	value	reason
log_statement_stats	OFF	Evitar sobrecarga de criação de perfil
log_duration	OFF	Não é necessário saber a duração das consultas normais
log_statement	Nenhuma	Não registrar consultas sem um motivo mais específico
log_min_duration_statement	Um valor maior do que o esperado para consultas normais	Mostra as consultas de execução prolongada anormais

Observação

As configurações relacionadas a log em nosso serviço gerenciado levam em conta as recomendações acima. Você pode deixá-los como estão. No entanto, já vimos clientes alterando as configurações para tornar o registro em log agressivo, o que causou problemas de desempenho.

Contenção de bloqueio

O banco de dados usa bloqueios para manter os dados consistentes em caso de acesso simultâneo. No entanto, alguns padrões de consulta exigem uma quantidade excessiva de bloqueio e existem alternativas mais rápidas.

Integridade e bloqueios do sistema

Antes de falar mais sobre as ineficiências comuns do bloqueio, veremos como exibir bloqueios e atividades em todo o cluster de banco de dados. A exibição citus_stat_activity fornece uma exibição detalhada.

A exibição mostra, entre outras coisas, como as consultas são bloqueadas por "eventos de espera", incluindo bloqueios. O agrupamento por wait_event_type ilustra a integridade do sistema:

-- general system health

SELECT wait_event_type, count(*)
  FROM citus_stat_activity
 WHERE state != 'idle'
 GROUP BY 1
 ORDER BY 2 DESC;

Um wait_event_type NULL significa que a consulta não está aguardando nada.

Se houver bloqueios na saída da atividade de estatística, veja as consultas bloqueadas específicas usando citus_lock_waits:

SELECT * FROM citus_lock_waits;

Por exemplo, se uma consulta for bloqueada em outra tentando atualizar a mesma linha, serão exibidas as instruções bloqueadas e as de bloqueio:

-[ RECORD 1 ]-------------------------+--------------------------------------
waiting_gpid                          | 10000011981
blocking_gpid                         | 10000011979
blocked_statement                     | UPDATE numbers SET j = 3 WHERE i = 1;
current_statement_in_blocking_process | UPDATE numbers SET j = 2 WHERE i = 1;
waiting_nodeid                        | 1
blocking_nodeid                       | 1

Para ver não apenas os bloqueios acontecendo no momento, mas também os padrões históricos, você pode capturar bloqueios nos logs do PostgreSQL. Para saber mais, confira a configuração do servidor log_lock_waits na documentação do PostgreSQL. Outro ótimo recurso são sete dicas para lidar com bloqueios no Blog de Dados do Citus.

Problemas comuns e soluções

Comandos de DDL

Os comandos de DDL, como truncate, drop e create index usam bloqueios de gravação e bloqueiam as gravações em toda a tabela. A minimização dessas operações reduz problemas de bloqueio.

Dicas:

Tente consolidar a DDL em janelas de manutenção ou usá-las com menos frequência.
O PostgreSQL dá suporte à criação de índices simultaneamente para evitar colocar um bloqueio de gravação na tabela.
Considere definir lock_timeout em uma sessão SQL antes de executar um comando DDL pesado. Com lock_timeout, o PostgreSQL anulará o comando de DDL se o comando aguardar muito tempo por um bloqueio de gravação. Um comando de DDL à espera de um bloqueio pode fazer com que as próximas consultas se enfileiram.

Transações ociosas em conexões de transação

As transações ociosas (não confirmadas) às vezes bloqueiam outras consultas desnecessariamente. Por exemplo:

BEGIN;

UPDATE ... ;

-- Suppose the client waits now and doesn't COMMIT right away.
--
-- Other queries that want to update the same rows will be blocked.

COMMIT; -- finally!

Para limpar manualmente as consultas de longa ociosidade no nó coordenador, você pode executar um comando como este:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'citus'
 AND pid <> pg_backend_pid()
 AND state in ('idle in transaction')
 AND state_change < current_timestamp - INTERVAL '15' MINUTE;

O PostgreSQL também oferece uma configuração de idle_in_transaction_session_timeout para automatizar o encerramento da sessão ociosa.

Deadlocks

O Azure Cosmos DB for PostgreSQL detecta deadlocks distribuídos e cancela as respectivas consultas, mas a situação tem um desempenho inferior em relação à tentativa de primeiro evitar os deadlocks. Uma fonte comum de deadlocks é a atualização do mesmo conjunto de linhas em uma ordem diferente em várias transações ao mesmo tempo.

Por exemplo, a execução destas transações em paralelo:

Sessão A:

BEGIN;
UPDATE ads SET updated_at = now() WHERE id = 1 AND company_id = 1;
UPDATE ads SET updated_at = now() WHERE id = 2 AND company_id = 1;

Sessão B:

BEGIN;
UPDATE ads SET updated_at = now() WHERE id = 2 AND company_id = 1;
UPDATE ads SET updated_at = now() WHERE id = 1 AND company_id = 1;

-- ERROR:  canceling the transaction since it was involved in a distributed deadlock

A Sessão A atualizou a ID 1 e depois a 2, enquanto a sessão B atualizou a 2 e depois a 1. Escreva o código SQL de transações com atenção para atualizar as linhas na mesma ordem. (A ordem de atualização às vezes é chamada de "hierarquia de bloqueio")

Em nossa medida, a atualização em massa de um conjunto de linhas com muitas transações foi 3x mais rápida ao evitar deadlock.

E/S durante a ingestão

Normalmente, o gargalo de E/S é um problema menor para o Azure Cosmos DB for PostgreSQL do que para o PostgreSQL de nó único devido à fragmentação. Os fragmentos são tabelas individualmente menores, com melhores taxas de ocorrência no índice e no cache, aprimorando desempenho.

No entanto, mesmo com o Azure Cosmos DB for PostgreSQL, à medida que tabelas e índices aumentam, a E/S de disco pode se tornar um problema para a ingestão de dados. É preciso estar atento ao número crescente de entradas de "E/S" wait_event_type que aparecem em citus_stat_activity:

SELECT wait_event_type, wait_event count(*)
  FROM citus_stat_activity
 WHERE state='active'
 GROUP BY 1,2;

Execute a consulta acima repetidamente para capturar informações relacionadas a eventos de espera. Observe como as contagens de diferentes tipos de evento de espera são alteradas.

Examine também as métricas no portal do Azure, especialmente a métrica IOPS que está sendo maximizada.