[ 
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)

Reply via email to