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 1.000 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 é o COPY comando. Se o COPY comando não for impossível, batch INSERT é o próximo melhor método. Multi-threading com um COPY comando é ideal para carregar dados em massa.

Etapas para carregar dados em massa

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

Passo 1: Preparar os seus dados

Certifique-se de que seus dados estejam limpos e formatados corretamente para o banco de dados.

Passo 2: Escolha o método de carregamento

Selecione o método de carregamento apropriado com base no tamanho e na complexidade dos seus 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

Após o upload, verifique se os dados foram carregados corretamente no banco de dados.

Práticas recomendadas para cargas iniciais de dados

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

Índices de queda

Antes de fazer uma carga inicial de dados, recomendamos descartar todos os índices nas tabelas. Criar os índices depois que os dados são carregados é sempre mais eficiente.

Restrições de queda

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

  • Principais restrições exclusivas

Para obter um desempenho forte, recomendamos eliminar restrições de chave exclusivas antes de uma carga de dados inicial e recriá-las 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 eliminar as restrições de chave estrangeira antes da carga inicial de dados e recriá-las após a conclusão da carga de dados.

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

Tabelas não registadas

Considere os prós e contras das tabelas não registradas antes de usá-las em 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 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

  • auto vacuum': It's best to turn off auto vacuum' durante a carga inicial de dados. 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 auto vacuumo .

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 vácuo automático, 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 aumentar o valor para 1 hora antes de carregar inicialmente os dados 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: Isto pode ser ativado. A habilitação desse parâmetro pode incorrer em alguns custos extras da CPU para compactação durante o registro de registro write-ahead (WAL) e descompactação durante a reprodução de WAL.

Recomendações

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 todo junto durante as cargas iniciais de dados (por exemplo, desabilitar pgaudit, pg_stat_statements, query store).

Recriar índices e adicionar restrições

Supondo que você tenha descartado os índices e restrições antes da carga inicial, recomendamos o uso de 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

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

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 a adição de 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. Você pode indexar chaves estrangeiras na tabela filho para fazer pesquisas mais rápidas.
  • Consultas, onde você pode ver tabelas pai e filho se unindo 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 mais significativo, como 15 minutos, pode reduzir a carga de E/S, mas a desvantagem é que leva mais tempo para se 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. O exemplo a seguir mostra uma maneira de chegar ao valor correto para max_wal_size.

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

Conteúdos relacionados