Subject: performance problem on big tables

From: Mariel Cherkassky <[email-masked]>
Date: Mon, 14 Aug 2017 16:24:24 +0300

I have performance issues with two big tables. Those tables are located on
an oracle remote database. I'm running the quert : insert into
local_postgresql_table select * from oracle_remote_table.

The first table has 45M records and its size is 23G. The import of the data
from the oracle remote database is taking 1 hour and 38 minutes. After that
I create 13 regular indexes on the table and it takes 10 minutes per table
->2 hours and 10 minutes in total.

The second table has 29M records and its size is 26G. The import of the
data from the oracle remote database is taking 2 hours and 30 minutes. The
creation of the indexes takes 1 hours and 30 minutes (some are indexes on
one column and the creation takes 5 min and some are indexes on multiples
column and it takes 11 min.

Those operation are very problematic for me and I'm searching for a
solution to improve the performance. The parameters I assigned :

min_parallel_relation_size = 200MB
max_parallel_workers_per_gather = 5
max_worker_processes = 8
effective_cache_size = 2500MB
work_mem = 16MB
maintenance_work_mem = 1500MB
shared_buffers = 2000MB
RAM : 5G
CPU CORES : 8

*-I tried running select count(*) from table in oracle and in postgresql
the running time is almost equal.*

*-Before importing the data I drop the indexes and the constraints.*

*-I tried to copy a 23G file from the oracle server to the postgresql
server and it took me 12 minutes.*

Please advice how can I continue ? How can I improve something in this
operation ?

---

From: MichaelDBA <[email-masked]>
Date: Mon, 14 Aug 2017 11:10:51 -0400

Total RAM on your host is 5GB, really?  Before touching anything else, 
increase your RAM.  That will be your big performance boost right 
there.  Then, you can "up" your effective_cache_size and 
maintenance_work_mem.

Regards,
Michael Vitale

> Mariel Cherkassky <mailto:[email-masked]>
> Monday, August 14, 2017 9:24 AM
>
> I have performance issues with two big tables. Those tables are 
> located on an oracle remote database. I'm running the quert : |insert 
> into local_postgresql_table select * from oracle_remote_table.|
>
> The first table has 45M records and its size is 23G. The import of the 
> data from the oracle remote database is taking 1 hour and 38 minutes. 
> After that I create 13 regular indexes on the table and it takes 10 
> minutes per table ->2 hours and 10 minutes in total.
>
> The second table has 29M records and its size is 26G. The import of 
> the data from the oracle remote database is taking 2 hours and 30 
> minutes. The creation of the indexes takes 1 hours and 30 minutes 
> (some are indexes on one column and the creation takes 5 min and some 
> are indexes on multiples column and it takes 11 min.
>
> min_parallel_relation_size =200MB
> ||
> max_parallel_workers_per_gather =5
> max_worker_processes =8
> effective_cache_size =2500MB
> work_mem =16MB
> maintenance_work_mem =1500MB
> shared_buffers =2000MB
> RAM :5G
> CPU CORES :8
>
> *-I tried running select count(*) from table in oracle and in 
> postgresql the running time is almost equal.*
>

---

From: Daniel Blanch Bataller <[email-masked]>
Date: Mon, 14 Aug 2017 17:11:55 +0200

Hi.

In general using COPY is *much faster* than anything else. You can even split the data load and run it in parallel, start with as many jobs as processors you have. Same with indexes, run them in parallel. With parallel I mean various psql running at the same time.

Tuning postgres will help too, but not as much as using COPY.

https://www.postgresql.org/docs/9.6/static/performance-tips.html <https://www.postgresql.org/docs/9.6/static/performance-tips.html>

https://www.postgresql.org/docs/9.6/static/populate.html <https://www.postgresql.org/docs/9.6/static/populate.html>

https://www.postgresql.org/docs/9.6/static/populate.html#POPULATE-COPY-FROM

Regards,

Daniel Blanch..

> El 14 ago 2017, a las 15:24, Mariel Cherkassky <[email-masked]> escribió:
> 
> I have performance issues with two big tables. Those tables are located on an oracle remote database. I'm running the quert : insert into local_postgresql_table select * from oracle_remote_table.
> 
> The first table has 45M records and its size is 23G. The import of the data from the oracle remote database is taking 1 hour and 38 minutes. After that I create 13 regular indexes on the table and it takes 10 minutes per table ->2 hours and 10 minutes in total.
> 
> The second table has 29M records and its size is 26G. The import of the data from the oracle remote database is taking 2 hours and 30 minutes. The creation of the indexes takes 1 hours and 30 minutes (some are indexes on one column and the creation takes 5 min and some are indexes on multiples column and it takes 11 min.
> 
> Those operation are very problematic for me and I'm searching for a solution to improve the performance. The parameters I assigned :
> 
> -I tried running select count(*) from table in oracle and in postgresql the running time is almost equal.
> 
> -Before importing the data I drop the indexes and the constraints.
> 
> -I tried to copy a 23G file from the oracle server to the postgresql server and it took me 12 minutes.
> 
> Please advice how can I continue ? How can I improve something in this operation ?
>

---

From: Rick Otten <[email-masked]>
Date: Mon, 14 Aug 2017 11:45:01 -0400

Moving that many gigs of data across your network could also take a long
time simply depending on your network configuration.  Before spending a
huge amount of energy tuning postgresql, I'd probably look at how long it
takes to simply copy 20 or 30 G of data between the two machines.

> data from the oracle remote database is taking 1 hour and 38 minutes. After
> that I create 13 regular indexes on the table and it takes 10 minutes per
> table ->2 hours and 10 minutes in total.
>

---

From: Jeff Janes <[email-masked]>
Date: Mon, 14 Aug 2017 09:39:06 -0700

> data from the oracle remote database is taking 1 hour and 38 minutes.
To investigate this, I'd decouple the two steps and see how long each one
takes:

\copy (select * from oracle_remote_table) to /tmp/tmp with binary
\copy local_postresql_table from /tmp/tmp with binary

Cheers,

Jeff

---

From: Mariel Cherkassky <[email-masked]>
Date: Tue, 15 Aug 2017 13:06:40 +0300

Hi,
So I I run the cheks that jeff mentioned :
\copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1 hour
and 35 minutes
\copy local_postresql_table from /tmp/tmp with binary - Didnt run because
the remote oracle database is currently under maintenance work.

So I decided to follow MichaelDBA tips and I set the ram on my machine to
16G and I configured the effective_cache memory to 14G,tshared_buffer to be
2G and maintenance_work_mem to 4G.

I started running the copy checks again and for now it coppied 5G in 10
minutes. I have some questions :
1)When I run insert into local_postresql_table select * from
remote_oracle_table I insert that data as bulk to the local table or row by
row ?  If the answer as bulk than why copy is a better option for this case
?
2)The copy from dump into the postgresql database should take less time
than the copy to dump ?
3)What do you think about the new memory parameters that I cofigured ?

