Using Apache Spark for fast computation process but writing into Database consumes huge amount of time

Question

I am using the Apache spark for loading the dataset from the Oracle Database using the jdbc format in Spark-Java.I am loading a dataset of the size 10M and 1M and performing various operations on the dataset.After all the processing is done,I am writing multiple datasets into DB,which consumes almost 90% of the time. So,how to reduce this write speed into the Oracle database. I tried various combinations of the executors-memory,cores,number of executors,but not a major difference obtained. Command used to run the apache spark built jar file is:

./spark-submit --class com.sample.Transformation --conf spark.sql.shuffle.partitions=5001 --num-executors=22 --executor-cores=8 --executor-memory=10GB --jars /scratch/rmbbuild/spark_ormb/drools-jars/ojdbc6.jar,/scratch/rmbbuild/spark_ormb/drools-jars/kie-api-7.7.0.Final.jar,/scratch/rmbbuild/spark_ormb/drools-jars/drools-core-7.7.0.Final.jar,/scratch/rmbbuild/spark_ormb/drools-jars/drools-compiler-7.7.0.Final.jar,/scratch/rmbbuild/spark_ormb/drools-jars/kie-soup-maven-support-7.7.0.Final.jar,/scratch/rmbbuild/spark_ormb/drools-jars/kie-internal-7.7.0.Final.jar,/scratch/rmbbuild/spark_ormb/drools-jars/xstream-1.4.10.jar,/scratch/rmbbuild/spark_ormb/drools-jars/kie-soup-commons-7.7.0.Final.jar,/scratch/rmbbuild/spark_ormb/drools-jars/ecj-4.4.2.jar,/scratch/rmbbuild/spark_ormb/drools-jars/mvel2-2.4.0.Final.jar,/scratch/rmbbuild/spark_ormb/drools-jars/kie-soup-project-datamodel-commons-7.7.0.Final.jar,/scratch/rmbbuild/spark_ormb/drools-jars/kie-soup-project-datamodel-api-7.7.0.Final.jar --driver-class-path /scratch/rmbbuild/spark_ormb/drools-jars/ojdbc6.jar --master spark://10.180.182.218:7077 "/scratch/rmbbuild/spark_ormb/POC-jar/Transformation-0.0.1-SNAPSHOT.jar" > /scratch/rmbbuild/spark_ormb/POC-jar/logs/logs10.txt

Write methods in Transformation.java file is as follows:

txndetailDS.write().mode("append").format("jdbc").option("url", connection).option("dbtable", CI_TXN_DETAIL).save();

    finalpriceItemParamsGrpDS.distinct().write().mode("append").format("jdbc").option("url", connection).option("dbtable", CI_PRICEITEM_PARM_GRP_K).save();

    finalpriceItemParamsGroupTable.distinct().write().mode("append").format("jdbc").option("url", connection).option("dbtable", CI_PRICEITEM_PARM_GRP).save();

    finalPritmOutput.distinct().write().mode("append").format("jdbc").option("url", connection).option("dbtable", CI_TXN_DTL_PRITM).save();
            outputDataSetforsummary.write().mode("append").format("jdbc").option("url", connection).option("dbtable", CI_TXN_DTL_PRITM_SUMMARY).save();

So,if anyone have faced this issue can help me out?

Or how to get the optimized performance tuning for spark-submit as well for this scenarios,so that the write into the Database gets speeded up...

Mansoor Baba Shaik · Accepted Answer · 2018-08-23 04:40:27Z

1

Try to reconfigure your total number of executors to not exceed available memory, use only one core per executor this way you can achieve better performance and parallelism.

--num-executors=40 --executor-cores=1 --executor-memory=5G

Keep in mind that JDBC connection is single threaded per executor and it makes to use just one core for that reason. With above configuration 40 parallel connections would be established to the RDBMS.

Also, I suggest using .option("batchsize", "10000") so that insertion would be faster that is 10000 records at once rather than default 1000.

answered Aug 23, 2018 at 4:40

Mansoor Baba Shaik

4922 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ben10 Over a year ago

What about this parameter --conf spark.sql.shuffle.partitions=5001 ?

ben10 Over a year ago

Hii,I ran the same command with the changes as --conf spark.sql.shuffle.partitions=5001 --num-executors=40 --executor-cores=1 --executor-memory=5G & batchsize to 100000 .But,still it takes time for I/O operation with Database.

Pawan B · Accepted Answer · 2018-08-23 07:19:57Z

0

Set the partition with the spark-submit:

spark.sql.shuffle.partitions=5000
conf spark.default.parallelism=5000

The partition should be in multiple of no of cores (8*625 = 5000)

If you don't have large data then don't have large number of partitions.

You can check more on optimizing job here

You can set these properties also:

batchsize: The JDBC batch size, which determines how many rows to insert per round trip. This can help performance on JDBC drivers. This option applies only to writing. It defaults to 1000.

numPartitions:The maximum number of partitions that can be used for parallelism in table reading and writing. This also determines the maximum number of concurrent JDBC connections.

answered Aug 23, 2018 at 7:19

Pawan B

4,6332 gold badges27 silver badges39 bronze badges

Collectives™ on Stack Overflow

Using Apache Spark for fast computation process but writing into Database consumes huge amount of time

Write methods in Transformation.java file is as follows:

So,if anyone have faced this issue can help me out?

Or how to get the optimized performance tuning for spark-submit as well for this scenarios,so that the write into the Database gets speeded up...

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Write methods in Transformation.java file is as follows:

So,if anyone have faced this issue can help me out?

Or how to get the optimized performance tuning for spark-submit as well for this scenarios,so that the write into the Database gets speeded up...

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related