public inbox for [email protected]
help / color / mirror / Atom feedFrom: Claudio Freire <[email protected]>
To: Mariel Cherkassky <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: performance problem on big tables
Date: Thu, 24 Aug 2017 13:14:05 -0300
Message-ID: <CAGTBQpb_V9UymUr0ZARY2y0P_s+7DozKoTUK-pok==o5vx4XsA@mail.gmail.com> (raw)
In-Reply-To: <CA+t6e1kUotu9EWU7qRe2DsrrvLzH-CzaRbKqPUnp-3J=XqUxyQ@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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 );
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: <CAGTBQpb_V9UymUr0ZARY2y0P_s+7DozKoTUK-pok==o5vx4XsA@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