---

From: Jeff Janes <[email-masked]>
Date: Tue, 15 Aug 2017 09:13:30 -0700

> the remote oracle database is currently under maintenance work.

The "\copy...from" doesn't depend on oracle, it would be only depend on
local file system (/tmp/tmp), provided that the "\copy...to" finished.
Anyway, given the length of time it took, I think you can conclude the
bottleneck is in oracle_fdw itself, or in Oracle, or the network.

Cheers,

Jeff

---

From: Scott Marlowe <[email-masked]>
Date: Tue, 15 Aug 2017 11:14:14 -0600

> \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1 hour

So 26G takes 95 minutes, or 27 MB/minute or 456k/second? Sound about
right (it's early, I haven't had enough coffee please check my math).
That's pretty slow unless you're working across pretty big distances
with mediocre connections.  My home internet downloads about 100MB/s
by comparison.

> the remote oracle database is currently under maintenance work.

You shouldn't need the remote oracle server if you've already copied
it over, you're just copying from local disk into the local pgsql db.
Unless I'm missing something.

> 2G and maintenance_work_mem to 4G.

Good settings. Maybe set work_mem to 128MB or so while you're at it.

> row ?  If the answer as bulk than why copy is a better option for this case

insert into select from oracle remote is one big copy, but it will
take at least as long as copying from oracle to the local network
took. Compare that to the same thing but use file_fdw on the file
locally.

> 2)The copy from dump into the postgresql database should take less time than
> the copy to dump ?

Yes. The copy from Oracle to your local drive is painfully slow for a
modern network connection.

> 3)What do you think about the new memory parameters that I cofigured ?

They should be OK. I'm more worried about the performance of the io
subsystem tbh.

-- 
Sent via pgsql-performance mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

---

From: Pavel Stehule <[email-masked]>
Date: Tue, 15 Aug 2017 19:44:51 +0200

2017-08-15 18:13 GMT+02:00 Jeff Janes <[email-masked]>:

>> \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1
>> hour and 35 minutes

dumping from Oracle is not fast - I seen it when oracle_fdw or ora2pg cases.

Regards

Pavel

---

From: Mariel Cherkassky <[email-masked]>
Date: Wed, 16 Aug 2017 15:26:29 +0300

After all the changes of the memory parameters the same operation(without
the copy utility) didnt run much faster - it  took one minute less. I made
a test with the copy command (without the 'with binary') and it took 1.5
hours to create the dumpfile in my local postgresql server. Then I tried to
run the copy from the local dump and it is already running two hours and it
didnt even finish. I looked at the server log and I saw that I run the copy
command at 13:18:05, 3 minutes later checkpoint started and completed and
there are no messages in the log after that. What can I do ? Improving the
memory parameters and the memory on the server didnt help and for now the
copy command doesnt help either.

2017-08-15 20:14 GMT+03:00 Scott Marlowe <[email-masked]>:

> hour
> than

---

From: Daniel Blanch Bataller <[email-masked]>
Date: Wed, 16 Aug 2017 15:08:56 +0200

See if the copy command is actually working, copy should be very fast from your local disk.

> El 16 ago 2017, a las 14:26, Mariel Cherkassky <[email-masked]> escribió:
> 
> 
> After all the changes of the memory parameters the same operation(without the copy utility) didnt run much faster - it  took one minute less. I made a test with the copy command (without the 'with binary') and it took 1.5 hours to create the dumpfile in my local postgresql server. Then I tried to run the copy from the local dump and it is already running two hours and it didnt even finish. I looked at the server log and I saw that I run the copy command at 13:18:05, 3 minutes later checkpoint started and completed and there are no messages in the log after that. What can I do ? Improving the memory parameters and the memory on the server didnt help and for now the copy command doesnt help either.
> 
> 
> 
> 
> 2017-08-15 20:14 GMT+03:00 Scott Marlowe <[email-masked] <mailto:[email-masked]>>:
> <[email-masked] <mailto:[email-masked]>> wrote:
> > Hi,

