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