Query with spark-sql performacne tuning
This tutorial guides you to create CarbonData Tables and optimize performance. The following sections will elaborate on the below topics :
The influence of codegen configuration on query
In practice, we found that when the number of counters(columns applied SUM operator) reaches a certain threshold, the query time increases dramatically.
As shown in the figure below(spark 2.1):
The horizontal axis is the number of counter, and the vertical axis is the time consumed in seconds.
It can be seen from the figure that when the number of counter exceeds 85, the query time is significantly increased.
After analysis, this problem is related to spark.sql.codegen.wholeStage, which is enabled by default since spark 2.0. and it will do all the internal optimization possible from the spark catalyst side.
Whole-Stage Java Code Generation (aka Whole-Stage CodeGen) is a physical query optimization in Spark SQL that fuses multiple physical operators (as a subtree of plans that support code generation) together into a single Java function.
Whole-Stage Java Code Generation improves the execution performance of a query by collapsing a query tree into a single optimized function that eliminates virtual function calls and leverages CPU registers for intermediate data.
As the number of counter grows, generated codes for data process become larger. There are nearly 3000 lines of code when 34 counters. Java does not recommend huge methods, which can not make use of JIT and the code will run in interpretation mode. So the performance drops sharply when the counter in query grows.
But unfortunately, spark 2.1 only provides switching capability. User can only choose to turn the function on or off. This leads to a sharp drop in performance when the aggregation operator is too large. If the query scenario has many counters, user can configure “spark.sql.codegen.wholeStage” = false. This depends on the query scenario.
Fortunately, spark 2.3 provide more configuration. Users can better configure this parameter.
User can check more configuration here.
Recommended configuration for spark2.3：
| Parameter name | value | describe |
|---|---|---|
| spark.sql.codegen.wholeStage | true | (internal) Whether the whole stage (of multiple physical operators) will be compiled into a single Java method (true) or not (false). Default: true Use SQLConf.wholeStageEnabled method to access the current value. |
| spark.sql.codegen.hugeMethodLimit | 8000 | (internal) The maximum bytecode size of a single compiled Java function generated by whole-stage codegen. Default: 65535 The default value 65535 is the largest bytecode size possible for a valid Java method. When running on HotSpot, it may be preferable to set the value to 8000 (which is the value of HugeMethodLimit in the OpenJDK JVM settings) Use SQLConf.hugeMethodLimit method to access the current value. |