https://community.cloudera.com/t5/Support-Questions/Spark-Sql-for-ETL-performance-tuning/m-p/238616#M200427 <P>I am using spark sql cli for performing ETL operations on hive tables.</P><P>There is a sql script query which involves more than 4 joins into different tables along with where conditions in each joins for filtering before inserting it to a new big table.</P><P>The performance of this query is really poor even with really small amount of data in each tables.</P><P>I have tried using various properties for improvising the performance of the query.</P><P><BR /></P><P>SET spark.max.fetch.failure.per.stage = 10;</P><P>SET spark.rpc.io.serverThreads = 64;</P><P>SET spark.memory.fraction = 0.8;</P><P>SET spark.memory.offHeap.enabled = true;</P><P>SET spark.memory.offHeap.size = 3g;</P><P>SET spark.shuffle.file.buffer = 1 MB;</P><P>SET spark.unsafe.sorter.spill.reader.buffer.size = 1 MB;</P><P>SET spark.file.transferTo = false;</P><P>SET spark.shuffle.file.buffer = 1 MB;</P><P>SET spark.shuffle.unsafe.file.output.buffer = 5 MB;</P><P>SET spark.io.compression.lz4.blockSize=512KB;</P><P>SET spark.shuffle.service.index.cache.entries = 2048;</P><P>SET spark.shuffle.io.serverThreads = 128;</P><P>SET spark.shuffle.io.backLog = 8192;</P><P>SET spark.shuffle.registration.timeout = 2m;</P><P>SET spark.shuffle.registration.maxAttempt = 5;</P><P><BR /></P><P>But still the query runs for hours.</P><P>These are the questions that pops out from my mind:</P><OL><LI>Is there any other ways to optimize and troubleshoot the query?</LI><LI>Can we assume that Spark sql is not meant to handle a query with more complex joins?</LI><LI>Should I break the script to multiple scripts with less number of Joins?</LI></OL> Thu, 18 Apr 2019 22:06:40 GMT barath51777 2019-04-18T22:06:40Z