Compartilhar 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 diversos métodos para carregar dados em massa no servidor flexível do Banco de Dados do Azure para PostgreSQL, com as melhores práticas para os carregamento de dados iniciais em bancos de dados vazios e para os carregamentos 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:

  • Execute um comando INSERT de registro único.
  • Faça o carregamento em lote de 100 a 1.000 linhas por commit. É possível usar um bloco de transação para agrupar diversos registros por commit.
  • Execute INSERT com diversos valores de linha.
  • Execute o comando COPY.

O método preferencial para carregar dados em um banco de dados é usar o comando COPY. Se não for possível usar o comando COPY, o próximo melhor método é usar o lote INSERT. O multithreading com um comando COPY é ideal para carregar dados em massa.

Etapas para carregar dados em massa

Aqui estão as etapas para carregar dados em massa no servidor flexível do Banco de Dados do Azure para PostgreSQL.

Etapa 1: preparar os dados

Verifique se os dados estão limpos e formatados corretamente para o banco de dados.

Etapa 2: Escolher o método de carregamento

Selecione o método de carregamento apropriado com base no tamanho e na complexidade dos dados.

Etapa 3: Executar o método de carregamento

Execute o método de carregamento escolhido para carregar seus dados no banco de dados.

Etapa 4: Verificar os dados

Depois de carregar, verifique se os dados foram carregados corretamente no banco de dados.

Práticas recomendadas para carregamentos de dados iniciais

Aqui estão as práticas recomendadas para carregamentos de dados iniciais.

Remover índices

Antes de fazer um carregamento de dados inicial, é recomendável eliminar todos os índices nas tabelas. É sempre mais eficiente criar os índices após o carregamento dos dados.

Remover restrições

Veja a descrição das seguintes principais restrições de remoção:

  • Restrições exclusivas da chave

Para obter um alto desempenho, recomenda-se remover as restrições de chaves exclusivas antes de um carregamento de dados inicial e recria-las após a operação. No entanto, descartar restrições de chave exclusivas cancela as proteções contra dados duplicados.

  • Restrições de chave estrangeira

Recomenda-se remover as restrições de chave estrangeira antes do carregamento de dados inicial e recriá-las após a operação.

Alterar o parâmetro session_replication_role para replica também desabilita todas as verificações de chave estrangeira. No entanto, se a alteração não for usada corretamente, ela poderá deixar os dados inconsistentes.

Tabelas não registradas

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

O uso de tabelas não registradas acelera o carregamento de dados. Os dados gravados em tabelas não registradas não são gravados no log de gravação antecipada.

Veja as seguintes desvantagens do uso de tabelas não registradas:

  • Não são seguras contra falhas. Uma tabela não registrada é automaticamente truncada após uma falha ou um 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 esse tipo, use as seguintes opções:

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

    CREATE UNLOGGED TABLE <tablename>;

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

    ALTER TABLE <tablename> SET UNLOGGED;

Ajuste de parâmetro do servidor

  • auto vacuum': It's best to turn off vácuo automático durante a carga de dados inicial. Após a conclusão do carregamento inicial, recomenda-se executar um VACUUM ANALYZE manual em todas as tabelas do banco de dados e, em seguida, ativar auto vacuum.

Observação

Siga as recomendações somente 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 a criação automática de chaves estrangeiras, índices e vácuo automático.

  • checkpoint_timeout: em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL, o valor checkpoint_timeout 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 do servidor flexível do Banco de Dados do Azure para PostgreSQL.

  • checkpoint_completion_target: recomenda-se 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 de dados inicial.

  • wal_compression: isso pode ser ativado. A ativação desse parâmetro pode gerar algum custo extra de CPU na compactação durante o WAL (log write-ahead) e na descompactação durante a reprodução do WAL.

Recomendações

Antes de iniciar um carregamento de dados inicial na instância do servidor flexível do Banco de Dados do Azure para PostgreSQL, recomendamos que você:

  • Desabilitar a alta disponibilidade no servidor. É possível habilitar após a conclusão do carregamento inicial no primário.
  • Criar réplicas de leitura após o carregamento inicial de dados.
  • Minimize o registro em log tanto quanto possível ou desative-o completamente durante os carregamentos iniciais de dados (por exemplo: desabilitar pgaudit, pg_stat_statements, repositório de consultas).

