I am trying to work with Apache spark with data source MySQL. I have a cluster having 1 master and 1 slave node and both have 8 GB ram and 2 cores I am submitting my SQL query to spark using spark-shell and that table having 6402821 this many rows. I am performing a group by onto that table. and time taken by MySQL is 5.2secs and using spark when I am performing query the time is 21Secs. why is this happening?
i am also setting some configurations like partitionColumn, upperBound, lowerBound, and numofPartitions but still no change.
I have also tried with executing the query using 1,2,4 cores but the time taken by the spark is same 21Secs.
is this problem occurs because of my MySQL database is on a single machine and all spark nodes try to query on data onto that single machine?
Can any one help me to solve this issue?
the database having a table called demo_call_stats on which i am trying to query is:
val jdbcDF = spark.read.format("jdbc").options( Map("url" -> "jdbc:mysql://192.168.0.31:3306/cmanalytics?user=root&password=","zeroDateTimeBehaviour"->"convertToNull", "dbtable" -> "cmanalytics.demo_call_stats", "fetchSize" -> "10000", "partitionColumn" -> "newpartition", "lowerBound" -> "0", "upperBound" -> "4", "numPartitions" -> "4")).load()
jdbcDF.createOrReplaceTempView("call_stats")
val sqlDF = sql("select Count(*), classification_id from call_stats where campaign_id = 77 group by classification_id")
sqlDF.show()
Any help will be most appreciated.
Thanks