Optimizing OLAP Data Source Integration with SparkSQL: Cluster and Node Tuning, Profiling, and GC

The article documents the integration of an OLAP data source into SparkSQL and shares a series of performance‑tuning experiences, inviting readers with more expertise to provide feedback.

Optimization is approached from two angles: cluster‑level tuning (CPU and memory allocation, data distribution, shuffle handling) and single‑node tuning, which follows Brendan D. Gregg’s classification of performance issues into On‑CPU and Off‑CPU.

Cluster‑level checklist :

CPU and memory resource allocation

Data locality

Shuffle configuration

Data format, cache level, serialization, compression

Parallelism and straggler detection

Using Spark History Server’s Web UI, the author observes that most execution time is spent in the executor’s computation phase. A quoted observation about HDFS client concurrency leads to the recommendation of limiting executor cores to five, e.g., spark.executor.cores=5, resulting in a 30% performance gain.

Single‑node (On‑CPU) optimization relies on sampling tools to capture hot call stacks. The author prefers flame‑graphs for visualizing hotspots and demonstrates how to generate mixed C++/Java flame‑graphs with perf and perf‑map‑agent:

$ jps | grep CoarseGrainedExecutorBackend | awk 'NF==2 && NR==1 {print $1}' | perf record -F 99 -p `xargs` -a -g -- sleep 60

After generating the perf script, the flame‑graph is produced:

$ perf script -f comm,pid,tid,cpu,event,sym,trace | ./stackcollapse-perf.pl --pid | ./flamegraph.pl --color=java --hash > executor-flame.svg

The resulting graph shows roughly equal CPU usage by GC threads, JIT compilation threads, and the Java main thread. The author notes limitations of perf‑map‑agent for interpreted bytecode and suggests using Java Flight Recorder (JFR) instead.

Enabling JFR in Spark executors is as simple as adding extra JVM options:

spark.executor.extraJavaOptions    -XX:+UnlockCommercialFeatures -XX:+FlightRecorder -XX:StartFlightRecording=filename=executor.jfr,dumponexit=true,settings=profile

After execution, the executor.jfr file can be converted to a flame‑graph with jfr‑flame‑graph:

$ ./flamegraph-output.sh folded -f executor.jfr -o executor.txt
$ cat executor.txt | ./flamegraph.pl > executor-flame-java.svg

The analysis reveals two major CPU hotspots: HDFS document retrieval and SparkSQL aggregation (generated by CodeGen). The author refactors the aggregation code to avoid costly JavaConverters and excessive toString calls, reducing the hotspots.

Off‑CPU analysis uses JFR events captured automatically. The author opens the .jfr file with Java Mission Control (JMC) to inspect I/O wait, thread park, and monitor contention events, noting that many waits are caused by HDFS latency and file‑read operations.

To capture fine‑grained I/O events, the JFR profile is edited to lower the threshold for java/file_read and java/file_write from 10 ms to 10 µs:

<event path="java/file_read">
  <setting name="enabled">true</setting>
  <setting name="stackTrace">true</setting>
  <setting name="threshold">10 us</setting>
</event>

<event path="java/file_write">
  <setting name="enabled">true</setting>
  <setting name="stackTrace">true</setting>
  <setting name="threshold">10 us</setting>
</event>

Analysis shows thousands of file‑read calls (each < 1 MB) accumulating over 6 seconds, suggesting a possible optimization by increasing read buffer size.

Garbage‑collection tuning starts with selecting the appropriate collector. For a throughput‑oriented short‑lived Spark job, Parallel GC is chosen. The author sets -XX:ParallelGCThreads=5 to match executor cores and disables the adaptive size policy ( -XX:-UseAdaptiveSizePolicy) to prevent ergonomics‑triggered Full GCs.

Further tuning includes fixing the initial heap size ( -Xms8G) to avoid heap growth‑induced Full GCs, adjusting -XX:NewRatio=1 to reduce Minor GCs, and examining memory allocation patterns via JMC. The analysis identifies large byte[] allocations (up to 1 GB) in the HDFS client, which are reduced to 200 MB, eliminating an additional Minor GC.

Overall, the article provides a practical checklist and concrete command‑line examples for diagnosing and improving SparkSQL performance on OLAP workloads.