Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dm0WW-0002Ya-Id for pgsql-performance@arkaria.postgresql.org; Sun, 27 Aug 2017 16:34:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dm0WV-0008Qy-6G for pgsql-performance@arkaria.postgresql.org; Sun, 27 Aug 2017 16:34:55 +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 1dm0WT-0008Qj-CN for pgsql-performance@postgresql.org; Sun, 27 Aug 2017 16:34:53 +0000 Received: from mail-wr0-x22a.google.com ([2a00:1450:400c:c0c::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dm0WO-0006MH-Gp for pgsql-performance@postgresql.org; Sun, 27 Aug 2017 16:34:51 +0000 Received: by mail-wr0-x22a.google.com with SMTP id p8so12202774wrf.2 for ; Sun, 27 Aug 2017 09:34:48 -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=jcn4X90i2hHZZjTryJClEViBXXdvk3NvhR4qOC8tLLA=; b=dpd7rQFtT9Iz+8pdiQfD36n42OtBdSwwrMfheWgum/jBERhqD3OilHnJwBO9p7osjy y7rpRaxr1golKxlR+wtrNlA6bnUVKy9YcalYbkvgDUDHNRbSoZnHKS1R0FfT7g33h8ke n7tJ56CEUfZYybvb+YThsJLaZpF2VZsaGpCn+auRsxFB8ZCfhLyvRiLOirMjei8LxV7G RLFrQjToWQc7J40OPzL1+9zG7ao2qewb1kCdYb0HaNhqePWxXRzecTHdDasiCbEmfaZQ cRB+KgaFU7koA/Qz9gFG1QHAjaA8JuLMGj/M+OH1/X5dLdwKdajTRw/0/5Dz1wnn+zk1 hjVA== 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=jcn4X90i2hHZZjTryJClEViBXXdvk3NvhR4qOC8tLLA=; b=sqDm96xTXxd5VZK5kyyKITmuxW51vYIhlJ3dCQx5MXjMk5wN8fQYgEdVAgQXrGIO/F TDcZDtRHsqdA63tvYIRBQeCVp8ZK2QG8Z0ph7a4wMsYC1gU/4yk7MU0d5VlvavkbjSoi hj/6ORzcuJE6WutaNTflCvZ9Vk8IyCkIgemxGNUCZAvjS0bkKQNdbp8pUUMzg6m+WC1s a+1b+WHLLNXtJDcsIJY+Yl20Iitv3eMgwPWmUvVYAAUCeIppk7c7MaOLOds0c5uAeZ2u Au/Oe/MTigl6MYH+tnmNeHIGPe7Ra1ckLwQftDLJ1fOwbMxGErUUPToC8ayocHkx97wo uLbw== X-Gm-Message-State: AHYfb5i4wjz1WZ70msdxLen0SRahDkHVQqVwf60BRztVOLIztFADNK1A OMTbHrEwkG7nbbUYU/9izHHrh7B6WA== X-Received: by 10.223.184.74 with SMTP id u10mr300090wrf.182.1503851686558; Sun, 27 Aug 2017 09:34:46 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Sun, 27 Aug 2017 09:34:45 -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, 27 Aug 2017 19:34:45 +0300 Message-ID: Subject: Re: performance problem on big tables To: Claudio Freire Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="f403045f519e929b150557bec116" 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 --f403045f519e929b150557bec116 Content-Type: text/plain; charset="UTF-8" Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it but I'm getting error dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 ); ERROR: syntax error at or near "10240" LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 ); dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch '10240'); ERROR: option "prefetch" not found 2017-08-24 19:14 GMT+03:00 Claudio Freire : > On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky > wrote: > > 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. > > > > > > Please don't top-post. > > I'm assuming you're using this: http://laurenz.github.io/oracle_fdw/ > > If you check the docs, you'll see this: > https://github.com/laurenz/oracle_fdw#foreign-table-options > > So I'm guessing you could: > > ALTER FOREIGN TABLE remote_table OPTIONS ( SET prefetch 10240 ); > --f403045f519e929b150557bec116 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi, yes indeed I'm using laurenz`s or= acle_fdw extension. I tried to run it but I'm getting error=C2=A0
=

dbch=3D# ALTER= FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 );
ERROR: =C2=A0syntax error at or near "10240"
=
LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET pref= etch 10240 );


dbch=3D# =C2=A0alter foreign table tc_sub_rate_ver_prod OPTIO= NS (SET prefetch '10240');
ERROR: =C2=A0optio= n "prefetch" not found




2017-08-24 19:14 GMT+03:00 Claudio Frei= re <klaussfreire@gmail.com>:
On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky
<mariel.= cherkassky@gmail.com> wrote:
> 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 <klaussfreire@gmail.com>:
>>
>> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
>> <mariel.cherkass= ky@gmail.com> wrote:
>> > To summarize, I still have performance problems. My current s= ituation :
>> >
>> > 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 op= eration are
>> > very slow and I tried to check the reason for that and mybe c= hoose 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 3= 0 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.<= br> >>
>> Have you tried increasing the prefetch option in the remote table?=
>>
>> If you left it in its default, latency could be hurting your abili= ty
>> to saturate the network.
>
>

Please don't top-post.

I'm assuming you're using this: http://laurenz.github.io= /oracle_fdw/

If you check the docs, you'll see this:
https://github.com/laurenz/oracle_fd= w#foreign-table-options

So I'm guessing you could:

ALTER FOREIGN TABLE remote_table OPTIONS ( SET prefetch 10240 );

--f403045f519e929b150557bec116--