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