increase insert into local table from remote oracle table preformance
increase insert into local table from remote oracle table preformance
От:
Mariel Cherkassky <mariel.cherkassky@gmail.com>
Дата:
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: increase insert into local table from remote oracle tablepreformance
От:
Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Дата:
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.
Re: increase insert into local table from remote oracle tablepreformance
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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
Re: increase insert into local table from remote oracle table preformance
От:
Andrew Kerber <andrew.kerber@gmail.com>
Дата:
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 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 >