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.cherkas...@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.batal...@gmail.com <mailto:daniel.blanch.batal...@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.cherkas...@gmail.com >> <mailto:mariel.cherkas...@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.marl...@gmail.com >> <mailto:scott.marl...@gmail.com>>: >> On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky >> <mariel.cherkas...@gmail.com <mailto:mariel.cherkas...@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. >> > >
