Partilhar via

Práticas recomendadas para carregar dados em massa no Banco de Dados do Azure para PostgreSQL - Servidor Flexível

  • Artigo

APLICA-SE A: Banco de Dados do Azure para PostgreSQL - Servidor Flexível

Este artigo discute vários métodos para carregar dados em massa no Banco de Dados do Azure para servidor flexível PostgreSQL, juntamente com as práticas recomendadas para cargas de dados iniciais em bancos de dados vazios e cargas de dados incrementais.

Métodos de carregamento

Os seguintes métodos de carregamento de dados são organizados em ordem do mais demorado para o menos demorado:

  • Execute um comando de registro INSERT único.
  • Lote em 100 a 1000 linhas por confirmação. Você pode usar um bloco de transação para encapsular vários registros por confirmação.
  • Executar INSERT com vários valores de linha.
  • Execute o comando COPY.

O método preferido para carregar dados em um banco de dados é usar o COPY comando. Se o COPY comando não for possível, usar batch INSERT é o melhor método. Multi-threading com um COPY comando é o método ideal para carregar dados em massa.

Práticas recomendadas para cargas iniciais de dados

Índices de queda

Antes de fazer um carregamento inicial de dados, recomendamos que você solte todos os índices nas tabelas. É sempre mais eficiente criar os índices depois que os dados são carregados.

Restrições de queda

As principais restrições de queda são descritas aqui:

  • Principais restrições exclusivas

    Para obter um desempenho forte, recomendamos que você elimine restrições de chave exclusivas antes de uma carga de dados inicial e as recrie-as depois que a carga de dados for concluída. No entanto, eliminar restrições de chave exclusivas cancela as proteções contra dados duplicados.

  • Restrições de chave estrangeira

    Recomendamos que você elimine as restrições de chave estrangeira antes da carga inicial de dados e as recrie-as depois que a carga de dados for concluída.

    Alterar o session_replication_role parâmetro para replica também desativa todas as verificações de chave estrangeira. No entanto, esteja ciente de que fazer a alteração pode deixar os dados em um estado inconsistente se eles não forem usados corretamente.

Tabelas não registadas

Considere os prós e contras de usar tabelas não registradas antes de usá-las em carregamentos de dados iniciais.

O uso de tabelas não registradas torna o carregamento de dados mais rápido. Os dados gravados em tabelas não registradas não são gravados no log write-ahead.

As desvantagens de usar tabelas não registradas são:

  • Eles não são à prova de colisão. Uma tabela não registrada é automaticamente truncada após uma falha ou desligamento não limpo.
  • Os dados de tabelas não registradas não podem ser replicados para servidores em espera.

Para criar uma tabela não registrada ou alterar uma tabela existente para uma tabela não registrada, use as seguintes opções:

  • Crie uma nova tabela não registrada usando a seguinte sintaxe:

    CREATE UNLOGGED TABLE <tablename>;

  • Converter uma tabela registrada existente em uma tabela não registrada usando a seguinte sintaxe:

    ALTER TABLE <tablename> SET UNLOGGED;

Ajuste de parâmetros do servidor

  • autovacuum: Durante o carregamento inicial de dados, é melhor desligar autovacuumo . Após a conclusão do carregamento inicial, recomendamos que você execute um manual VACUUM ANALYZE em todas as tabelas do banco de dados e ative autovacuumo .

Nota

Siga as recomendações aqui apenas se houver memória e espaço em disco suficientes.

  • maintenance_work_mem: Pode ser definido como um máximo de 2 gigabytes (GB) em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL. maintenance_work_mem ajuda a acelerar o autovácuo, o índice e a criação de chaves estrangeiras.

  • checkpoint_timeout: Em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL, o checkpoint_timeout valor pode ser aumentado para um máximo de 24 horas a partir da configuração padrão de 5 minutos. Recomendamos que você aumente o valor para 1 hora antes de carregar dados inicialmente na instância flexível do servidor do Banco de Dados do Azure para PostgreSQL.

  • checkpoint_completion_target: Recomendamos um valor de 0,9.

  • max_wal_size: Pode ser definido como o valor máximo permitido em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL, que é de 64 GB enquanto você está fazendo o carregamento inicial de dados.

  • wal_compression: Pode ser ligado. A ativação desse parâmetro pode incorrer em algum custo adicional da CPU gasto na compactação durante o log de gravação antecipada (WAL) e na descompactação durante a reprodução da WAL.

Recomendações de servidor flexível do Banco de Dados do Azure para PostgreSQL

Antes de iniciar uma carga inicial de dados na instância flexível do servidor do Banco de Dados do Azure para PostgreSQL, recomendamos que:

  • Desative a alta disponibilidade no servidor. Você pode habilitá-lo depois que a carga inicial for concluída no primário.
  • Crie réplicas de leitura após a conclusão do carregamento inicial de dados.
  • Torne o registro mínimo ou desative-o completamente durante as cargas iniciais de dados (por exemplo: desativar pgaudit, pg_stat_statements, armazenamento de consultas).

Recriar índices e adicionar restrições

