Task 3.5: Understand the basic tuning parameters (spark.sql.shuffle.partitions, spark.default.parallelism, spark.executor/driver.memory, spark.sql.autoBroadcastJoinThreshold) and re-measure the performance.

• Aspark.databricks.adaptive.autoBroadcastJoinThreshold set to 1GB

  Incorrect

  This is the size threshold for switching a sort merge join to a broadcast hash join at run time; it does not choose the number of shuffle partitions.

• Bspark.sql.adaptive.skewJoin.enabled set to true

  Incorrect

  Skew join handling is on by default and splits oversized skewed tasks in shuffle-based joins; it does not set the partition count for the shuffle.

• Cspark.sql.shuffle.partitions set to auto

  Correct

  Setting this property to auto enables auto-optimized shuffle, which sets the number of shuffle partitions from the query plan and the query input data size instead of the fixed default of 200.

• Dspark.databricks.optimizer.adaptive.enabled set to false

  Incorrect

  This turns adaptive query execution off, which removes the run-time re-optimization; it does not size the shuffle from the input.

spark.sql.shuffle.partitions is the default number of partitions used when shuffling data for joins or aggregations, 200 unless changed. On Databricks it also accepts auto, which turns on auto-optimized shuffle and picks the number from the plan and the input size, so one setting fits small and large runs.

• Aspark.sql.shuffle.partitions set to 1

  Incorrect

  This forces every shuffle into a single partition, which makes memory pressure worse and does not stop broadcasts.

• Bspark.executor.memory set to a lower value per executor

  Incorrect

  Less executor memory makes out-of-memory errors more likely; it does not change the join strategy.

• Cspark.sql.autoBroadcastJoinThreshold set to -1

  Correct

  The threshold is the maximum size of a table broadcast in a join; setting it to -1 disables automatic broadcasting.

• Dspark.sql.adaptive.enabled set to false

  Incorrect

  This turns off adaptive query execution, but the static plan still broadcasts a table whose statistics fall under the threshold.

autoBroadcastJoinThreshold, 10 MB by default, controls automatic broadcast joins. A large broadcast is a common cause of memory problems, and -1 turns automatic broadcasts off.

• ASet it in the cluster's Spark config or pick a larger worker type

  Correct

  Deploy-related properties such as executor and driver memory take effect when executors launch; they are set in the compute configuration, not changed at run time.

• BCall spark.conf.set again right before each action in the notebook

  Incorrect

  Repeating a runtime setting does not resize executors that are already running.

• CRun SET spark.executor.memory = 16g in a %sql cell

  Incorrect

  A SQL SET changes the session configuration at run time, which has the same limitation.

• DAdd spark.executor.memory as a job task parameter

  Incorrect

  Task parameters pass values to the notebook's code; they do not configure the cluster's executors.

Runtime SQL settings can change within a session; deploy-related properties such as executor and driver memory are fixed when the compute starts.

• Aspark.sql.files.maxRecordsPerFile

  Incorrect

  This is the maximum number of records written out to a single file; it shapes the files a job writes, not the partitions a scan reads.

• Bspark.sql.files.openCostInBytes

  Incorrect

  This is the estimated cost of opening a file, used when several files are put into one partition; it is not the cap on bytes per partition.

• Cspark.sql.shuffle.partitions

  Incorrect

  This is the number of partitions used after a shuffle for joins and aggregations; it does not split the file scan.

• Dspark.sql.files.maxPartitionBytes

  Correct

  This is the maximum number of bytes to pack into a single partition when reading files from file-based sources such as Parquet, JSON and ORC; its default is 128 MB.

For file-based sources, the size of each read partition, and so of each scan task, follows spark.sql.files.maxPartitionBytes, 128 MB by default. Lowering it to 64 MB gives about twice as many, smaller scan tasks; rerunning the job then shows whether the change helped that stage.

• AIncrease spark.executor.memory on the worker nodes of the cluster

  Incorrect

  The executors are healthy; the problem is that all results are collected into the driver.

• BWrite the result to a table instead of collecting it to the driver

  Correct

  Collecting brings every row to the driver, which is limited by driver memory and maxResultSize; keeping the data distributed avoids that, and a larger driver helps only when the data must be collected.

• CIncrease spark.sql.shuffle.partitions for the query that builds the result

  Incorrect

  More shuffle partitions change task sizes on the executors, not the amount of data collected to the driver.

• DAdd a broadcast hint to the largest input

  Incorrect

  Broadcasting a large input adds memory pressure and does nothing about collected results.

Actions such as collect and toPandas move data to the driver. Keep large results distributed, or size the driver for what must be collected.