DBTuneSuite: An Extendible Experimental Suite to Test the Time Performance of Multi-layer Tuning Options on Database Management Systems

TL;DR

DBTuneSuite introduces an extensible experimental framework to evaluate the time performance of multi-layer tuning options across four open DBMS (MySQL, MariaDB, DuckDB, PostgreSQL). It provides end-to-end scripts and data for experiments spanning data loading, indexing strategies, covering/clustered indexes, vertical partitioning, denormalization, triggers, connection pooling, and more, tested on standard schemas including Employee and TPC-H. The study reveals system-dependent effects for nearly every knob, with surprising findings such as direct-path loading often dominating bulk ingestion, index use not always being superior for all queries, and heavy variation in benefits from vertical partitioning and triggers. The authors also present practical guidance and a public repository to enable practitioners to reproduce results and tailor benchmarks to their workloads, highlighting that high-level tuning choices can yield larger gains than traditional parameter tuning. Overall, DBTuneSuite provides a comprehensive toolkit for understanding, comparing, and optimizing DBMS behavior across real-world workloads and configurations.

Abstract

DBTuneSuite is a suite of experiments on four widely deployed free database systems to test their performance under various query/upsert loads and under various tuning options. The suite provides: (i) scripts to generate data and to install and run tests, making it expandable to other tests and systems; (ii) suggestions of which systems work best for which query types; and (iii) quantitative evidence that tuning options widely used in practice can behave very differently across systems. This paper is most useful for database system engineers, advanced database users and troubleshooters, and students.

Figures (91)

• Figure 1: Decision tree for engine choice based on workload. Indexes are particularly effective for MySQL and Postgres. DuckDB is very fast for scans.
• Figure 2: Direct path significantly improved the performance across all the engines.
• Figure 3: Direct path significantly improved the performance across all the engines.
• Figure 4: There is a clear improvement in the performance of data loading across all engines as batch size increases on the $10^5$ row dataset except on PostgreSQL after 10,000 batch.
• Figure 5: There is a noticeable improvement in data loading performance for MariaDB and DuckDB as the batch size increases for the $10^7$-row dataset, while PostgreSQL and MySQL show generally stable performance across different batch sizes.