Supondo que você tenha descartado os índices e restrições antes da carga inicial, recomendamos que você use valores altos em maintenance_work_mem (como mencionado anteriormente) para criar índices e adicionar restrições. Além disso, a partir do PostgreSQL versão 11, os seguintes parâmetros podem ser modificados para uma criação de índice paralelo mais rápida após a carga inicial de dados:

  • max_parallel_workers: Define o número máximo de trabalhadores que o sistema pode suportar para consultas paralelas.

  • max_parallel_maintenance_workers: Controla o número máximo de processos de trabalho, que podem ser usados no CREATE INDEX.

Você também pode criar os índices fazendo as configurações recomendadas no nível da sessão. Aqui está um exemplo de como fazê-lo:

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

Práticas recomendadas para cargas de dados incrementais

Tabelas de partição

Recomendamos sempre que particione tabelas grandes. Algumas vantagens do particionamento, especialmente durante cargas incrementais, incluem:

  • A criação de novas partições com base em novos deltas torna eficiente adicionar novos dados à tabela.
  • A manutenção de mesas torna-se mais fácil. Você pode soltar uma partição durante uma carga de dados incremental para evitar exclusões demoradas em tabelas grandes.
  • O Autovacuum seria acionado apenas em partições que foram alteradas ou adicionadas durante cargas incrementais, o que facilita a manutenção de estatísticas na tabela.

Manter as estatísticas da tabela atualizadas

O monitoramento e a manutenção de estatísticas de tabela são importantes para o desempenho da consulta no banco de dados. Isso também inclui cenários em que você tem cargas incrementais. O PostgreSQL usa o processo de daemon de vácuo automático para limpar tuplas mortas e analisar as tabelas para manter as estatísticas atualizadas. Para obter mais informações, consulte Monitoramento e ajuste de vácuo automático.

Criar índices sobre restrições de chave estrangeira

A criação de índices em chaves estrangeiras nas tabelas filho pode ser benéfica nos seguintes cenários:

  • Atualizações ou exclusões de dados na tabela pai. Quando os dados são atualizados ou excluídos na tabela pai, as pesquisas são realizadas na tabela filho. Para tornar as pesquisas mais rápidas, você pode indexar chaves estrangeiras na tabela filho.
  • Consultas, onde você pode ver a junção de tabelas pai e filho em colunas principais.

Identificar índices não utilizados

Identifique os índices não utilizados no banco de dados e solte-os. Os índices são uma sobrecarga nas cargas de dados. Quanto menos índices em uma tabela, melhor o desempenho durante a ingestão de dados.

Você pode identificar índices não utilizados de duas maneiras: pelo Repositório de Consultas e por uma consulta de uso de índice.

Query Store (Arquivo de Consultas)

O recurso Repositório de Consultas ajuda a identificar índices, que podem ser descartados com base em padrões de uso de consulta no banco de dados. Para obter orientação passo a passo, consulte Repositório de consultas.

Depois de habilitar o Repositório de Consultas no servidor, você pode usar a consulta a seguir para identificar índices que podem ser descartados conectando-se a azure_sys banco de dados.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Utilização do índice

Você também pode usar a seguinte consulta para identificar índices não utilizados:

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

As number_of_scanscolunas , tuples_reade indicariam tuples_fetched o índice usage.number_of_scans valor de coluna de zero pontos como um índice que não está sendo usado.

Ajuste de parâmetros do servidor

Nota

Siga as recomendações nos parâmetros a seguir somente se houver memória e espaço em disco suficientes.

  • maintenance_work_mem: Este parâmetro pode ser definido para um máximo de 2 GB na instância de servidor flexível do Banco de Dados do Azure para PostgreSQL. maintenance_work_mem ajuda a acelerar a criação de índices e adições de chaves estrangeiras.

  • checkpoint_timeout: Na instância flexível do servidor do Banco de Dados do Azure para PostgreSQL, o checkpoint_timeout valor pode ser aumentado para 10 ou 15 minutos a partir da configuração padrão de 5 minutos. Aumentar checkpoint_timeout para um valor maior, como 15 minutos, pode reduzir a carga de E/S, mas a desvantagem é que leva mais tempo para recuperar se houver uma falha. Recomendamos uma consideração cuidadosa antes de fazer a alteração.

  • checkpoint_completion_target: Recomendamos um valor de 0,9.

  • max_wal_size: Esse valor depende de SKU, armazenamento e carga de trabalho. Uma maneira de chegar ao valor correto para max_wal_size é mostrada no exemplo a seguir.

    Durante o horário comercial de pico, chegue a um valor fazendo o seguinte:

    a. Pegue o número de sequência de log WAL (LSN) atual executando a seguinte consulta:

    SELECT pg_current_wal_lsn ();

    b. Aguarde o checkpoint_timeout número de segundos. Pegue o LSN WAL atual executando a seguinte consulta:

    SELECT pg_current_wal_lsn ();

    c. Use os dois resultados para verificar a diferença, em GB:

    SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression: Pode ser ligado. A ativação desse parâmetro pode incorrer em algum custo adicional da CPU gasto na compactação durante o registro em log da WAL e na descompactação durante a reprodução da WAL.

Próximos passos