Início / dba / Perguntas / 41059
Accepted
Yang
Asked: 2013-04-27 16:20:59 +0800 CST

Otimizando o desempenho da atualização em massa no PostgreSQL

  • 772

Usando PG 9.1 no Ubuntu 12.04.

Atualmente, leva até 24h para executarmos um grande conjunto de instruções UPDATE em um banco de dados, que são da forma:

UPDATE table
SET field1 = constant1, field2 = constant2, ...
WHERE id = constid

(Estamos apenas sobrescrevendo campos de objetos identificados por ID.) Os valores vêm de uma fonte de dados externa (ainda não no banco de dados em uma tabela).

As tabelas têm um punhado de índices cada e nenhuma restrição de chave estrangeira. Nenhum COMMIT é feito até o final.

Demora 2h para importar um pg_dumpbanco de dados inteiro. Esta parece ser uma linha de base que devemos razoavelmente visar.

Além de produzir um programa personalizado que de alguma forma reconstrói um conjunto de dados para o PostgreSQL reimportar, há algo que possamos fazer para aproximar o desempenho do UPDATE em massa ao da importação? (Esta é uma área que acreditamos que as árvores de mesclagem estruturadas em log lidam bem, mas estamos nos perguntando se há algo que possamos fazer no PostgreSQL.)

Algumas ideias:

  • descartando todos os índices não-ID e reconstruindo depois?
  • aumentando checkpoint_segments, mas isso realmente ajuda a sustentar a taxa de transferência de longo prazo?
  • usando as técnicas mencionadas aqui ? (Carregar novos dados como tabela e, em seguida, "mesclar" dados antigos onde o ID não é encontrado em novos dados)

Basicamente, há um monte de coisas para tentar e não temos certeza de quais são as mais eficazes ou se estamos negligenciando outras coisas. Passaremos os próximos dias experimentando, mas pensamos em perguntar aqui também.

Eu tenho carga simultânea na tabela, mas é somente leitura.

postgresql performance

2 respostas

  1. Best Answer

    Suposições

    Como as informações estão faltando no Q, vou assumir:

    • Seus dados vêm de um arquivo no servidor de banco de dados.
    • Os dados são formatados exatamente como a COPYsaída, com um exclusivo id por linha para corresponder à tabela de destino.
      Caso contrário, formate-o corretamente primeiro ou use COPYas opções para lidar com o formato.
    • Você está atualizando cada linha na tabela de destino ou a maioria delas.
    • Você pode descartar e recriar a tabela de destino.
      Isso significa que não há acesso simultâneo. Senão, considere esta resposta relacionada:
    • Não há objetos dependentes, exceto os índices.

    Solução

    Eu sugiro que você siga uma abordagem semelhante, conforme descrito no link do seu terceiro marcador . Com grandes otimizações.

    Para criar a tabela temporária, existe uma forma mais simples e rápida:

    CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

    Um único grande UPDATEde uma tabela temporária dentro do banco de dados será mais rápido do que atualizações individuais de fora do banco de dados em várias ordens de magnitude.

    No modelo MVCC do PostgreSQL , um UPDATEmeio de criar uma nova versão de linha e marcar a antiga como excluída. Isso é tão caro quanto um INSERTe um DELETEcombinado. Além disso, deixa você com muitas tuplas mortas. Como você está atualizando toda a tabela de qualquer maneira, seria mais rápido criar uma nova tabela e descartar a antiga.

    Se você tiver RAM suficiente disponível, defina temp_buffers(somente para esta sessão!) alto o suficiente para manter a tabela temporária na RAM - antes de fazer qualquer outra coisa.

    Para obter uma estimativa de quanta RAM é necessária, execute um teste com uma pequena amostra e use funções de tamanho de objeto db :

    SELECT pg_size_pretty(pg_relation_size('tmp_tbl'));  -- complete size of table
SELECT pg_column_size(t) FROM tmp_tbl t LIMIT 10;  -- size of sample rows

    Roteiro completo

    SET temp_buffers = '1GB';        -- example value

CREATE TEMP TABLE tmp_tbl AS SELECT * FROM tbl LIMIT 0;

COPY tmp_tbl FROM '/absolute/path/to/file';

CREATE TABLE tbl_new AS
SELECT t.col1, t.col2, u.field1, u.field2
FROM   tbl     t
JOIN   tmp_tbl u USING (id);

-- Create indexes like in original table
ALTER TABLE tbl_new ADD PRIMARY KEY ...;
CREATE INDEX ... ON tbl_new (...);
CREATE INDEX ... ON tbl_new (...);

-- exclusive lock on tbl for a very brief time window!
DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

DROP TABLE tmp_tbl; -- will also be dropped at end of session automatically

    Carga simultânea

    Operações simultâneas na tabela (que eu descartei nas suposições no início) vão esperar, uma vez que a tabela é bloqueada perto do final e falham assim que a transação é confirmada, porque o nome da tabela é resolvido para seu OID imediatamente, mas a nova tabela tem um OID diferente. A tabela permanece consistente, mas as operações simultâneas podem receber uma exceção e precisam ser repetidas. Detalhes nesta resposta relacionada:

    ATUALIZAR rota

    Se você (teve que) seguir a UPDATErota, elimine qualquer índice que não seja necessário durante a atualização e recrie-o posteriormente. É muito mais barato criar um índice de uma só vez do que atualizá-lo para cada linha individual. Isso também pode permitir atualizações HOT .

    Eu descrevi um procedimento semelhante usando UPDATEnesta resposta intimamente relacionada no SO .

     

    • 60

  2. Se os dados puderem ser disponibilizados em um arquivo estruturado, você poderá lê-lo com um wrapper de dados externo e realizar uma mesclagem na tabela de destino.

    • 2

relate perguntas