pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedincrease insert into local table from remote oracle table preformance
11+ messages / 6 participants
[nested] [flat]
* increase insert into local table from remote oracle table preformance
@ 2018-08-13 20:34 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: Mariel Cherkassky @ 2018-08-13 20:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
I'm using postgresql v10.4. I have a local partitioned table (by range -
data, every day has its own table). I'm using the oracle_fdw extension to
bring data from the oracle partitioned table into my local postgresql
(insert into local select * from remote_oracle). Currently, I dont have any
indexes on the postgresql`s table. It takes me 10 hours to copy 200G over
the network and it is very slow.
Any recommandations what can I change or improve ?
Thanks , Mariel.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Fwd: increase insert into local table from remote oracle table preformance
@ 2018-08-13 20:35 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Mariel Cherkassky @ 2018-08-13 20:35 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm using postgresql v10.4. I have a local partitioned table (by range -
data, every day has its own table). I'm using the oracle_fdw extension to
bring data from the oracle partitioned table into my local postgresql
(insert into local select * from remote_oracle). Currently, I dont have any
indexes on the postgresql`s table. It takes me 10 hours to copy 200G over
the network and it is very slow.
Any recommandations what can I change or improve ?
Thanks , Mariel.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Fwd: increase insert into local table from remote oracle table preformance
@ 2018-08-13 21:03 legrand legrand <legrand_legrand@hotmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: legrand legrand @ 2018-08-13 21:03 UTC (permalink / raw)
To: pgsql-performance
Did you try
- runing multiple inserts in parallel,
- Stop wal archiving,
- Tune fetch sise ?
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: increase insert into local table from remote oracle table preformance
@ 2018-08-14 05:21 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Mark Kirkwood @ 2018-08-14 05:21 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi, we probably need more inforation to offer anything useful here, e.g:
- The network bandwidth between the 2 hosts
- the number of partitions on the Postgres end (i.e how many days in
your case)
- single or batched INSERT
The lack of indexes is probably not going to effect INSERT performance
that much, but the number of partition tables has a huge impact, so we
need to know this stuff!
Cheers
Mark
On 14/08/18 08:34, Mariel Cherkassky wrote:
> Hi,
> I'm using postgresql v10.4. I have a local partitioned table (by range
> - data, every day has its own table). I'm using the oracle_fdw
> extension to bring data from the oracle partitioned table into my
> local postgresql (insert into local select * from remote_oracle).
> Currently, I dont have any indexes on the postgresql`s table. It takes
> me 10 hours to copy 200G over the network and it is very slow.
> Any recommandations what can I change or improve ?
>
>
> Thanks , Mariel.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: increase insert into local table from remote oracle table preformance
@ 2018-08-14 07:12 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2018-08-14 07:12 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-admin@lists.postgresql.org
Mariel Cherkassky wrote:
> Hi,
> I'm using postgresql v10.4. I have a local partitioned table (by range - data, every day has its own table).
> I'm using the oracle_fdw extension to bring data from the oracle partitioned table into my local postgresql
> (insert into local select * from remote_oracle). Currently, I dont have any indexes on the postgresql`s table.
> It takes me 10 hours to copy 200G over the network and it is very slow.
> Any recommandations what can I change or improve ?
Hard to say anything with so little data.
You could try a bigger value for the "prefetch" option.
One known reason for slow performance is if there are LOBs in the Oracle table.
You could parallelize processing by running several such INSERTs in
parallel, perhaps one per partition, and inserting directly into
the partitions.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Fwd: increase insert into local table from remote oracle table preformance
@ 2018-08-14 11:33 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mariel Cherkassky @ 2018-08-14 11:33 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
Hi,
I'll try to answer all your question so that you will have more information
about the situation :
I have one main table that is called main_table_hist. The "main_table _hist"
is partitioned by range (date column) and includes data that is considered
as "history data" . I'm trying to copy the data from the oracle table to my
local postgresql table (about 5T). For every day in the year I have in the
oracle table partition and therefore I will create for every day in year
(365 in total) a partition in postgresql. Every partition of day consist of
4 different partitions by list (text values). So In total my tables
hierarchy should look like that :
main_table_hist
14/08/2018_main
14/08/2018_value1
14/08/2018_value2
14/08/2018_value3
14/08/2018_value1
Moreover, I have another table that is called "present_data" that consist
of 7 partitions (the data of the last 7 days - 300G) that I'm loading from
csv files (daily). Every night I need to deattach the last day partition
and attach it to the history table.
This hierarchy works well in oracle and I'm trying to build it on
postgresql. Right now I'm trying to copy the history data from the remote
database but as I suggested it takes 10 hours for 200G.
Some details :
-Seting the wals to minimum is possible but I cant do that as a daily work
around because that means restarting the database.
I must have wals generated in order to restore the "present_data" in case
of disaster.
-The network
-My network bandwidth is 1GB.
-The column in the table are from types : character varying,big
int,timestamp,numeric. In other words no blobs.
-I have many check constraints on the table.
- Laurenz - "You could try a bigger value for the "prefetch" option."- Do
you have an example how to do it ?
-Inserting directly into the right parittion might increase the preformance
?
Thanks , Mariel.
2018-08-14 0:03 GMT+03:00 legrand legrand <legrand_legrand@hotmail.com>:
> Did you try
> - runing multiple inserts in parallel,
> - Stop wal archiving,
> - Tune fetch sise ?
>
> Regards
> PAscal
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-
> f2050081.html
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Fwd: increase insert into local table from remote oracle table preformance
@ 2018-08-14 22:28 legrand legrand <legrand_legrand@hotmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: legrand legrand @ 2018-08-14 22:28 UTC (permalink / raw)
To: pgsql-performance
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=to_date('14/08/2018','DD/MM/YYYY') and value='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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Fwd: increase insert into local table from remote oracle table preformance
@ 2018-08-15 08:43 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Mariel Cherkassky @ 2018-08-15 08:43 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
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=to_date('14/08/2018','DD/MM/YYYY') and value='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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: increase insert into local table from remote oracle table preformance
@ 2018-08-15 11:42 Andrew Kerber <andrew.kerber@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 11+ messages in thread
From: Andrew Kerber @ 2018-08-15 11:42 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Mariel Cherkassky <mariel.cherkassky@gmail.com>; pgsql-admin@lists.postgresql.org
You need to track down your limited resource. IO, CPU, or network. I would say it’s unlikely to be CPU, but you never know. Look at the activities on each server and see what resource is maxed out. My guess is IO, but you could also have your network choked.
Sent from my iPad
> On Aug 14, 2018, at 02:12, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> Mariel Cherkassky wrote:
>> Hi,
>> I'm using postgresql v10.4. I have a local partitioned table (by range - data, every day has its own table).
>> I'm using the oracle_fdw extension to bring data from the oracle partitioned table into my local postgresql
>> (insert into local select * from remote_oracle). Currently, I dont have any indexes on the postgresql`s table.
>> It takes me 10 hours to copy 200G over the network and it is very slow.
>> Any recommandations what can I change or improve ?
>
> Hard to say anything with so little data.
>
> You could try a bigger value for the "prefetch" option.
>
> One known reason for slow performance is if there are LOBs in the Oracle table.
>
> You could parallelize processing by running several such INSERTs in
> parallel, perhaps one per partition, and inserting directly into
> the partitions.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: increase insert into local table from remote oracle table preformance
@ 2018-08-15 15:52 Daniel Blanch Bataller <daniel.blanch.bataller@gmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Daniel Blanch Bataller @ 2018-08-15 15:52 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: legrand legrand <legrand_legrand@hotmail.com>; pgsql-performance
The Postgres command of choice to load bulk data is COPY https://www.postgresql.org/docs/current/static/sql-copy.html <https://www.postgresql.org/docs/current/static/sql-copy.html; is much faster than anything else.
It’s likely that the slowest part could be Oracle exporting it’s data. Try to use sqlplus to export the data and see how long does it take, you won’t be able to make the process faster than Oracle can export it’s data.
If it’s fast enough, format the resulting file in a suitable format for Postgres ‘COPY FROM’ 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ó:
>
> 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 <mailto: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=to_date('14/08/2018','DD/MM/YYYY') and value='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 <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 <http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html;
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Fwd: increase insert into local table from remote oracle table preformance
@ 2018-08-15 20:26 legrand legrand <legrand_legrand@hotmail.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: legrand legrand @ 2018-08-15 20:26 UTC (permalink / raw)
To: pgsql-performance
This is not so bad, you where at 10h for 200GB (20GB/h),
And now at 24GB/h, it makes a 20% increase ;0)
Could you tell us what are the résults with parallel exécutions
(Before to switch to unload reload strategy)
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2018-08-15 20:26 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-13 20:34 increase insert into local table from remote oracle table preformance Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-08-13 20:35 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-08-13 21:03 ` legrand legrand <legrand_legrand@hotmail.com>
2018-08-14 11:33 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-08-14 22:28 ` legrand legrand <legrand_legrand@hotmail.com>
2018-08-15 08:43 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-08-15 15:52 ` Daniel Blanch Bataller <daniel.blanch.bataller@gmail.com>
2018-08-15 20:26 ` legrand legrand <legrand_legrand@hotmail.com>
2018-08-14 05:21 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-08-14 07:12 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-08-15 11:42 ` Andrew Kerber <andrew.kerber@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox