主页 / dba / 问题 / 165329
Accepted
Vao Tsun
Asked: 2017-02-24 08:11:21 +0800 CST

postgres_fdw 比 copy + scp + copy 慢（~12 次）

  • 772

  • 国外服务器 9.2

  • 本地服务器 9.5

  • 表是 10GB

  • 在与外国服务器相同的网络接口上执行数据传输

  • 没有为数据设置索引

  • 老办法：

    1. 复制到 - 2:36
    2. SCP-08:17
    3. 从 - 10:11 复制

  • postgres_fdw：

    1. 到老方法完成时，它已经完成了 800MBinsert into .. select * from foreign_table

我是否错过了配置中的某些内容（意味着我可以改进它），或者postgres_fdw只是不适合批量加载（意味着我无法改进它）？

（我将它用于小数据量的协调，它工作正常。insert select from fdw代替运行 bash 命令的想法看起来很不错。）*

我尝试从本地服务器到远程服务器的 psql \copy table- 六分钟 - 比通过 ssh 更快。

该fetch_size选项在 9.6 之前不可用，可以模拟dblink_fetch(CURSOR, fetch_size)- 请参阅下面的答案。

postgresql bulk

2 个回答

  1. Best Answer

    postgres_fdw 对于批量传输当然不如copy to,copy from和scpare 优化。毕竟，批量传输是这些工具存在的主要原因。

    但这并不意味着您无能为力。如果您在本地服务器上运行 9.6，您可以尝试增加 fetch_size。

    • 4

  2. 在 9.6 之前，无法为服务器或外部表设置 fetch_size，但我们可以使用 dblinks 模拟此选项以进行批量操作。在下面的例子中，我通过模拟 fetch_size 从 100 到 100K 的变化，将postgres_fdw 中的 ~1GB 表批量选择从一小时半加速到两分钟。

    感谢@jjanes，我fetch_size从 9.6 开始研究可用。唉，我不能去升级，所以我不得不实施一个解决方法。pg_stat_activity我从本地服务器上注意到远程观看FETCH 100 FROM c1，所以我认为 fetch_size = 100 在以前的版本中可能是硬编码的。所以我跑了一个小总结，用 dblink 获取 100 行的数据：

    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)

    所以花了 4235 秒...接下来我将总结中的 fetch_size 从 100 增加到 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

    所以我看到**FETCH 100000 FROM cr**和预期的一样pg_stat_activity，执行时间从 4235 秒变为 90 秒- 这是 40 倍！

    最后要提到的是insert select from postgres_fdw，dblink 使用 fetch 100 结束所花费的时间或多或少相同：

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

相关问题