---

From: Mariel Cherkassky <[email-masked]>
Date: Wed, 16 Aug 2017 16:54:06 +0300

I run the copy command via psql to create a local dump of a 3G table and it
took me 134059.732ms =~2 minutes. After that I imported the data via copy
and it took 458648.677ms =~7 minutes. So the copy command works but pretty
slow.

---

From: Daniel Blanch Bataller <[email-masked]>
Date: Wed, 16 Aug 2017 16:04:30 +0200

Considering it has to write logs and data at checkpoints I don’t see it particularly slow compared to the extract phase. What kind of disks you have SSD or regular disks? Different disks for ltransaction logs and data?

> El 16 ago 2017, a las 15:54, Mariel Cherkassky <[email-masked]> escribió:
> 
> I run the copy command via psql to create a local dump of a 3G table and it took me 134059.732ms =~2 minutes. After that I imported the data via copy and it took 458648.677ms =~7 minutes. So the copy command works but pretty slow. 
> 
> 2017-08-16 16:08 GMT+03:00 Daniel Blanch Bataller <[email-masked] <mailto:[email-masked]>>:
>> El 16 ago 2017, a las 14:26, Mariel Cherkassky <[email-masked] <mailto:[email-masked]>> escribió:
>> 
>>

---

From: Mariel Cherkassky <[email-masked]>
Date: Wed, 16 Aug 2017 17:32:25 +0300

My server is virtual and it have virtual hd from a vnx storage machine. The
logs and the data are on the same disk.

2017-08-16 17:04 GMT+03:00 Daniel Blanch Bataller <
[email-masked]>:

> Considering it has to write logs and data at checkpoints I don’t see it
> particularly slow compared to the extract phase. What kind of disks you
> have SSD or regular disks? Different disks for ltransaction logs and data?
>
>
> I run the copy command via psql to create a local dump of a 3G table and
> it took me 134059.732ms =~2 minutes. After that I imported the data via
> copy and it took 458648.677ms =~7 minutes. So the copy command works but
> pretty slow.
>

---

From: Daniel Blanch Bataller <[email-masked]>
Date: Wed, 16 Aug 2017 23:46:18 +0200

Seems your disks are too slow. On my laptop (nothing special, just one disk) using COPY I can dump 3G in ~ 20 secs, loading takes 120 secs, bare copying 3G takes 10 secs. 

Similar proportion you had, but much faster. 

confirm I/O is your bottleneck, and tell us how you solved your problem

Anyway, You can cut import time by half if you set your destination table to unlogged (postgres will write half the data, it will save the transaction log writing). Remember to set it to logged when finished!!

Regards,

Daniel

> El 16 ago 2017, a las 16:32, Mariel Cherkassky <[email-masked]> escribió:
> 
> My server is virtual and it have virtual hd from a vnx storage machine. The logs and the data are on the same disk.
> 
> 2017-08-16 17:04 GMT+03:00 Daniel Blanch Bataller <[email-masked] <mailto:[email-masked]>>:
>> El 16 ago 2017, a las 15:54, Mariel Cherkassky <[email-masked] <mailto:[email-masked]>> escribió:
>>

---

From: Mariel Cherkassky <[email-masked]>
Date: Thu, 17 Aug 2017 09:25:32 +0300

Hi Daniel,
I already tried to set the destination table to unlogged - it improved the
performance slightly. Is there a way to make sure that I/O is the problem ?

2017-08-17 0:46 GMT+03:00 Daniel Blanch Bataller <
[email-masked]>:

> Seems your disks are too slow. On my laptop (nothing special, just one
> disk) using COPY I can dump 3G in ~ 20 secs, loading takes 120 secs, bare
> copying 3G takes 10 secs.
>
> Anyway, You can cut import time by half if you set your destination table
> to unlogged (postgres will write half the data, it will save the
> transaction log writing). Remember to set it to logged when finished!!
>
>
> Regards,
>
> Daniel
>
> My server is virtual and it have virtual hd from a vnx storage machine.
> The logs and the data are on the same disk.
>
>>> After all the changes of the memory parameters the same
>>> operation(without the copy utility) didnt run much faster - it  took one
>>> minute less. I made a test with the copy command (without the 'with
>>> binary') and it took 1.5 hours to create the dumpfile in my local
>>> postgresql server. Then I tried to run the copy from the local dump and it
>>> is already running two hours and it didnt even finish. I looked at the
>>> server log and I saw that I run the copy command at 13:18:05, 3 minutes
>>> later checkpoint started and completed and there are no messages in the log
>>> after that. What can I do ? Improving the memory parameters and the memory
>>> on the server didnt help and for now the copy command doesnt help either.
>>>
>>>
>>>
>>>

---

From: Mariel Cherkassky <[email-masked]>
Date: Thu, 17 Aug 2017 12:00:18 +0300

I checked with the storage team in the company and they saw that I have
alot of io on the server. How should I reduce the io that the postgresql
uses ?

---

From: Daniel Blanch Bataller <[email-masked]>
Date: Thu, 17 Aug 2017 12:06:39 +0200

I would just check how does it take to copy 3GB using an standard copy command. on my computer it took 10 secs. 

> El 17 ago 2017, a las 11:00, Mariel Cherkassky <[email-masked]> escribió:
> 
> I checked with the storage team in the company and they saw that I have alot of io on the server. How should I reduce the io that the postgresql uses ?
> 
> 2017-08-17 9:25 GMT+03:00 Mariel Cherkassky <[email-masked] <mailto:[email-masked]>>:
> Hi Daniel,
> I already tried to set the destination table to unlogged - it improved the performance slightly. Is there a way to make sure that I/O is the problem ? 
> 
> 2017-08-17 0:46 GMT+03:00 Daniel Blanch Bataller <[email-masked] <mailto:[email-masked]>>:
>> El 16 ago 2017, a las 16:32, Mariel Cherkassky <[email-masked] <mailto:[email-masked]>> escribió:
>>

---

From: Claudio Freire <[email-masked]>
Date: Thu, 17 Aug 2017 13:37:29 -0300

On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
<[email-masked]> wrote:
> I checked with the storage team in the company and they saw that I have alot
> of io on the server. How should I reduce the io that the postgresql uses ?

Do you have concurrent activity on that server?

What filesystem are you using wherever the data is sitting?

If you've got concurrent fsyncs happening, some filesystems handle
that poorly. When you've got WAL and data mixed in a single disk, or
worse, filesystem, it happens often that the filesystem won't handle
the write barriers for the WAL efficiently. I/O gets intermingled with
bulk operations, and even small fsyncs will have to flush writes from
bulk operations, which makes a mess of things.

It is a very good idea, and in fact a recommended practice, to put WAL
on its own disk for that reason mainly.

With that little RAM, you'll also probably cause a lot of I/O in temp
files, so I'd also recommend setting aside another disk for a temp
tablespace so that I/O doesn't block other transactions as well.

This is all assuming you've got concurrent activity on the server. If
not, install iotop and try to see who's causing that much I/O.

---

From: Mariel Cherkassky <[email-masked]>
Date: Sun, 20 Aug 2017 09:39:45 +0300

This server is dedicated to be a postgresql production database, therefore
postgresql is the only thing the runs on the server. The fs that I`m using
is xfs. I`ll add two different disks - one for the wals and one for the
temp tablespace. Regarding the disk, what size should they be considering
that the database size is about 250G. Does 16G of ram considered little ? I
installed iotop and I see that postgresql writer is writing most of the
time and above all.

I mentioned that I perform alot of insert into table select * from table.
Before that I remove indexes,constraints and truncate the table. Should I
run vacuum before or after the operation ?

2017-08-17 19:37 GMT+03:00 Claudio Freire <[email-masked]>:

> alot

---

From: Mariel Cherkassky <[email-masked]>
Date: Sun, 20 Aug 2017 14:00:51 +0300

I realized something weird. When I`m preforming the copy utility of
postgresql in order to create dump from a local table in my postgresql db
it takes for 32G table 20 minutes. When I try to use copy for a foregin
table (on oracle database) It takes more than 2 hours.. During the copy
operation from the foreign table I dont see alot of write operations, with
iotop i see that its writes 3 M/s. What else I can check ?

---

From: Mariel Cherkassky <[email-masked]>
Date: Sun, 20 Aug 2017 14:32:09 +0300

When I run copy from local table the speed of the writing is 22 M/S. When I
use the copy from remote_oracle_Table it writes 3 M/s. SCP between the
servers coppies very fast. How should I continue ?

2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <[email-masked]>:

>> This server is dedicated to be a postgresql production database,
>> therefore postgresql is the only thing the runs on the server. The fs that
>> I`m using is xfs. I`ll add two different disks - one for the wals and one
>> for the temp tablespace. Regarding the disk, what size should they be
>> considering that the database size is about 250G. Does 16G of ram
>> considered little ? I installed iotop and I see that postgresql writer is
>> writing most of the time and above all.
>>

---

From: Mariel Cherkassky <[email-masked]>
Date: Mon, 21 Aug 2017 11:00:41 +0300

To summarize, I still have performance problems. My current situation :

I'm trying to copy the data of many tables in the oracle database into my
postgresql tables. I'm doing so by running insert into
local_postgresql_temp select * from remote_oracle_table. The performance of
this operation are very slow and I tried to check the reason for that and
mybe choose a different alternative.

1)First method - Insert into local_postgresql_table select * from
remote_oracle_table this generated total disk write of 7 M/s and actual
disk write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 minutes.

2)second method - copy (select * from oracle_remote_table) to
/tmp/dump generates
total disk write of 4 M/s and actuval disk write of 100 K/s. The copy
utility suppose to be very fast but it seems very slow.

-When I run copy from the local dump, the reading is very fast 300 M/s.

-I created a 32G file on the oracle server and used scp to copy it and it
took me a few minutes.

-The wals directory is located on a different file system. The parameters I
assigned :

min_parallel_relation_size = 200MB
max_parallel_workers_per_gather = 5
max_worker_processes = 8
effective_cache_size = 12GB
work_mem = 128MB
maintenance_work_mem = 4GB
shared_buffers = 2000MB
RAM : 16G
CPU CORES : 8

HOW can I increase the writes ? How can I get the data faster from the
oracle database to my postgresql database?

---

From: Daniel Blanch Bataller <[email-masked]>
Date: Mon, 21 Aug 2017 10:37:40 +0200

> El 21 ago 2017, a las 10:00, Mariel Cherkassky <[email-masked]> escribió:
> 
> I'm trying to copy the data of many tables in the oracle database into my postgresql tables. I'm doing so by running insert into local_postgresql_temp select * from remote_oracle_table. The performance of this operation are very slow and I tried to check the reason for that and mybe choose a different alternative.
> 
> 1)First method - Insert into local_postgresql_table select * from remote_oracle_table this generated total disk write of 7 M/s and actual disk write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 minutes.
> 
> 2)second method - copy (select * from oracle_remote_table) to /tmp/dump generates total disk write of 4 M/s and actuval disk write of 100 K/s. The copy utility suppose to be very fast but it seems very slow.
> 
> 

Are you using a FDW to access oracle server and then dump it using copy? This is going to be slow, FDW isn't fast.

> -When I run copy from the local dump, the reading is very fast 300 M/s.

You reported it was slow before. What has changed? How much does it take to load the 32G table then?

> -I created a 32G file on the oracle server and used scp to copy it and it took me a few minutes.
> 
> 
> -The wals directory is located on a different file system. The parameters I assigned :
> 
> HOW can I increase the writes ? How can I get the data faster from the oracle database to my postgresql database?
> 
> 

Extract the table to a file in the oracle server in a format that the COPY utility can read, then copy it to postgres server and load it. You can even pipe commands and do it in a single step.

This is what I meant when I said that COPY is much faster than any thing else. To make it even faster, if I/O is not your bottleneck, you can chop the table in chunks and load it in parallel as I told you before, I have done this many times when migrating data from oracle to postgres. ora2pg uses this method to migrate data from oracle to postgres too. 

> 
> 2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <[email-masked] <mailto:[email-masked]>>:
> I realized something weird. When I`m preforming the copy utility of postgresql in order to create dump from a local table in my postgresql db it takes for 32G table 20 minutes. When I try to use copy for a foregin table (on oracle database) It takes more than 2 hours.. During the copy operation from the foreign table I dont see alot of write operations, with iotop i see that its writes 3 M/s. What else I can check ? 
> 
> 2017-08-20 9:39 GMT+03:00 Mariel Cherkassky <[email-masked] <mailto:[email-masked]>>:
> This server is dedicated to be a postgresql production database, therefore postgresql is the only thing the runs on the server. The fs that I`m using is xfs. I`ll add two different disks - one for the wals and one for the temp tablespace. Regarding the disk, what size should they be considering that the database size is about 250G. Does 16G of ram considered little ? I installed iotop and I see that postgresql writer is writing most of the time and above all.
> 
> I mentioned that I perform alot of insert into table select * from table. Before that I remove indexes,constraints and truncate the table. Should I run vacuum before or after the operation ? 
> 
> 2017-08-17 19:37 GMT+03:00 Claudio Freire <[email-masked] <mailto:[email-masked]>>:

---

From: Mariel Cherkassky <[email-masked]>
Date: Mon, 21 Aug 2017 14:27:37 +0300

All this operation runs as part of a big transaction that I run. How can I
create a dump in the oracle server and copy it to the postgresql server
from a postgresql transaction ? Chopping the table is optional when I use
copy, but when I use copy to remote oracle table it takes longer to create
the dump.

2017-08-21 11:37 GMT+03:00 Daniel Blanch Bataller <
[email-masked]>:

> local_postgresql_temp select * from remote_oracle_table. The performance
> of this operation are very slow and I tried to check the reason for that
> and mybe choose a different alternative.
>
> 2)second method - copy (select * from oracle_remote_table) to /tmp/dump generates
> Are you using a FDW to access oracle server and then dump it using copy?
> This is going to be slow, FDW isn't fast.
>
>
> Extract the table to a file in the oracle server in a format that the COPY
> utility can read, then copy it to postgres server and load it. You can even
> pipe commands and do it in a single step.
>
> This is what I meant when I said that COPY is much faster than any thing
> else. To make it even faster, if I/O is not your bottleneck, you can chop
> the table in chunks and load it in parallel as I told you before, I have
> done this many times when migrating data from oracle to postgres. ora2pg
> uses this method to migrate data from oracle to postgres too.
>
>
>>> I mentioned that I perform alot of insert into table select * from
>>> table. Before that I remove indexes,constraints and truncate the table.
>>> Should I run vacuum before or after the operation ?
>>>

