Thread: [PERFORM] performance problem on big tables

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 <mariel.cherkassky@gmail.com> 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 <scott.marlowe@gmail.com>:

On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> 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

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.

> \copy local_postresql_table from /tmp/tmp with binary - Didnt run because
> 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.

> 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.

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

> 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
> ?

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.

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 <mariel.cherkassky@gmail.com> 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 <daniel.blanch.bataller@gmail.com>:

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 <mariel.cherkassky@gmail.com> 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 <scott.marlowe@gmail.com>:

On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> 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

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.

> \copy local_postresql_table from /tmp/tmp with binary - Didnt run because
> 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.

> 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.

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

> 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
> ?

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.

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 <mariel.cherkassky@gmail.com> 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 <daniel.blanch.bataller@gmail.com>:

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 <mariel.cherkassky@gmail.com> 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 <daniel.blanch.bataller@gmail.com>:

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 <mariel.cherkassky@gmail.com> 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 <scott.marlowe@gmail.com>:

On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> 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

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.

> \copy local_postresql_table from /tmp/tmp with binary - Didnt run because
> 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.

> 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.

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

> 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
> ?

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.

On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> 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.

El 21 ago 2017, a las 10:00, Mariel Cherkassky <mariel.cherkassky@gmail.com> escribió:

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.

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 :

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?

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 <mariel.cherkassky@gmail.com>:

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 <mariel.cherkassky@gmail.com>:

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 <klaussfreire@gmail.com>:

On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> 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.

El 21 ago 2017, a las 13:27, Mariel Cherkassky <mariel.cherkassky@gmail.com> 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 <daniel.blanch.bataller@gmail.com>:

El 21 ago 2017, a las 10:00, Mariel Cherkassky <mariel.cherkassky@gmail.com> escribió:

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.

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 :

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?

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 <mariel.cherkassky@gmail.com>:

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 <mariel.cherkassky@gmail.com>:

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 <klaussfreire@gmail.com>:

On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> 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.

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

Mariel Cherkassky

Monday, August 21, 2017 8:22 AM

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.

Daniel Blanch Bataller

Monday, August 21, 2017 4:37 AM

El 21 ago 2017, a las 10:00, Mariel Cherkassky <mariel.cherkassky@gmail.com> escribió:

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.

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 :

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?

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 <mariel.cherkassky@gmail.com>:

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 <mariel.cherkassky@gmail.com>:

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 <klaussfreire@gmail.com>:

On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> 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.

Mariel Cherkassky

Monday, August 21, 2017 4:00 AM

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?

Mariel Cherkassky

Sunday, August 20, 2017 7:00 AM

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 ? 

Mariel Cherkassky

Sunday, August 20, 2017 2:39 AM

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 ? 

 

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Mariel Cherkassky
Sent: Monday, August 21, 2017 10:20 AM
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: pgsql-performance@postgresql.org
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

On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> 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.

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.

On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> 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 <klaussfreire@gmail.com>:
>>
>> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
>> <mariel.cherkassky@gmail.com> wrote:
>> > 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.
>>
>> 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.
>
>

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 );

On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> 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

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.