Recriar índices e adicionar restrições

Supondo que os índices e as restrições foram removidos antes do carregamento inicial, recomenda-se usar valores altos em maintenance_work_mem (conforme mencionado anteriormente) para criar índices e adicionar restrições. Além disso, a partir do PostgreSQL 11, os seguintes parâmetros podem ser modificados para uma criação de índice paralelo mais rápida após o carregamento inicial de dados:

  • max_parallel_workers: define o número máximo de trabalhos 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 em CREATE INDEX.

Também é possível criar índices realizando as configurações recomendadas no nível da sessão. Veja o seguinte exemplo de como fazer isso:

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

As práticas recomendadas para cargas de dados incrementais são descritas aqui:.

Tabelas de partição

É sempre recomendado particionar tabelas grandes. Algumas vantagens do particionamento, especialmente durante cargas incrementais, incluem o seguinte:

  • A criação de partições com base em novos deltas torna a adição de dados à tabela mais eficiente.
  • A manutenção de tabelas torna-se mais fácil. É possível descartar uma partição durante um carregamento incremental de dados para evitar exclusões demoradas em tabelas grandes.
  • O vácuo automático seria disparado apenas em partições que foram alteradas ou adicionadas durante cargas incrementais, o que facilita a manutenção de estatísticas na tabela.

Manter estatísticas atualizadas da tabela

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 saber mais, confira Monitoramento e ajuste do Autovacuum.

Criar índices em restrições de chave estrangeira

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

  • Atualizações de dados ou exclusões na tabela pai. Quando os dados são atualizados ou excluídos na tabela pai, as pesquisas são executadas na tabela filho. Você pode indexar chaves estrangeiras na tabela filho para fazer pesquisas mais rapidamente.
  • Consultas, nas quais é possível ver a junção de tabelas pai e filho em colunas-chave.

Identificar índices não utilizados

Identifique índices não utilizados no banco de dados e remova-os. Índices são uma sobrecarga em cargas de dados. Quanto menos índices houver em uma tabela, melhor será o desempenho durante a ingestão de dados.

É possível identificar índices não utilizados de duas maneiras: por Repositório de Consultas e por uma consulta de uso de índice.

Repositório de Consultas

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

Depois de habilitar o Repositório de Consultas no servidor, é possível usar a consulta a seguir para identificar índices que podem ser removidos por meio da conexão com o banco de dados azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Uso do índice

Também é possível 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 colunas number_of_scans, tuples_read e tuples_fetched indicam o valor da coluna usage.number_of_scans do índice de zero pontos como um índice que não está sendo usado.

Ajuste de parâmetro do servidor

Observação

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

  • maintenance_work_mem: esse parâmetro pode ser definido como 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 índice e adições de chave estrangeira.

  • checkpoint_timeout: na instância do servidor flexível do Banco de Dados do Azure para PostgreSQL, o valor checkpoint_timeout pode ser aumentado para 10 ou 15 minutos a partir da configuração padrão de 5 minutos. Aumentar checkpoint_timeout para um valor mais significativo, como 15 minutos, pode reduzir a carga de E/S, mas a desvantagem é que leva mais tempo para a recuperação em caso de falhas. Recomenda-se considerar cuidadosamente antes de realizar uma alteração.

  • checkpoint_completion_target: recomenda-se um valor de 0,9.

  • max_wal_size: esse valor depende da SKU, do armazenamento e da 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. Obtenha o LSN (número de sequência de log) do WAL atual executando a seguinte consulta:

SELECT pg_current_wal_lsn ();

b. Aguarde checkpoint_timeout segundos. Execute a seguinte consulta para obter o LSN atual do WAL:

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 running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;
  • wal_compression: isso pode ser ativado. Habilitar esse parâmetro pode incorrer em um custo extra de CPU para compactação durante o registro em log e descompactação do WAL durante a reprodução do WAL.

Conteúdo relacionado