Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djOUZ-0005xM-Gr for pgsql-performance@arkaria.postgresql.org; Sun, 20 Aug 2017 11:34:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1djOUX-0003jW-Oj for pgsql-performance@arkaria.postgresql.org; Sun, 20 Aug 2017 11:34:05 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1djOSm-0000ea-Fm for pgsql-performance@postgresql.org; Sun, 20 Aug 2017 11:32:16 +0000 Received: from mail-wr0-x230.google.com ([2a00:1450:400c:c0c::230]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djOSi-0005v4-J7 for pgsql-performance@postgresql.org; Sun, 20 Aug 2017 11:32:14 +0000 Received: by mail-wr0-x230.google.com with SMTP id f8so40448360wrf.3 for ; Sun, 20 Aug 2017 04:32:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=ZTVgLLfrTphaynP0SCWb0IdEMJMKSvgp2vRsuskyAJA=; b=Qw7NaN7wYXfVLNlFQZmQmhjs1qoQBnWZ2BwTcmF+AsCx7TWg6aP4ldOboIyTBAbae2 I7ACSGRn/VMuFSUOg9topgH6bdhznrjidgCwGrrjOlsv1pU2a8zzDn/GuQPNuyyvRHU8 mHQA4IBGtJoRNq9YyUND60C5+xxmunTPeoMnSqu57WsOJHBk4nprAHCWoJ9UJ9+T6cKs Sr9J6k0tWstttHKo6shoQGfYmWDooF5CbYQGV1Sx9NrRsKI7KsBqiRFxqyC5cJoKsi/L P22MHmx7i94JOpjjTjp9JAL2eSKM7WYIFWPDK+sFUwjKBqKi/nHbgtPvRvrHPLxoV4qw JoZw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=ZTVgLLfrTphaynP0SCWb0IdEMJMKSvgp2vRsuskyAJA=; b=lLiaOe92AymHGJxSelUoIu/ISnIUnyd1DOXA1/HiUD+rcPdRnzzQLheeXEwWxCfg1B PxyESk4U3XWgqbK2SntQl0S8Sx4ka2ZICwmx4XGhGkUj5xY+RO3Snzv20TBm7UBdhYLY 6Egi+dXE692MFIFgoJfKDeGJ7uETCAgWIEy3oT/wZS71fSLp7Ame7T2xGJYTjG+H3L1t neX3F2W7FLfHXemrdpXhaDP7rqnwtvxQul55s3thZfMyZkxe3uJzNVUj/HWIecBwUBIc Sdl4aULJaAkuch4eYWWI6cuk85YwxRyfCI0o0ZBmlnc636K0k9nzWV2iEAN+z+Yv/e+h dQFQ== X-Gm-Message-State: AHYfb5iuQqon7SokC9vPJkFpJpnvCa/kaP6G7oRPJCwf2rnXnQXlFzs4 15Y4eIISZiEAeVHdGY/e6WrOzR6Qlg== X-Received: by 10.28.236.208 with SMTP id h77mr121871wmi.162.1503228730562; Sun, 20 Aug 2017 04:32:10 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Sun, 20 Aug 2017 04:32:09 -0700 (PDT) In-Reply-To: References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> From: Mariel Cherkassky Date: Sun, 20 Aug 2017 14:32:09 +0300 Message-ID: Subject: Re: performance problem on big tables To: Claudio Freire Cc: Daniel Blanch Bataller , Scott Marlowe , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a11476f18805e9e05572db669" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11476f18805e9e05572db669 Content-Type: text/plain; charset="UTF-8" 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 : > 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 : > >> 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 : >> >>> On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky >>> 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. >>> >> >> --001a11476f18805e9e05572db669 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
When I run copy from local table the spee= d 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 cont= inue ?

2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <= mariel.cherkassky@gmail.com>:
I realized something weird. When I`m preforming the copy utility of pos= tgresql in order to create dump from a local table in my postgresql db it t= akes 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 se= e that its writes 3 M/s. What else I can check ?=C2=A0
=
2017-08-20 9:39 GMT+03:00 Mariel Cherkassky = <mariel.cherkassky@gmail.com>:
<= div dir=3D"ltr">This server is dedicated to be a postgresql production data= base, 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 considere= d little ? I installed iotop and I see that postgresql writer is writing mo= st of the time and above all.

I mentioned that I perform alot of insert into table select * from tabl= e. Before that I remove indexes,constraints and truncate the table. Should = I run vacuum before or after the operation ?=C2=A0

2017-08-= 17 19:37 GMT+03:00 Claudio Freire <klaussfreire@gmail.com>:
On Thu, Aug 17, 2017 at 6:00= AM, Mariel Cherkassky
<mariel= .cherkassky@gmail.com> wrote:
> I checked with the storage team in the company and they saw that I hav= e alot
> of io on the server. How should I reduce the io that the postgresql us= es ?

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.

--001a11476f18805e9e05572db669--