When I run copy from local table the speed of the writing is 22 M/S. When I use the copy from remote_oracle_Table it writes 3 M/s. SCP between the servers coppies very fast. How should I continue ?
2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <mariel.cherkas...@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.cherkas...@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 <klaussfre...@gmail.com>: >> >>> On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky >>> <mariel.cherkas...@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. >>> >> >>
