Re: performance problem on big tables

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(dot)cherkassky(at)gmail(dot)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(dot)cherkassky(at)gmail(dot)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 <klaussfreire(at)gmail(dot)com>:
>>
>>> On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
>>> <mariel(dot)cherkassky(at)gmail(dot)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.
>>>
>>
>>