Обсуждение: Fwd: increase insert into local table from remote oracle table preformance
Fwd: increase insert into local table from remote oracle table preformance
От
Mariel Cherkassky
Дата:
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.
Re: Fwd: increase insert into local table from remote oracle tablepreformance
От
legrand legrand
Дата:
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
Re: Fwd: increase insert into local table from remote oracle table preformance
От
Mariel Cherkassky
Дата:
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
Re: Fwd: increase insert into local table from remote oracle tablepreformance
От
legrand legrand
Дата:
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
Re: Fwd: increase insert into local table from remote oracle table preformance
От
Mariel Cherkassky
Дата:
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.
Re: increase insert into local table from remote oracle tablepreformance
От
Daniel Blanch Bataller
Дата:
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’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>: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.
Re: Fwd: increase insert into local table from remote oracle tablepreformance
От
legrand legrand
Дата:
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