Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dkmv8-00076s-NU for pgsql-performance@arkaria.postgresql.org; Thu, 24 Aug 2017 07:51:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dkmv8-0002aC-3x for pgsql-performance@arkaria.postgresql.org; Thu, 24 Aug 2017 07:51:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dkmv7-0002ZU-6k for pgsql-performance@postgresql.org; Thu, 24 Aug 2017 07:51:17 +0000 Received: from mail-wm0-x230.google.com ([2a00:1450:400c:c09::230]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dkmv3-00047V-Cu for pgsql-performance@postgresql.org; Thu, 24 Aug 2017 07:51:16 +0000 Received: by mail-wm0-x230.google.com with SMTP id b189so14646017wmd.0 for ; Thu, 24 Aug 2017 00:51: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=cdySeitxmIrdRkZbHRyIL4fRIFeUB+50u59F2wXuTIM=; b=j+I6yTgn9KiMDNiD0qe4rA7rZJ1Ew89HsKKeGtziQWs+E7a+IX7N4fRRcMC7+Ynllx f4eCJpsBal4sMSyt7cnqcUB0Ip46D1srsCU6vN04tY5MxW84Os4p0AV8b3RLJhh2Q6ac PXTGmFWqykMioyRN3SvZeGRKsi3WWAKhdqyJOPG29VrydscCkUtBLFUemaa6cJWktVHK hdZPHXxaJHQ+h7ywX216JcBeYF1GYPIAiX/F8dG78IdVrgi9ZtYrwXf2t6FA4zXyBAuC bSgx/RMxOVSrv6Ln7FCBOzwNkGQ/RkTENhHNNRxshR7J2DasmeBPAsgcxeYrxwIZWjMf W8+w== 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=cdySeitxmIrdRkZbHRyIL4fRIFeUB+50u59F2wXuTIM=; b=YbRq7X4WvhAAtl31+clSaa7eInLDeEXy/7r0Weuam9DypcIi2Z4+sGFXoG3s7mevBm upiD2XCke/PR9JQzas45TsnN5UP6+5GWUI6WSjLSw1Zlq1MKLfi6pSKZYjq6eS+/R8Ac UfPT//f49Jnnu1YfVWfEBWc6zOcMc9HilTX3MtuXrQUJaT5CUG6+FaplRQJoLAJWME5L 2RX1DkZNNfSyqAAM7v9Eqpr8eo4HKeFsS6b/5RgXRADvAAl7Nmh81I36KJeDZxlydrz6 xqhIg439nZBNM2gbdmnfiUk00lxZQZhfzHQjtS0CBeJK0eBl/2OeAeujWnCl0SRx0UYU piiQ== X-Gm-Message-State: AHYfb5hsLgqPpSlL8xZjqNr2WBiVsv6tWvyPBnXzlyOfdC0h6MdvkedH pFt/B+vQ5qltMCBWJTC0+R+NEs9tww== X-Received: by 10.28.54.169 with SMTP id y41mr3268535wmh.30.1503561072129; Thu, 24 Aug 2017 00:51:12 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Thu, 24 Aug 2017 00:51:11 -0700 (PDT) In-Reply-To: References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> From: Mariel Cherkassky Date: Thu, 24 Aug 2017 10:51:11 +0300 Message-ID: Subject: Re: performance problem on big tables To: Claudio Freire Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a114315549a3f6c05577b1794" 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 --001a114315549a3f6c05577b1794 Content-Type: text/plain; charset="UTF-8" Hi Claudio, how can I do that ? Can you explain me what is this option ? 2017-08-24 2:15 GMT+03:00 Claudio Freire : > On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky > wrote: > > To summarize, I still have performance problems. My current situation : > > > > I'm trying to copy the data of many tables in the oracle database into my > > postgresql tables. I'm doing so by running insert into > local_postgresql_temp > > select * from remote_oracle_table. The performance of this operation are > > very slow and I tried to check the reason for that and mybe choose a > > different alternative. > > > > 1)First method - Insert into local_postgresql_table select * from > > remote_oracle_table this generated total disk write of 7 M/s and actual > disk > > write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 minutes. > > > > 2)second method - copy (select * from oracle_remote_table) to /tmp/dump > > generates total disk write of 4 M/s and actuval disk write of 100 K/s. > The > > copy utility suppose to be very fast but it seems very slow. > > Have you tried increasing the prefetch option in the remote table? > > If you left it in its default, latency could be hurting your ability > to saturate the network. > --001a114315549a3f6c05577b1794 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Claudio, how can I do that ? Can you e= xplain me what is this option ?

<= div class=3D"gmail_quote">
2017-08-24 2:15 GMT+03:00 Claudi= o Freire <klaussfreire@gmail.com>:
On Mon, Aug 21, 2017 at 5:00 AM, Mariel Ch= erkassky
<mariel.cherkassky@gmail.= com> wrote:
> To summarize, I still have performance problems. My current situation = :
>
> I'm trying to copy the data of many tables in the oracle database = into my
> postgresql tables. I'm doing so by running insert into local_postg= resql_temp
> select * from remote_oracle_table. The performance of this operation a= re
> very slow and I tried to check the reason for that and mybe choose a > different alternative.
>
> 1)First method - Insert into local_postgresql_table select * from
> remote_oracle_table this generated total disk write of 7 M/s and actua= l disk
> write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 minutes= .
>
> 2)second method - copy (select * from oracle_remote_table) to /tmp/dum= p
> generates total disk write of 4 M/s and actuval disk write of 100 K/s.= The
> copy utility suppose to be very fast but it seems very slow.

Have you tried increasing the prefetch option in the remote table?
If you left it in its default, latency could be hurting your ability
to saturate the network.

--001a114315549a3f6c05577b1794--