Workload-Aware Evaluation of Thread Parallelism and Memory Configuration for Analytical Query Processing in DuckDB

Performance of analytical queries can be highly dependent on resource allocation and workload type, making it potentially inefficient to allocate them uniformly. This study examines how the TPC-H workload is processed by DuckDB with varying levels of thread parallelism, dataset size and memory limits. A total of 1,980 executions were captured for all 22 TPC-H queries evaluated on one, two and four threads and at three scale factors (SF=1, SF=5, SF=10). To investigate the memory sensitivity of five structurally different queries at SF=10 under three memory constraints, namely 1 GB, 2 GB and 4 GB, a complementary counterbalanced experiment was conducted yielding a further 135 measurements. We observed that the four-thread execution outperforms the single-thread execution for all 22 queries at all three scales with mean speedups of ~1.81×, ~1.95×, and ~1.94× at SF=1, SF=5, and SF=10, respectively. Responses to the query, however, were not consistent, and the gains from parallelism (from 2 to 4 threads) were not consistent in all cases. The memory experiment also showed that the performance of Q9 decreased by around 34.0% from 1 GB to 4 GB while the performance of other selected queries was either insignificant, or even improved a little, or even had a plateau. These results suggest that the benefits of extra threads and configured memory can vary by workload, suggesting that query specific behavior should be taken into account when configuring resources for performing analytical queries.