Postgresql 9.6 writing data from remote oracle db is slow -


im using oracle_fdw extension in postgresql database. i'm trying copy data of many tables in oracle database postgresql tables. i'm doing running insert local_postgresql_temp select * remote_oracle_table. performance of operation slow , tried check reason , mybe choose different alternative.

1)first method - insert local_postgresql_table select * remote_oracle_table generated total disk write of 7 m/s , actual disk write of 4 m/s(iotop). 32g table took me 2 hours , 30 minutes.

2)second method - copy (select * oracle_remote_table) /tmp/dump generates total disk write of 4 m/s , actuval disk write of 100 k/s. copy utility suppose fast seems slow.

-when run copy local dump, reading fast 300 m/s.

-i created 32g file on oracle server , used scp copy , took me few minutes.

-the wals directory located on different file system. parameters 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 increase writes ? how can data faster oracle database postgresql database?

i run perf on entire process , results : enter image description here

i'd concentrate on bits said fast.

-i created 32g file on oracle server , used scp copy , took me few minutes.

-when run copy local dump, reading fast 300 m/s.

i'd suggest combine these two. use dump tool (or sqlplus) export data oracle file postgresql's copy command can read. generate binary file format directly bit tricky, generating csv separated version etc, shouldn't tricky. example @ how spool csv formatted file using sqlplus?


Comments

Post a Comment

Popular posts from this blog

ubuntu - PHP script to find files of certain extensions in a directory, returns populated array when run in browser, but empty array when run from terminal -

i running os ububtu 16.04. ultimate goal able run php script cron job every minute. here php script located @ /var/www/html/tests/test/index.php : <?php $directorypath = $_server['document_root']."/tests/test/data/"; echo "directorypath: $directorypath<br><br>";//check $imagefilepathsarray = glob($directorypath . "*.{png,gif}", glob_brace); echo "<br><br>imagefilepathsarray: "; print_r($imagefilepathsarray);//check ?> when open php script in browser visiting url my-ip-address/tests/test/index.php , get: directorypath: /var/www/html/tests/test/data/ imagefilepathsarray:array( [0] => /var/www/html/tests/test/data/pic.png [1] => /var/www/html/tests/test/data/pic.gif ) before writing cron job in crontab file, need check if can run anywhere using terminal . opened ubuntu terminal @ /home/ location, , executed in terminal: sudo php /var/www/html/tests/test/index.php i got following out...
Read more

php - How can i create a user dashboard -

today have questions you, want create user dashboard site, include (obviously), users data, example -email -username -and others stuff how can information, , print them in html page? if can, how can these information via php sessions? shuld use session? example, if user login, , start new session ( session_start() ), how can data print in dashboard? should use php "project"? in advice, have day , thank time!
Read more

javascript - How to detect toggling of the fullscreen-toolbar in jQuery Mobile? -

i have jquery mobile page fixed full-screen toolbar, data-tap-toggle enabled. straight under toolbar have positioned banner should slide upwards when toolbar hiding , slide downward when toolbar showing. jquery mobile toggles toolbar applying , removing ui-fixed-hidden class - sadly, can't find in documentation of toolbar widget toggle , hide or show event that. how can detect when toolbar toggled, reposition banner? .banner { position: fixed; background-color: darkseagreen; top: 46px; min-height: 48px; width: 100%; text-align: center; line-height: 48px; } <!doctype html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"> <script src="https://code.jquery.com/jquery-1....
Read more