public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mariel Cherkassky <[email protected]>
To: Claudio Freire <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: performance problem on big tables
Date: Sun, 27 Aug 2017 19:34:45 +0300
Message-ID: <CA+t6e1=0+ByGppTvqYQ70oXTzsdpiC3753PPLhWkpt5m5Jfa8A@mail.gmail.com> (raw)
In-Reply-To: <CAGTBQpb_V9UymUr0ZARY2y0P_s+7DozKoTUK-pok==o5vx4XsA@mail.gmail.com>
References: <CA+t6e1m06=RkReCDxdyjwAMwVwuKxakV8TKmQ2cnMLFYbOjMCA@mail.gmail.com>
<CA+t6e1nG8bF4-hrvjijhJ_nC5OXmw32eXNYdxuYMkPSk2QLrag@mail.gmail.com>
<CAOR=d=1JE-GT6iv__uHWQ9DcPcGS5LBTq_gpiKrvjw3moDHuZg@mail.gmail.com>
<CA+t6e1k7MOf0vgs2t=tWZ-tJ3MGmTqq1bkcQseRCZ9n+u-QBkQ@mail.gmail.com>
<[email protected]>
<CA+t6e1nvx3KfKP_QAKQ+WvzJF5k3h5Uq+F674bVk5PjWv2enrw@mail.gmail.com>
<[email protected]>
<CA+t6e1nXJz34ECUfJAkq7JQVkzr2mg-cbsW_acBXcyM2eyBKBw@mail.gmail.com>
<[email protected]>
<CA+t6e1kXc1=z-4yAB+jBXpLNVCvTO2d2W69Re-N4YrckdXoDvg@mail.gmail.com>
<CA+t6e1kijisMi3cjNy3HEEvDMMH_u9WkzS3ViQGZ9cPTZ3YtWQ@mail.gmail.com>
<CAGTBQpaXBW_edddHy649yMVL81zDo=rY8MP1ZxQDxnUZ=9hGaw@mail.gmail.com>
<CA+t6e1nB58DtiMmNeY_Nh+wjRyT_WOFFxNJRJK=M30d4HAWJnQ@mail.gmail.com>
<CA+t6e1n6nqwyh1Jb1WTU6kYr9kgkdZe+GABY9XgDozixoS08DA@mail.gmail.com>
<CA+t6e1=+aig4WoZRG+_qpRG+zs_U--v42dSznT4B55Y=xh9Lhw@mail.gmail.com>
<CAGTBQpZ8mMxXm0U2e=pnd=30j-r_RP+EKs9XF6wJ4FX=-62MPQ@mail.gmail.com>
<CA+t6e1kUotu9EWU7qRe2DsrrvLzH-CzaRbKqPUnp-3J=XqUxyQ@mail.gmail.com>
<CAGTBQpb_V9UymUr0ZARY2y0P_s+7DozKoTUK-pok==o5vx4XsA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 <[email protected]>:
> On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky
> <[email protected]> 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 <[email protected]>:
> >>
> >> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
> >> <[email protected]> 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 );
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: performance problem on big tables
In-Reply-To: <CA+t6e1=0+ByGppTvqYQ70oXTzsdpiC3753PPLhWkpt5m5Jfa8A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox