Improving Spark SQL Performance_

Improving Spark SQL Performance_

53850955f15249a1a9dc49df6113e400?s=128

LINE Developers

March 14, 2019
Tweet

Transcript

  1. IMPROVING SPARK SQL PERFORMANCE Keiji Yoshida - LINE Corporation

  2. OASIS

  3. Agenda 1. Issue 2. Gathering Statistics 3. Injecting Custom Optimization

    Rules 4. Cost-based Optimizer
  4. Agenda 1. Issue 2. Gathering Statistics 3. Injecting Custom Optimization

    Rules 4. Cost-based Optimizer
  5. Users / Day 1,300+ Spark Apps / Day 7,000+ Spark

    Jobs / Day 200+ USAGE
  6. SPARK VERSION AND CONFIGURATION • 2.4.0 • spark.sql.cbo.enabled = false

  7. SLOW QUERIES Rank Query Count Average Time (min) Total Time

    (min) 1 Query A 335 20 6,842 2 Query B 28 85 2,380 3 Query C 109 20 2,182 4 Query D 4028 0 1,543 5 Query E 14 105 1,480
  8. IMPACTS • Speed of data analysis and decision making •

    Usage efficiency of computing resources
  9. CHALLENGES FOR END USERS • Acquiring knowledge and skills of

    data engineering • Taking care of queries’ performances • Changing queries and tables in the production environment
  10. SOLUTION • Improving queries’ performances on the platform side

  11. Agenda 1. Issue 2. Gathering Statistics 3. Injecting Custom Optimization

    Rules 4. Cost-based Optimizer
  12. GATHERING STATISTICS • 1,500 sec → 60 sec

  13. QUERY select t.dt, m.name, count(1) from ( select dt, key

    from db.transaction where dt between '20190211' and '20190217' ) t inner join db.master m on m.key = t.key group by t.dt, m.name
  14. QUERY select t.dt, m.name, count(1) from ( select dt, key

    from db.transaction where dt between '20190211' and '20190217' ) t inner join db.master m on m.key = t.key group by t.dt, m.name 1,800,000,000 records 200 records
  15. PHYSICAL PLAN *(6) HashAggregate(keys=[dt#958, name#970], functions=[count(1)]) +- Exchange hashpartitioning(dt#958, name#970,

    200) +- *(5) HashAggregate(keys=[dt#958, name#970], functions=[partial_count(1)]) +- *(5) Project [dt#958, name#970] +- *(5) SortMergeJoin [key#937], [key#960], Inner :- *(2) Sort [key#937 ASC NULLS FIRST], false, 0 : +- Exchange hashpartitioning(key#937, 200) : +- *(1) Project [dt#958, key#937] : +- *(1) Filter isnotnull(key#937) : +- *(1) FileScan orc transaction[key#937,dt#958] Batched: true, Format: ORC, Location:… +- *(4) Sort [key#960 ASC NULLS FIRST], false, 0 +- Exchange hashpartitioning(key#960, 200) +- *(3) Project [key#960, name#970] +- *(3) Filter isnotnull(key#960) +- *(3) FileScan orc master[key#960,name#970] Batched: true, Format: ORC, Location:…
  16. SORT MERGE JOIN

  17. BROADCAST HASH JOIN

  18. AUTO BROADCASTJOIN THRESHOLD • spark.sql.autoBroadcastJoinThreshold = 10 MB

  19. QUERY select t.dt, m.name, count(1) from ( select dt, key

    from db.transaction where dt between '20190211' and '20190217' ) t inner join db.master m on m.key = t.key group by t.dt, m.name 1,800,000,000 records 200 records
  20. PHYSICAL PLAN *(6) HashAggregate(keys=[dt#958, name#970], functions=[count(1)]) +- Exchange hashpartitioning(dt#958, name#970,

    200) +- *(5) HashAggregate(keys=[dt#958, name#970], functions=[partial_count(1)]) +- *(5) Project [dt#958, name#970] +- *(5) SortMergeJoin [key#937], [key#960], Inner :- *(2) Sort [key#937 ASC NULLS FIRST], false, 0 : +- Exchange hashpartitioning(key#937, 200) : +- *(1) Project [dt#958, key#937] : +- *(1) Filter isnotnull(key#937) : +- *(1) FileScan orc db.transaction[key#937,dt#958] Batched: true, Format: ORC +- *(4) Sort [key#960 ASC NULLS FIRST], false, 0 +- Exchange hashpartitioning(key#960, 200) +- *(3) Project [key#960, name#970] +- *(3) Filter isnotnull(key#960) +- *(3) FileScan orc db.master[key#960,name#970] Batched: true, Format: ORC
  21. STATS val q = "select * from db.master” spark.sql(q).queryExecution.optimizedPlan.stats.sizeInBytes →

    9223372036854775807
  22. OASIS.INSERTOVERWRITE spark.sql(q).repartition(filesNum).write().mode(“overwrite”).insertInto(“db.master”)

  23. OASIS.INSERTOVERWRITE spark.sql(q).repartition(filesNum).write().mode(“overwrite”).insertInto(“db.master”) spark.sql(“analyze table db.master compute statistics noscan”)

  24. STATS

  25. STATS val q = "select * from db.master” spark.sql(q).queryExecution.optimizedPlan.stats.sizeInBytes →

    8239
  26. PHYSICAL PLAN *(3) HashAggregate(keys=[dt#149, name#161], functions=[count(1)]) +- Exchange hashpartitioning(dt#149, name#161,

    200) +- *(2) HashAggregate(keys=[dt#149, name#161], functions=[partial_count(1)]) +- *(2) Project [dt#149, name#161] +- *(2) BroadcastHashJoin [key#128], [key#151], Inner, BuildRight :- *(2) Project [dt#149, key#128] : +- *(2) Filter isnotnull(key#128) : +- *(2) FileScan orc db.transaction[key#128,dt#149] Batched: true, Format:… +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true])) +- *(1) Project [key#151, name#161] +- *(1) Filter isnotnull(key#151) +- *(1) FileScan orc db.master[key#151,name#161] Batched: true, Format:…
  27. RESULT • 1,500 sec → 60 sec

  28. Agenda 1. Issue 2. Gathering Statistics 3. Injecting Custom Optimization

    Rules 4. Cost-based Optimizer
  29. INJECTING CUSTOM OPTIMIZATION RULES • 6,000 sec → 200 sec

  30. QUERY select t.dt, a.area_name, count(distinct uid) from ( select dt,

    area_code, uid from db.transaction where dt between '20180818' and '20190217' ) t inner join (select * from db.area where dt = ‘20190217’) a on a.area_code = t.area_code group by t.dt, a.area_name
  31. QUERY select t.dt, a.area_name, count(distinct uid) from ( select dt,

    area_code, uid from db.transaction where dt between '20180818' and '20190217' ) t inner join (select * from db.area where dt = ‘20190217’) a on a.area_code = t.area_code group by t.dt, a.area_name 4,500,000,000 records 90 records
  32. PHYSICAL PLAN +- *(5) HashAggregate(...) +- *(5) HashAggregate(...) +- *(5)

    HashAggregate(...) +- *(5) Project [...] +- *(5) SortMergeJoin [...], Inner :- *(2) Sort [...], false, 0 : +- Exchange hashpartitioning(...) : +- *(1) Project [...] : +- *(1) Filter (...) : +- *(1) FileScan orc db.transaction[...] Batched: true, Format: ORC +- *(4) Sort [...], false, 0 +- Exchange hashpartitioning(...) +- *(3) Filter (...) +- Scan hive db.area [...], HiveTableRelation
  33. HIVE.STATS.AUTOGATHER

  34. BROADCAST HINT select /*+ broadcast(r) */ * from records r

    join src s on r.key = s.key
  35. CUSTOMIZE OPTIMIZATION spark.experimental.extraOptimizations = MyOptimization :: Nil

  36. CUSTOMIZE OPTIMIZATION object InsertBroadcastHints extends Rule[LogicalPlan] { val targets =

    Map(("db", "area") -> "dt") override def apply(plan: LogicalPlan): LogicalPlan = plan transform { case join: Join if isSelectFromTarget(join.left) => join.copy(left = ResolvedHint(join.left, HintInfo(true))) case join: Join if isSelectFromTarget(join.right) => join.copy(right = ResolvedHint(join.right, HintInfo(true))) } private def isSelectFromTarget(plan: LogicalPlan): Boolean = plan match { case p: Project => p.child match { case f: Filter => f.child match { case rel: HiveTableRelation => targets.get(rel.tableMeta.database, rel.tableMeta.identifier.table) match { case Some(pt) => isFilteredByLiteral(f.condition, pt) case None => false } … } private def isFilteredByLiteral(expr: Expression, pt: String): Boolean = ... } spark.experimental.extraOptimizations = InsertBroadcastHints :: Nil
  37. OPTIMIZED LOGICAL PLAN Aggregate [...] +- Project [...] +- Join

    Inner, (...) :- Project [...] : +- Filter (...) : +- Relation[...] orc +- ResolvedHint (broadcast) +- Project [...] +- Filter (...) +- HiveTableRelation `db`.`area`, ...
  38. PHYSICAL PLAN *(4) HashAggregate(...) +- Exchange hashpartitioning(...) +- *(3) HashAggregate(...)

    +- *(3) HashAggregate(...) +- Exchange hashpartitioning(...) +- *(2) HashAggregate(...) +- *(2) Project [...] +- *(2) BroadcastHashJoin [...], [...], Inner, BuildRight :- *(2) Project [...] : +- *(2) Filter (...) : +- *(2) FileScan orc db.transaction[...] Batched: true, Format: ORC +- BroadcastExchange HashedRelationBroadcastMode(...) +- *(1) Filter (...) +- Scan hive db.area [...], HiveTableRelation
  39. RESULT • 6,000 sec → 200 sec

  40. Agenda 1. Issue 2. Gathering Statistics 3. Injecting Custom Optimization

    Rules 4. Cost-based Optimizer
  41. COST-BASED OPTIMIZATION • Apache Spark 2.2.0~ • Statistics, Cost •

    Advanced Optimizations 1. Broadcast Hash Join Based on Column Statistics 2. Join Reordering
  42. COST-BASED OPTIMIZATION • Apache Spark 2.2.0~ • Statistics, Cost •

    Advanced Optimizations 1. Broadcast Hash Join Based on Column Statistics 2. Join Reordering
  43. QUERY select a.* from db.a inner join db.b on a.id

    = b.id inner join db.c on b.key = c.key
  44. QUERY select a.* from db.a inner join db.b on a.id

    = b.id inner join db.c on b.key = c.key 1,500,000,000 records 400,000,000 records 1 records
  45. JOIN ORDER db.a (1,500,000,000 records) db.b (400,000,000 records) db.c (1

    record)
  46. db.a db.b db.c

  47. CBO • set spark.sql.cbo.enabled = true • set spark.sql.cbo.joinReorder.enabled =

    true
  48. db.b db.c db.a

  49. RESULT • 1,000 sec → 100 sec

  50. COST • cost = weight * numOfRows + (1.0 -

    weight) * dataSize
  51. THANK YOU