Big SQL Worker Configuration

In Big SQL 4.2 and previous releases there was a 1:1 relationship between the number of Big SQL Workers : the number of Compute Nodes. However since Big SQL 4.2.5 and Big SQL v5, there is now a n:1 relationship between the number of Big SQL Workers to number of Compute Nodes. Each Big SQL Worker within a compute node is referred to as a logical Big SQL worker.

Big SQL 4.2 – Compute nodes house 1 Big SQL Worker

This architectural change since Big SQL 4.2.5 offers an alternative means to increase inter-partition parallelism to boost INSERT and SELECT statement performance.

Big SQL Compute nodes housing multiple Big SQL Workers

The default number of Logical Big SQL workers is 1 because the optimal number of Logical Big SQL Workers to add depends on the cluster specifications. The next section gives some best practice recommendation as to the number of Logical Big SQL Workers to configure on your cluster. Read more about how to Configure Logical Big SQL Workers from Ambari.

Best Practice considerations for Logical Big SQL Workers

Best Practice for number of Logical Big SQL Workers to configure

On each Compute Node, logical Big SQL workers share memory and CPU resources. Each logical Big SQL worker has access to a subset of the memory on each Compute Node. Our recommendation is that each logical Big SQL worker should be assigned at least 28GB of memory. Each logical Big SQL worker binds to all CPUs available to Big SQL on each Compute Node. Our recommendation is that there should be at least 1 core to service each Logical Big SQL Worker.
Therefore as a best practice, the following calculation can be used when determining the number of logical Big SQL workers to choose:

    1) num_logical_workers= min(num_cores*bigsql_resource_pct/100, system_memory (GB)/28 * bigsql_resource_pct/100)                    where bigsql_resource_pct is the percentage of resources assigned to Big SQL

The default bigsql_resource_percentage (also referred to as bigsql_resource_allocation in some later releases) is 25. This can be increased during install time or after install. Read more about how to increase Big SQL memory.

Best Practice for Degree of Intra-Partition Parallelism with Logical Big SQL Workers

Logical Big SQL workers offer an alternative means to increase inter-partition parallelism. Intra-partition parallelism also needs to be considered when increasing inter-partition parallelism. For best performance we do not want to over saturate the cores on the system. The degree of intra-partition parallelism is controlled by the dbm cfg parameter DFT_DEGREE, also referred to as the degree of SMP parallelism.
Therefore as a best practice recommendation adjust the DFT_DEGREE when using Logical Big SQL Workers so that:

    2) num_log_workers * DFT_DEGREE (y) <= num_cores * bigsql_resource_pct/100                     By default, DFT_DEGREE is 8. To adjust the DFT_DEGREE issue this command:   db2 update db cfg for bigsql using DFT_DEGREE y and restart the Big SQL Service.

The DFT_DEGREE is set to 8 based on results of internal testing using the TPC-DS workload. Increasing beyond 8 could lead to performance regressions for some queries especially when running with multiple queries concurrently.

Other Best Practices when using Logical Big SQL Workers

When running Big SQL workloads, it is always recommended that each Compute Node has the same amount of memory and CPU cores so that assignment of work can be spread evenly across the cluster. Consider adding memory or CPU cores to those systems that are less powerful than the others.

Every logical Big SQL worker has to connect to the Big SQL Scheduler at the start of a scan to get a list of HDFS splits that has to be read. The total number of threads used by the Scheduler (configured through scheduler.maxWorkerThreads in bigsql-conf.xml) by default is 1024. Depending on the number of logical Big SQL workers chosen, this parameter may need to be increased. In the test system below we did not need to increase this configuration parameter.

Performance Improvements

Cluster Specifications

Two clusters were used for this study:

Logical Big SQL Worker Configuration

Following the best practice recommendation 1), the number of Big SQL logical workers that should be configured on each Compute Node is: min ((128*0.9/28), (20*0.9)) = 4.

Following the best practice recommendation 2), for best performance DFT_DEGREE is adjusted to 4 as the default 8 would have over-saturated the system : num_log_worker (4) * DFT_DEGREE (8) = 32 and there are only 20 cores on the system. DFT_DEGREE of 4 * 4 Logical Big SQL Workers = 20 cores * 0.8 so the DFT_DEGREE was altered to 4 for these tests.

Insert Performance Results

For both 1 and 10 TB TPC-DS, we observed a 1.6x speedup to INSERT performance with 2 logical Big SQL workers per Compute Node, and a 2.2x speedup with 4 logical Big SQL workers per Compute Node.

INSERT performance improvement with addition of Logical Big SQL Workers

Select Performance Results

For the single stream ( i.e. each query running after the previous completed) 10TB TPC-DS workload, this combination of 4 logical Big SQL workers and DFT_DEGREE=4, provides a 1.6x speedup over the baseline. Logical Big SQL workers provide the benefit in single user workloads because Big SQL can leverage more CPU resources by driving more parallelism. In these tests, Big SQL drove ~2.5x more user CPU on average with this configuration.

1.6X performance improvement on single stream TPC-DS with 4 Logical Big SQL Workers

For the 4-stream (4 concurrent users running at the same time ) 10 TB TPC-DS workload, this combination provides a 1.1x speedup over the baseline. Performance improvement would have been more with the multi-user workload but we found that we started to see more contention on the disk sub-system with this configuration. Since this test was not configured with SSD drives, we did not notice the same level of performance improvements with multiple vs single stream because our disks were not fast enough to accommodate the load.

1.1X performance improvement on 4-stream TPC-DS with 4 Logical Big SQL Workers

The snapshot belows shows the disk utilization when no Logical Big SQL Workers are configured. Notice that the disk subsystem is not saturated in this configuration.

Disk utilization on 4-stream TPC-DS with no logical Big SQL workers configured

The snapshot below was taken when 4 Logical Big SQL Workers was configured. Note that there are instances when the disk was 100% utilized which shows that the bottleneck moved to slow disk response times. It also shows that the system is more saturated with the use of Logical Big SQL Workers.

Disk utilization on 4-stream TPC-DS with 4 logical Big SQL workers configured

In this study we had more powerful SSD drives and also Use Logical Big SQL Workers when comparing Big SQL to Spark SQL Performance.

Conclusion

Boost Big SQL Performance with the use of Logical Big SQL Workers available in Big SQL 4.2.5 and Big SQL 4.3TP To get the expected behavior, follow the best practice recommendation as closely as possible.

Thank you to the following people for contributions towards this work: Metin Kalayci, Tony Lai, Pat Meehan, Abhayan Sundararajan, Ilker Ender, Nailah Bissoon, Hebert Pereyra, Suvina D’Souza, Simon Harris, Xabriel J Collazo Mojica

IBM Support

Tips

Logical Big SQL Workers Boost Big SQL Performance - Hadoop Dev

Technical Blog Post

Abstract

Body