Início / dba / Perguntas / 123244
Accepted
davetapley
Asked: 2015-12-09 14:37:45 +0800 CST

Atualize iterando a tabela em lotes mais rapidamente do que a tabela inteira no PostgreSQL

  • 772

Eu tenho uma tabela com aproximadamente um milhão de linhas.

Ele está sendo usado em produção e executei um UPDATEque cobre aproximadamente 95% dessas linhas.

Depois de cinco horas , cancelei o pedido porque estava demorando muito.

A tabela tem uma coluna de ID de incremento automático, então tentei estender a WHEREcondição da minha consulta para incluir id BETWEEN 1 AND 500.

Esta atualização foi concluída em aproximadamente dois segundos. Em seguida, iterei manualmente idem lotes de 500, por exemplo BETWEEN 500 AND 1000, depois BETWEEN 1000 AND 1500.

Nesse ritmo, serão necessários 2.000 lotes de 500 para atualizar toda a tabela.

A atualização de 2.000 lotes de 2 segundos cada leva pouco mais de uma hora .

Minhas perguntas são:

  1. Que razões existem para esta diferença?
  2. Não me importo com o isolamento da transação, então existe uma maneira de simular essa 'atualização em lotes', sem precisar fazer o script das 2000 atualizações para serem executadas separadamente.
postgresql performance

1 respostas

  1. Best Answer

    Farei o possível para responder resumidamente à sua pergunta, mas como não estou realmente ciente de seu nível de conforto com o PostgreSQL e não tenho muito tempo para entrar em uma explicação detalhada de qualquer maneira, Vou manter as respostas simples e você pode pedir esclarecimentos se quiser mais informações.

    1) Por que é mais rápido em lotes?

    Devido à estrutura do registro de gravação antecipada do PostgreSQL , à quantidade de espaço de buffer compartilhado na RAM e à tentativa de executar UPDATEtudo em uma única transação, meu palpite é que você simplesmente não tem recursos de computação suficientes para lidar com eficiência com a atualização para quase um milhão de registros em uma única transação.

    O PostgreSQL possui um sistema de controle de concorrência bem construído , o que significa essencialmente que ele deve manter as cópias antigas de suas pré UPDATE-linhas disponíveis durante sua UPDATEoperação. Isso para que, caso outro cliente tente acessar essas linhas enquanto você estiver atualizando, caso a atualização falhe, ou caso você cancele a atualização, você não perca as informações antigas.

    Se você executar um grande o suficiente UPDATE, o PostgreSQL carregará as páginas na memória e as modificará, mas eventualmente ficará sem memória para trabalhar, então ele é forçado a copiar imediatamente essas páginas temporariamente para o disco se quiser carregar outras páginas e continuar a transação.

    Em vez de poder amortizar as gravações de disco durante um período de tempo, você apenas forçou seu banco de dados a um gargalo.

    2) Script das atualizações

    Você absolutamente pode fazer o script das atualizações, criando uma função em PL/pgSQL . Há muito o que aprender sobre PL/pgSQL, incluindo muitas coisas que provavelmente não sei, mas de modo geral, você poderia fazer algo assim

    CREATE OR REPLACE FUNCTION mini_batch_update()
  RETURNS void AS
$BODY$
DECLARE

id_val integer;

BEGIN

  FOR id_val IN 0..2000
  LOOP

  **UPDATE GOES HERE**
  WHERE id > (500 * id_val) AND id <= 500 * (id_val + 1);

  END LOOP;


END
$BODY$
LANGUAGE plpgsql;

    Não demorei muito para fazer esse lote funcionar da melhor forma possível; o que quero dizer é que simplesmente codifiquei vários dos valores numéricos para simplificar. No seu caso, você pode querer obter mais detalhes e incluir: 1) Algo que verifique o valor máximo de id para que você defina seus limites adequadamente e 2) mesmo que eu tenha codificado lotes de 500, você pode facilmente fazer isso um parâmetro de entrada de função.

    Desculpe, não tenho tempo para testar isso ou garantir que realmente funcione bem. Boa sorte!

    • 10

relate perguntas