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 = 200MBgather = 5max_worker_processes = 8effective_cache_size = 2500MBwork_mem = 16MBmaintenance_work_mem = 1500MBshared_buffers = 2000MBRAM : 5GCPU 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 ?
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 ?
2017-08-14 16:24 GMT+03:00 Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx>:
