Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fpy6x-0004iH-9N for pgsql-performance@arkaria.postgresql.org; Wed, 15 Aug 2018 15:53:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fpy6v-00035z-3k for pgsql-performance@arkaria.postgresql.org; Wed, 15 Aug 2018 15:53:25 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fpy6U-0000sC-0g for pgsql-performance@lists.postgresql.org; Wed, 15 Aug 2018 15:52:58 +0000 Received: from authsmtp19.register.it ([81.88.48.42] helo=authsmtp.register.it) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fpy6Q-00039P-6Y for pgsql-performance@postgresql.org; Wed, 15 Aug 2018 15:52:56 +0000 Received: from [192.168.1.33] ([80.30.24.217]) by cmsmtp with ESMTPSA id py6Mfj2UrnNDGpy6MfpK3f; Wed, 15 Aug 2018 17:52:51 +0200 X-Rid: dblanch@converta.es@80.30.24.217 From: Daniel Blanch Bataller Message-Id: <69AB6D6D-22BA-4A27-8116-0F5095A816D4@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_A83319C9-A6B3-42D6-B274-273EEDDA5952" Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: increase insert into local table from remote oracle table preformance Date: Wed, 15 Aug 2018 17:52:49 +0200 In-Reply-To: Cc: legrand legrand , PostgreSQL mailing lists To: Mariel Cherkassky References: <1534194238024-0.post@n3.nabble.com> <1534285714771-0.post@n3.nabble.com> X-Mailer: Apple Mail (2.3445.9.1) X-CMAE-Envelope: MS4wfHpGF1wMkgNYfJwcte2sGGgzxD92ufAX79WdUtGbOiV7hyTKWF9j2DRR/juhjsXwcTlZkfGJkqr44LOe5q5NMoacHrX+m5NT0Nr9RK83kkQXXnORnCXE +eJRJ8KmUK0JMPO5Ftx1RuHylDML8Yw8XEGFn6yVlhQcu3fKJrI67pBjIMh0iDrqBC23OWSRqn8qhYmVu2HAuAvWQ4EID9+K5Ufss5bvJ0OdDrqfXDMpn1y4 Oo3phDtXJvNGQpmeqmL2LU9p1fP8Ap/9l1AA+AswboHjhNLvJ6jKRRQ/s4F4ApG8 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_A83319C9-A6B3-42D6-B274-273EEDDA5952 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 The Postgres command of choice to load bulk data is COPY = https://www.postgresql.org/docs/current/static/sql-copy.html = is much = faster than anything else. It=E2=80=99s likely that the slowest part could be Oracle exporting = it=E2=80=99s data. Try to use sqlplus to export the data and see how = long does it take, you won=E2=80=99t be able to make the process faster = than Oracle can export it=E2=80=99s data. If it=E2=80=99s fast enough, format the resulting file in a suitable = format for Postgres =E2=80=98COPY FROM=E2=80=99 command. Finally you can pipe the Oracle export command and the Postgres COPY = FROM command, so the process can run twice as fast.=20 You can make it even faster if you divide the exported data by any = criteria and run those export | import scripts in parallel.=20 > El 15 ago 2018, a las 10:43, Mariel Cherkassky = escribi=C3=B3: >=20 > Inserting directly into the partition didnt help, the performance are = just the same. I tried to increase the prefetch value to 1000(alter = foreign table hist_oracle options (add prefetch '1000') but still no = change - 15 minutes for one partition(6GB). >=20 > On the oracle side the plan is full scan on the partition (I'm copying = the entire partition into a postgresql partition..) >=20 > 2018-08-15 1:28 GMT+03:00 legrand legrand >: > main ideas are: >=20 > - inserting directly to the right partition: > perform as many inserts as pg partitions found in main_table_hist, = like > INSERT INTO 14/08/2018_value1 select * from remote_oracle_hist where > day=3Dto_date('14/08/2018','DD/MM/YYYY') and value=3D'value1' >=20 > please check execution plan (in Oracle db) using EXPLAIN ANALYZE >=20 > - all those inserts should be executed in // (with 4 or 8 sql scripts) >=20 > - wal archiving should be disabled during hist data recovery only (not > during day to day operations) >=20 > - for prefetch see >=20 > https://github.com/laurenz/oracle_fdw = >=20 > prefetch (optional, defaults to "200") >=20 > Sets the number of rows that will be fetched with a single round-trip > between PostgreSQL and Oracle during a foreign table scan. This is > implemented using Oracle row prefetching. The value must be between 0 = and > 10240, where a value of zero disables prefetching. >=20 > Higher values can speed up performance, but will use more memory on = the > PostgreSQL server. >=20 >=20 > Regards > PAscal >=20 >=20 >=20 > -- > Sent from: = http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html = >=20 >=20 --Apple-Mail=_A83319C9-A6B3-42D6-B274-273EEDDA5952 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
The Postgres command of choice to load bulk data is = COPY https://www.postgresql.org/docs/current/static/sql-copy.html is much faster than anything else.

It=E2=80=99s likely that the slowest = part could be Oracle exporting it=E2=80=99s data. Try to use sqlplus to = export the data and see how long does it take, you won=E2=80=99t be able = to make the process faster than Oracle can export it=E2=80=99s = data.

If = it=E2=80=99s fast enough, format the resulting file in a suitable format = for Postgres =E2=80=98COPY FROM=E2=80=99 command.
Finally you can pipe the Oracle export = command and the Postgres COPY FROM command, so the process can run twice = as fast. 

You can make it even faster if you divide the exported data = by any criteria and run those export | import scripts in = parallel. 





El 15 ago 2018, a las 10:43, Mariel Cherkassky <mariel.cherkassky@gmail.com> escribi=C3=B3:

Inserting directly into the = partition didnt help, the performance are just the same. I tried to = increase the prefetch value to 1000(alter foreign table hist_oracle = options (add prefetch '1000') but still no change - 15 minutes for one = partition(6GB).

On the oracle side the plan is full scan on the = partition (I'm copying the entire partition into a postgresql = partition..)

2018-08-15 1:28 = GMT+03:00 legrand legrand <legrand_legrand@hotmail.com>:
main ideas are:

- inserting directly to the right partition:
  perform as many inserts as pg partitions found in = main_table_hist, like
  INSERT INTO 14/08/2018_value1 select * from remote_oracle_hist = where
day=3Dto_date('14/08/2018','DD/MM/YYYY') and = value=3D'value1'

please check execution plan (in Oracle db) using EXPLAIN ANALYZE

- all those inserts should be executed in // (with 4 or 8 sql = scripts)

- wal archiving should be disabled during hist data recovery only = (not
during day to day operations)

- for prefetch see

https://github.com/laurenz/oracle_fdw

prefetch (optional, defaults to "200")

Sets the number of rows that will be fetched with a single round-trip
between PostgreSQL and Oracle during a foreign table scan. This is
implemented using Oracle row prefetching. The value must be between 0 = and
10240, where a value of zero disables prefetching.

Higher values can speed up performance, but will use more memory on = the
PostgreSQL server.


Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html


= --Apple-Mail=_A83319C9-A6B3-42D6-B274-273EEDDA5952--