Início / dba / Perguntas / 165329
Accepted
Vao Tsun
Asked: 2017-02-24 08:11:21 +0800 CST

postgres_fdw mais lento do que copiar + scp + copiar (~ 12 vezes)

  • 772

  • servidor estrangeiro 9.2

  • servidor local 9.5

  • mesa é 10GB

  • transferência de dados realizada na mesma interface de rede que o servidor externo funciona

  • nenhum índice definido nos dados

  • à moda antiga:

    1. copiar para - 2:36
    2. scp - 08:17
    3. copiar de - 10:11

  • postgres_fdw:

    1. quando o old way terminou, ele fez 800 MB deinsert into .. select * from foreign_table

Perdi algo na configuração (o que significa que posso melhorá-lo) ou postgres_fdwsimplesmente não é para carregamento em massa (o que significa que não posso melhorá-lo)?

(Eu o uso para reconciliação de pequenas quantidades de dados e funciona bem. A ideia de insert select from fdw, em vez de executar comandos bash, parecia tão legal.)*

Eu tentei o psql para o servidor remoto do servidor local e \copy table- seis minutos - mais rápido que o ssh.

A fetch_sizeopção, não disponível antes de 9.6, pode ser simulada dblink_fetch(CURSOR, fetch_size)- veja minha resposta abaixo.

postgresql bulk

2 respostas

  1. Best Answer

    postgres_fdw certamente não é tão otimizado para transferência em massa quanto copy to, copy from, e scpsão. Afinal, a transferência em massa é o principal motivo da existência dessas ferramentas.

    Mas isso não significa que não há nada que você possa fazer. Se você estivesse executando o 9.6 no servidor local, você poderia tentar aumentar o fetch_size.

    • 4

  2. antes de 9.6 fetch_size não pode ser definido para servidor nem para tabela externa, mas podemos simular essa opção usando dblinks para operação em massa. No caso abaixo, acelerei a seleção em massa de tabela de ~1 GB de postgres_fdw de hora e meia para dois minutos , simulando a mudança de fetch_size de 100 para 100K.

    Graças a @jjanes, comecei a pesquisar o fetch_sizedisponível a partir de 9.6. Infelizmente, não posso fazer upgrade, então tive que implementar uma solução alternativa. Observando pg_stat_activityno remoto, notei FETCH 100 FROM c1no servidor local, então pensei que fetch_size = 100 provavelmente é codificado em versões anteriores. Então, executei um pequeno resumo para buscar dados com dblink buscado por 100 linhas:

    truncate table bin.t1;
begin;
do
$$
declare
 _r record;
begin
  perform dblink_connect('past');
  perform dblink_exec('BEGIN;');
  perform dblink_open('cr', 'select * from rel');

  for _r in 0..130*1000 loop /* I know the number of rows is apxm 130*1000*100*/
    raise info '%',concat(lpad(_r::text,4,'0'),': ',clock_timestamp());
    insert into bin.t1
      SELECT * FROM dblink_fetch('cr', 100) 
        AS (a integer, b character varying(200), c double precision)
    ;
  end loop;

  perform dblink_close('cr');
  perform dblink_exec('END;');
  perform dblink_disconnect();
end;
$$
;
end;
DO
Time: 4235292.205 ms
db=# end;
COMMIT
Time: 89.988 ms
db=# select count(1) from bin.t1;
  count
----------
 12309947
(1 row)

    Então levou 4235 segundos... Em seguida , aumentei fetch_size no meu wrap-up de 100 para 100*1000 :

    truncate table bin.t2;
begin;
do
$$
declare
 _r record;
begin
  perform dblink_connect('past');
  perform dblink_exec('BEGIN;');
  perform dblink_open('cr', 'select * from rel');

  for _r in 0..130 loop
    raise info '%',concat(lpad(_r::text,4,'0'),': ',clock_timestamp());
    insert into bin.t2
      SELECT * FROM dblink_fetch('cr', 100*1000) 
        AS (a integer, b character varying(200), c double precision)
    ;
  end loop;

  perform dblink_close('cr');
  perform dblink_exec('END;');
  perform dblink_disconnect();
end;
$$
;
end;
DO
Time: 89606.764 ms
db=# end;
COMMIT
Time: 0.153 ms
db=# select count(1) from bin.t2;
-[ RECORD 1 ]---
count | 12309947

    Então eu vi **FETCH 100000 FROM cr**como esperado pg_stat_activitye o tempo de execução mudou de 4235 segundos para 90 segundos - que é 40 e poucos vezes!

    Por último, isso insert select from postgres_fdwleva mais ou menos o mesmo tempo que o dblink finaliza com o fetch 100:

    db=# insert into bin.t3 select * from remote.rel;
INSERT 0 12296752
Time: 5321428.694 ms
    • 2

relate perguntas