Performance Tuning and Observation Techniques for dble Using BenchmarkSQL

Background

BenchmarkSQL is a TPC‑C benchmark tool that simulates real‑world transaction workloads across many relational databases, offering more realistic pressure testing than single‑purpose tools like Sysbench. dble, a database middleware, can also be benchmarked with BenchmarkSQL, and this article discusses observable‑driven tuning methods focused on configuration and internal thread settings.

Configuration Recommendations

Key configuration points include ensuring the number of dble user connections exceeds the test concurrency, setting dble log level to WARN (log4j2.xml), using RC isolation level with backend MySQL, disabling performance‑impacting switches such as useSqlStat=0 and enableSlowLog=0 , and configuring dble tables (global vs. sharding) with uniform data distribution using modulo algorithms. MySQL performance parameters should be tuned according to hardware.

Observation Methods

System Resource Monitoring

Use dstat to capture CPU, disk I/O, and network I/O in real time, optionally exporting results to CSV for later analysis.

dble‑Side Monitoring

Enable query cost time statistics by setting useCostTimeStat=1 in bootstrap.cnf and restart dble. Observe the six query phases (start, parse, route, fetch, post‑process, feedback) via the WallTime.Avg metric to locate bottlenecks.

Thread usage can be inspected with the command show @@thread_used , allowing adjustments of thread pool sizes based on observed utilization.

Load‑Testing Experience

Initial experiments showed that merely increasing thread counts did not always improve latency; instead, thread‑usage metrics became the primary tuning guide. The team categorized dble threads into three groups: front‑/back‑end I/O threads (processors, backendProcessors), business processing threads (processorExecutor, backendProcessorExecutor), and other threads (complexExecutor, writeToBackendExecutor). Adjustments revealed that business processing thread count has the strongest positive correlation with TPM‑C throughput, while I/O thread count and other threads have minimal impact.

Network I/O on the dble host also correlates positively with TPM‑C; reducing business processing threads often raises both network I/O and thread utilization, leading to higher throughput.

Summary

With the described tuning steps on dble 3.20.10.0 (4 MySQL backends, 1000 warehouses, 1000 concurrent threads) the observed performance ratio was roughly 2.8 : 1 compared to MySQL alone; the theoretical target is around 4 : 1, indicating further optimization potential.

For future BenchmarkSQL tests, focus on keeping business processing thread utilization high (≈95 %), monitor network I/O with dstat, and follow official dble recommendations for I/O and auxiliary thread counts. Ensure each test starts from a clean data state to avoid contamination.