sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Attila Szabo (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (SQOOP-2983) OraOop export has degraded performance with wide tables
Date Mon, 18 Jul 2016 19:15:20 GMT

    [ https://issues.apache.org/jira/browse/SQOOP-2983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15382877#comment-15382877

Attila Szabo commented on SQOOP-2983:

Hi [~jarcec], [~kathleen], [~david.robson],

Let me share my investigations and results with you in a "long story short" mode. If you find
my findings and my fix appropriate please help me to get this patch committed as soon as possible.
Thanks in advance!

So the story:

After a quite long testing and experimenting phase the following conclusions had been found:
- Using the direct path write (/*+APPEND_VALUES*/) seems to be a good idea, as when I've applied
it on the top of the standard ExportBatchOutputFormat and used the same "-Dsqoop.export.records.per.statement=5000
-Dsqoop.export.statements.per.transaction=1" session constraints the performance went above
5mb/sec, so the original idea is valid.
- According Oracle's documentation NOLOGGING feature works only properly when the session
is writing on the direct path, so it's been clear OraOOP should be fixed, and we should not
introduce a HINT parameter for the standard Oracle driver (although it could make sense to
introduce that in a different FR JIRA)
- Thus I've started to dig around what could be that different in the OraOOP query handling
and the standard Oracle driver. I was able to measure out that creating the prepared statements
are much slower in case of OraOOP. Executing further experiments I've found that something
should be wrong around configuringPreparedStatement. Here some problems was found (e.g. the
lookup of the column names are linear so could perform badly as wider the table gets), but
the problem still felt more fundamental. So finally I was able to figure out the problem is
with how we set/bind the values through JDBC with the help of the SqoopRecord. When I've applied
the same way how we did it in the ExportBatchOutputFormat the performance get instnatly better
(got up to 8-10 mb/sec).
- However there was still not too relevant difference between the partitioned version and
the non partitioned one (although it seemed to be trivial there should be, as in case of non
partitioned because of the direct write after a while the synchronous writes should concurrent/lock
out each one in a way the wait times should undermine the further parallelisation), and in
some cases (as I've raised the level of parallelisation) it become even much slower (got down
to 5mb/sec only in case of 3M lines/4.5gb/data with 10 mappers). and it was still wired for
me. So in the log files finally I've found the current way how we moved the tables->subpartitions
was very expensive, and sometimes took nearly more time than copying the data to the temp
table itself. Thus I've made some investigations and according to the Oracle documentation,
as soon as I've applied the "WITHOUT VALIDATION" clause on the ALTER statement it's just started
to work as it is intended.

Now in the current state it works like that I can even kill (==20+ load avarage) my local
DB with a 10 node cluster 20mappers, so finally the RDBMS become the bottleneck as it should

I kindly ask you to review my proposed changes and share your thoughts with me!

> OraOop export has degraded performance with wide tables
> -------------------------------------------------------
>                 Key: SQOOP-2983
>                 URL: https://issues.apache.org/jira/browse/SQOOP-2983
>             Project: Sqoop
>          Issue Type: Bug
>            Reporter: Attila Szabo
>            Assignee: Attila Szabo
>            Priority: Critical
> The current version of OraOOP seems to perform very low from performance POV when --direct
mode turned on (regardless if the partitioned feature is turned of).
> Just as a baseline from the current trunk version:
> Inserting 100.000 rows into a 800 column wide Oracle table has 400-600 kb/sec with direct
mode on my cluster, while the standard oracle driver can produce up to 1.2-1.8 mb/sec. (depending
on the number of mappers, batch size).
> Inserting 1.000.000 rows into the same table goes up to 800k-1mb/sec with OraOOP, however
with the standard Oracle connector it's around 3.5mb/sec.
> It seems OraOOP export needs a thorough review and some fixing.

This message was sent by Atlassian JIRA

View raw message