---

From: Daniel Blanch Bataller <[email-masked]>
Date: Mon, 21 Aug 2017 13:53:30 +0200

> El 21 ago 2017, a las 13:27, Mariel Cherkassky <[email-masked]> escribió:
> 
> All this operation runs as part of a big transaction that I run.
> How can I create a dump in the oracle server and copy it to the postgresql server from a postgresql transaction ?

I guess you could create a user defined function in any of the available languages (perl, python, java, …). Functions run inside transactions too…this is not simple, though. 

> Chopping the table is optional when I use copy, but when I use copy to remote oracle table it takes longer to create the dump. 

It may take longer depending on how the oracle machine, table and database are configured. In my experience oracle is not very fast dumping whole tables, not to mention tables with BLOB data, which can be as slow as hundreds of records per second  (which is probably not your case).

If this transaction is to synchronize data between transactional servers and data analysis servers you may consider using some type of replication where only changes are sent. EnterpriseDB has tools to do such things, I’m not aware of any other tool that can do this between oracle and postgres.

Regards,

Daniel.

> 
> 2017-08-21 11:37 GMT+03:00 Daniel Blanch Bataller <[email-masked] <mailto:[email-masked]>>:
> 
>> El 21 ago 2017, a las 10:00, Mariel Cherkassky <[email-masked] <mailto:[email-masked]>> escribió:
>>

---

From: Mariel Cherkassky <[email-masked]>
Date: Mon, 21 Aug 2017 15:22:58 +0300

I`m searching for a way to improve the current performance, I'm not
interesting in using a different tool or writing something new because I'm
trying to migrate a system on oracle database to a postgresql database.

2017-08-21 14:53 GMT+03:00 Daniel Blanch Bataller <
[email-masked]>:

> I guess you could create a user defined function in any of the available
> languages (perl, python, java, …). Functions run inside transactions
> too…this is not simple, though.
>
> Chopping the table is optional when I use copy, but when I use copy to
> remote oracle table it takes longer to create the dump.
>
>
> It may take longer depending on how the oracle machine, table and database
> are configured. In my experience oracle is not very fast dumping whole
> tables, not to mention tables with BLOB data, which can be as slow as
> hundreds of records per second  (which is probably not your case).
>
> If this transaction is to synchronize data between transactional servers
> and data analysis servers you may consider using some type of replication
> where only changes are sent. EnterpriseDB has tools to do such things, I’m
> not aware of any other tool that can do this between oracle and postgres.
>
> Regards,
>
> Daniel.
>
>
>> Extract the table to a file in the oracle server in a format that the
>> COPY utility can read, then copy it to postgres server and load it. You can
>> even pipe commands and do it in a single step.
>>
>> 2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <[email-masked]
>> >:
>>
>>> 2017-08-20 9:39 GMT+03:00 Mariel Cherkassky <[email-masked]
>>> >:
>>>

---

From: MichaelDBA <[email-masked]>
Date: Mon, 21 Aug 2017 09:55:32 -0400

Maybe I missed it in this continuous thread activity, but have you tried 
'''ora2pg"?  You can export from Oracle and import to Postgres in 
parallel jobs.  The import commands use the efficient COPY command by 
default (unless you override it in the ora2pg configuration file).  You 
can do the export and subsequent import in memory, but I would suggest 
the actual file export and import so you can take advantage of the 
parallel feature.

Regards,
Michael Vitale

> Monday, August 21, 2017 8:22 AM
> interesting in using a different tool or writing something new because 
> I'm trying to migrate a system on oracle database to a postgresql 
> database.
>
>
> Daniel Blanch Bataller <mailto:[email-masked]>
> Monday, August 21, 2017 4:37 AM
>
>> El 21 ago 2017, a las 10:00, Mariel Cherkassky 
>> <[email-masked] <mailto:[email-masked]>> 
>> escribió:
>>
>> I'm trying to copy the data of many tables in the oracle database 
>> into my postgresql tables. I'm doing so by running |insert into 
>> local_postgresql_temp select * from remote_oracle_table|. The 
>> performance of this operation are very slow and I tried to check the 
>> reason for that and mybe choose a different alternative.
>>
>> 1)First method - |Insert into local_postgresql_table select * from 
>> remote_oracle_table| this generated total disk write of 7 M/s and 
>> actual disk write of 4 M/s(iotop). For 32G table it took me 2 hours 
>> and 30 minutes.
>>
>> 2)second method - |copy (select * from oracle_remote_table) to 
>> /tmp/dump| generates total disk write of 4 M/s and actuval disk write 
>> of 100 K/s. The copy utility suppose to be very fast but it seems 
>> very slow.
>>
>>
>
>
> Are you using a FDW to access oracle server and then dump it using 
> copy? This is going to be slow, FDW isn't fast.
>
>
>> |min_parallel_relation_size=  200MB
>> max_parallel_workers_per_gather=  5
>> max_worker_processes=  8
>> effective_cache_size=  12GB
>> work_mem=  128MB
>> maintenance_work_mem=  4GB
>> shared_buffers=  2000MB
>> RAM:  16G
>> CPU CORES:  8|
>>
>> HOW can I increase the writes ? How can I get the data faster from 
>> the oracle database to my postgresql database?
>>
>>
>
>
> COPY utility can read, then copy it to postgres server and load it. 
> You can even pipe commands and do it in a single step.
>
> This is what I meant when I said that COPY is much faster than any 
> thing else. To make it even faster, if I/O is not your bottleneck, you 
> can chop the table in chunks and load it in parallel as I told you 
> before, I have done this many times when migrating data from oracle to 
> postgres. ora2pg uses this method to migrate data from oracle to 
> postgres too.
>
>>
>> 2017-08-20 14:00 GMT+03:00 Mariel Cherkassky 
>> <[email-masked] <mailto:[email-masked]>>:
>>
>>     I realized something weird. When I`m preforming the copy utility
>>     of postgresql in order to create dump from a local table in my
>>     postgresql db it takes for 32G table 20 minutes. When I try to
>>     use copy for a foregin table (on oracle database) It takes more
>>     than 2 hours.. During the copy operation from the foreign table I
>>     dont see alot of write operations, with iotop i see that its
>>     writes 3 M/s. What else I can check ?
>>
>>     2017-08-20 9:39 GMT+03:00 Mariel Cherkassky
>>     <[email-masked] <mailto:[email-masked]>>:
>>
>>         This server is dedicated to be a postgresql production
>>         database, therefore postgresql is the only thing the runs on
>>         the server. The fs that I`m using is xfs. I`ll add two
>>         different disks - one for the wals and one for the temp
>>         tablespace. Regarding the disk, what size should they be
>>         considering that the database size is about 250G. Does 16G of
>>         ram considered little ? I installed iotop and I see that
>>         postgresql writer is writing most of the time and above all.
>>
>>         I mentioned that I perform alot of insert into table select *
>>         from table. Before that I remove indexes,constraints and
>>         truncate the table. Should I run vacuum before or after the
>>         operation ?
>>
>>         2017-08-17 19:37 GMT+03:00 Claudio Freire
>>         <[email-masked] <mailto:[email-masked]>>:
>>
>>             <[email-masked]
>>             <mailto:[email-masked]>> wrote:
> Monday, August 21, 2017 4:00 AM
> I'm trying to copy the data of many tables in the oracle database into 
> my postgresql tables. I'm doing so by running |insert into 
> local_postgresql_temp select * from remote_oracle_table|. The 
> performance of this operation are very slow and I tried to check the 
> reason for that and mybe choose a different alternative.
>
> 1)First method - |Insert into local_postgresql_table select * from 
> remote_oracle_table| this generated total disk write of 7 M/s and 
> actual disk write of 4 M/s(iotop). For 32G table it took me 2 hours 
> and 30 minutes.
>
> 2)second method - |copy (select * from oracle_remote_table) to 
> /tmp/dump| generates total disk write of 4 M/s and actuval disk write 
> of 100 K/s. The copy utility suppose to be very fast but it seems very 
> slow.
>
> |min_parallel_relation_size =200MB
> effective_cache_size =12GB
> work_mem =128MB
> maintenance_work_mem =4GB
> shared_buffers =2000MB
> RAM :16G
> CPU CORES :8|
>
> Sunday, August 20, 2017 7:00 AM
> postgresql in order to create dump from a local table in my postgresql 
> db it takes for 32G table 20 minutes. When I try to use copy for a 
> foregin table (on oracle database) It takes more than 2 hours.. During 
> the copy operation from the foreign table I dont see alot of write 
> operations, with iotop i see that its writes 3 M/s. What else I can 
> check ?
>
> Sunday, August 20, 2017 2:39 AM
> therefore postgresql is the only thing the runs on the server. The fs 
> that I`m using is xfs. I`ll add two different disks - one for the wals 
> and one for the temp tablespace. Regarding the disk, what size should 
> they be considering that the database size is about 250G. Does 16G of 
> ram considered little ? I installed iotop and I see that postgresql 
> writer is writing most of the time and above all.
>
> table. Before that I remove indexes,constraints and truncate the 
> table. Should I run vacuum before or after the operation ?
>
>

