[jira] [Commented] (SQOOP-2983) OraOop export has degraded performance with wide tables

["Attila Szabo (JIRA)" <ji...@apache.org>]

    [ 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 be.

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
(v6.3.4#6332)