---

From: Mariel Cherkassky <[email-masked]>
Date: Mon, 21 Aug 2017 17:19:57 +0300

I had a system that consist from many objects(procedures,functions..) on an
oracle database. We decided to integrate that system to postgresql. That
system coppied alot of big tables from a different read only oracle
database and preformed on it alot of queries to produce reports. The part
of getting the data is part of some procedures, I cant change it so freely.
I'm searching a way to improve the perfomance of the database because I'm
sure that I didnt conifgure something well. Moreover, When I run complicted
queries (joint between 4 big tables and filtering) it takes alot of time
and I see that the server is cacheing all my ram memory.

2017-08-21 16:55 GMT+03:00 MichaelDBA <[email-masked]>:

> '''ora2pg"?  You can export from Oracle and import to Postgres in parallel
> jobs.  The import commands use the efficient COPY command by default
> (unless you override it in the ora2pg configuration file).  You can do the
> export and subsequent import in memory, but I would suggest the actual file
> export and import so you can take advantage of the parallel feature.
>
> Regards,
> Michael Vitale
>
> Mariel Cherkassky <[email-masked]>
> Daniel Blanch Bataller <[email-masked]>
> Mariel Cherkassky <[email-masked]>
> Mariel Cherkassky <[email-masked]>
> Mariel Cherkassky <[email-masked]>

---

From: Igor Neyman <[email-masked]>
Date: Mon, 21 Aug 2017 14:35:30 +0000

From: [email-masked] [mailto:[email-masked]] On Behalf Of Mariel Cherkassky
Sent: Monday, August 21, 2017 10:20 AM
To: MichaelDBA <[email-masked]>
Cc: [email-masked]
Subject: Re: [PERFORM] performance problem on big tables

I had a system that consist from many objects(procedures,functions..) on an oracle database. We decided to integrate that system to postgresql. That system coppied alot of big tables from a different read only oracle database and preformed on it alot of queries to produce reports. The part of getting the data is part of some procedures, I cant change it so freely. I'm searching a way to improve the perfomance of the database because I'm sure that I didnt conifgure something well. Moreover, When I run complicted queries (joint between 4 big tables and filtering) it takes alot of time and I see that the server is cacheing all my ram memory.

Probably your joins are done on Postgres side.

m.b. instead of Postgres pulling data from Oracle, you should try pushing data from Oracle to Postgres using Oracle’s Heterogeneous Services and Postgres ODBC driver. In this case you do your joins and filtering on Oracles side and just push the result set to Postgres.
That’s how I did migration from Oracle to Postgres.

Regards,
Igor Neyman

---

From: Mariel Cherkassky <[email-masked]>
Date: Mon, 21 Aug 2017 17:37:22 +0300

I already finished migrating the system from oracle to postgresql. Right
now, I'm trying to improve its performance - Im bringing data from another
read only database that is updaded every minute. I cant push data from the
oracle side to the postgresql side because the oracle database is read only.

2017-08-21 17:35 GMT+03:00 Igor Neyman <[email-masked]>:

>
>
> *From:* [email-masked] [mailto:pgsql-performance-
> [email-masked]] *On Behalf Of *Mariel Cherkassky
> *Sent:* Monday, August 21, 2017 10:20 AM
> *To:* MichaelDBA <[email-masked]>
> *Cc:* [email-masked]
> *Subject:* Re: [PERFORM] performance problem on big tables
>
>
>
> I had a system that consist from many objects(procedures,functions..) on
> an oracle database. We decided to integrate that system to postgresql. That
> m.b. instead of Postgres pulling data from Oracle, you should try pushing
> data from Oracle to Postgres using Oracle’s Heterogeneous Services and
> Postgres ODBC driver. In this case you do your joins and filtering on
> Oracles side and just push the result set to Postgres.
>

---

From: Michael DNA <[email-masked]>
Date: Mon, 21 Aug 2017 10:51:47 -0400

If your procedures to get the data is part is a query predicate, then you can still use ora2pg

Sent from my iPhone

> On Aug 21, 2017, at 10:35 AM, Igor Neyman <[email-masked]> wrote:
> 
>

---

From: Claudio Freire <[email-masked]>
Date: Wed, 23 Aug 2017 20:15:44 -0300

On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
<[email-masked]> wrote:
> postgresql tables. I'm doing so by running insert into local_postgresql_temp
> select * from remote_oracle_table. The performance of this operation are
> very slow and I tried to check the reason for that and mybe choose a
> different alternative.
>
> remote_oracle_table this generated total disk write of 7 M/s and actual disk
> write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 minutes.
>
> 2)second method - copy (select * from oracle_remote_table) to /tmp/dump
> generates total disk write of 4 M/s and actuval disk write of 100 K/s. The
> copy utility suppose to be very fast but it seems very slow.

Have you tried increasing the prefetch option in the remote table?

If you left it in its default, latency could be hurting your ability
to saturate the network.

---

From: Mariel Cherkassky <[email-masked]>
Date: Thu, 24 Aug 2017 10:51:11 +0300

Hi Claudio, how can I do that ? Can you explain me what is this option ?

2017-08-24 2:15 GMT+03:00 Claudio Freire <[email-masked]>:

> local_postgresql_temp
> disk

---

From: Claudio Freire <[email-masked]>
Date: Thu, 24 Aug 2017 13:14:05 -0300

On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky
<[email-masked]> wrote:
>> > my

Please don't top-post.

I'm assuming you're using this: http://laurenz.github.io/oracle_fdw/

If you check the docs, you'll see this:
https://github.com/laurenz/oracle_fdw#foreign-table-options

So I'm guessing you could:

ALTER FOREIGN TABLE remote_table OPTIONS ( SET prefetch 10240 );

---

From: Mariel Cherkassky <[email-masked]>
Date: Sun, 27 Aug 2017 19:34:45 +0300

Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it
but I'm getting error

dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch
10240 );
ERROR:  syntax error at or near "10240"
LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 );

dbch=#  alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch
'10240');
ERROR:  option "prefetch" not found

2017-08-24 19:14 GMT+03:00 Claudio Freire <[email-masked]>:

> /tmp/dump

---

From: Claudio Freire <[email-masked]>
Date: Mon, 28 Aug 2017 02:47:04 -0300

On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky
<[email-masked]> wrote:
> dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240
> );

Yeah, might need to put the 10240 in quotes.

---

From: Claudio Freire <[email-masked]>
Date: Mon, 28 Aug 2017 02:51:22 -0300

> ERROR:  option "prefetch" not found

Oh, sorry, I hadn't seen this until I hit send.

Unless the documentation is inaccurate or you're using a really old
version (from the changelog that option is from 2016), that should
work.

I don't have enough experience with oracle_fdw to help there, most of
my dealings have been with postgres_fdw.

---

From: Mariel Cherkassky <[email-masked]>
Date: Mon, 28 Aug 2017 09:05:30 +0300

I have the newest version :
select oracle_diag();
                                                     oracle_diag
---------------------------------------------------------------------------------------------------------------------
 oracle_fdw 1.5.0, PostgreSQL 9.6.3, Oracle client 11.2.0.4.0,
ORACLE_HOME=/PostgreSQL/9.6/tools/instantclient_11_2/
(1 row)

Is there a prefetch also for local tables ? I mean If I run with a cursor
over results of a select query, mybe setting the prefetch for a local table
might also improve performance ?

2017-08-28 8:51 GMT+03:00 Claudio Freire <[email-masked]>:

> 10240