Is Large Language Model Good at Database Knob Tuning? A Comprehensive Experimental Evaluation

For a given workload, knob pruning is a critical process aimed at identifying the most important knobs and narrowing their permissive ranges, which can reduce the search space of the knob recommendation methods. Leveraging the LLM as an alternative to traditional knob pruning methods involves incorporating several important elements within the prompt. As illustrated in Figure 2, the prompt for knob pruning contains the following elements:

• •

  “Task Description” describes the objective of the LLM.

• •

  “Candidate Knobs” provides detailed information about candidate knobs within the database engine, encompassing knob names, allowable ranges, types of knobs, and their respective descriptions.

• •

  “Workload and Database Information” contains crucial details about the workload, data characteristics, database kernel, and the hardware.

• •

  “Output Format” specifies the response format of the LLM. Specifically, it requires the LLM to enumerate the names of the chosen knobs, as well as their corresponding ranges and types, in an organized JSON format.

The model initialization technique is designed to speed up the tuning process by leveraging historical tuning records to initialize the model used in the knob recommendation methods. In this paper, we concentrate on utilizing LLMs to produce a set of effective configurations for the given workload. Subsequently, these configurations can be used to initialize the BO-based methods, effectively accelerating their convergence speed. As illustrated in Figure 3, we construct the prompt for model initialization in the following format:

• •

  “Task Description” outlines the objective of the LLM.

• •

  “Demonstration for Knob Refinement” includes a knob refinement instance, which aims to serve as the one-shot example in the prompt for demonstration purposes. This instance includes a current configuration, inner metrics, and a refined configuration.

• •

  “Environment” contains the information about database kernel and hardware information. The database kernel details encompass the database engine’s name and version. The hardware information specifies the number of CPUs and the available memory resources. In addition, we also include the text descriptions of each inner metric and tunable knob.

• •

  “Information about Current Workload” includes features about the current workload, such as workload type (OLAP or OLTP) and read-write ratio, and data statistics in the database.

• •

  “Output Format” specifies the format for LLM responses.

• •

  “Current Configuration” displays the default values of the knobs, which serve as the anchor point as discussed in Section 2.

• •

  “Database Feedback” showcases the performance and inner metrics of the database when executing the given workload under the default configuration. Incorporating this feedback is essential, as DBAs often depend on these metrics to assess the database’s status and implement necessary adjustments. For example, confronted with a low cache hit rate, DBAs typically choose to increase the cache size to improve database performance.

In practice, we utilize the LLM for multiple samplings to acquire a collection of effective configurations. These configurations then act as the initial points for BO-based knob recommendation methods.

The knob recommendation emerges as the crucial subtask within the tuning system, aimed at identifying a promising configuration for a specific workload. As elaborated in Section 2, the LLM undertakes the task of knob recommendation through an iterative process. Initially starting from the default configuration, the LLM employs iterative refinements based on feedback from the database. The prompt for knob recommendation closely mirrors the prompt of model initialization, as depicted in Figure 3. The key distinction lies in the fact that, for knob recommendation, both the “Current Configuration” and “Database Feedback” are subject to change with each iteration.

To evaluate the knob pruning capability of LLMs, we utilize a learning-based method, SHAP (Lundberg and Lee, 2017), as a competitive baseline. SHAP provides a unified framework for interpreting the significance of each knob. By analyzing a given set of tuning observations, where each observation consists of a ¡configuration, performance metric¿ pair, the importance of each knob is determined through the calculation of its SHAP value. As highlighted in (Zhang et al., 2022a), SHAP currently stands out as the most effective learning method for assessing the importance of knobs. To gather training data for SHAP, we collect approximately 6000 observations for the SYSBENCH workload using the Latin Hypercube Sampling (LHS) method (McKay, 1992), which can sample configurations across the entire configuration space. Subsequently, we execute the workload under these configurations to acquire their corresponding performance metrics.

In addition, we invite an industry database expert to conduct the knob pruning task as a human annotation baseline. Specifically, we allow the expert to identify crucial database knobs and their important value ranges based on his own expertise and experience. The expert is also permitted to consult with other experienced database administrators, the broader database community, and official MySQL documentation to complete the task.

After narrowing the search space, we then utilize a traditional knob recommendation method, SMAC (Hutter et al., 2011), to optimize these selected knobs for a maximum of 120 iterations. We record the configuration and corresponding database performance in each iteration to calculate the TES and ODP metrics.

For LLMs, we utilize the prompt illustrated in Figure 2 to perform the knob pruning from the candidate knobs. It is worth noting that, during the inference of the LLM, we set the temperature parameter to 0 to guarantee deterministic outcomes.

The experimental results are illustrated in Figure 4. Our observations are as follows: (1) In the knob pruning task, certain LLMs (Claude-3-Opus, GPT-4o, and GPT-4-Turbo) demonstrate comparable or even superior performance to that of the database expert. Upon analysis, we discover a significant similarity between the knobs selected by the LLMs and those chosen by the expert. For instance, Table 1 showcases the top 10 most critical knobs identified by the database expert, GPT-4o, and SHAP. Notably, the knobs selected by GPT-4o closely align with those chosen by the database expert. This resemblance may be attributed to the extensive training data utilized for GPT-4o (and other LLMs), which incorporates MySQL community discussions, relevant articles, blogs, and official documentation (Zhou et al., 2024). Consequently, the LLM can emulate the behavior of the database expert, leading to similar knob pruning outcomes. (2) Furthermore, we note that nearly all evaluated LLMs outperform the previous learning-based method, SHAP, in terms of both convergence speed and optimal database performance, with GPT-4o exhibiting the most favorable results. After examining the selection outcomes produced by SHAP, we observe a distinct knob set compared to GPT-4o and the database expert.

In delving into why GPT-4o surpasses the performance of the database expert in this task, a closer examination of their differences reveals a key distinction: as shown in Table 1, GPT-4o opts for knob “join_buffer_size” while the database expert selects knob “max_connections”. Increasing “join_buffer_size” can enhance the efficiency of the join operator, consequently boosting overall database performance. On the other hand, the impact of increasing “max_connections” on database performance is not always beneficial. If “max_connections” exceeds the actual number of connections required by the workload, increasing the value of this knob will have no discernible effect on the database performance.

Our main findings of this section are summarized as follows:

• •

  In the knob pruning task, certain LLMs (such as Claude-3-Opus and GPT-4o) demonstrate superior performance, surpassing even that of the DBAs. Furthermore, nearly all LLMs exhibit superior performance compared to the learning-based baseline, SHAP.

• •

  The knobs chosen by some LLMs closely resemble those selected by the database expert, indicating the potential for LLMs to replace DBAs in automating the process of pruning knobs.

• •

  We observe closed-source LLMs are much better than open-source LLMs in this task, indicating that the source of the LLM can have a substantial impact on this task.

• •

  Simply prompting LLMs without fine-tuning any parameters can achieve performance levels comparable to that of humans, showcasing the remarkable flexibility and adaptability of LLMs in effectively addressing this task.

• •

  Therefore, one promising future direction is to fine-tune an LLM tailored for knob pruning, potentially enabling it to surpass experts by a considerable margin.

To further enhance tuning efficiency, a series of model initialization methods (Aken et al., 2017; Li et al., 2019; Zhang et al., 2021a) have been introduced to use the past tuning records to initialize learnable models in the knob recommendation methods.

Current model initialization methods can be broadly categorized into three main groups: workload mapping, model ensemble, and pre-training. Workload mapping, as proposed by OtterTune (Aken et al., 2017), involves matching the target workload with the most similar historical workloads and leveraging their tuning observations to initialize the surrogate model. This approach can be integrated into a wide range of BO-based knob recommendation methods. The model ensemble technique, as described in ResTune (Zhang et al., 2021a), entails collecting a set of well-established tuning models on historical workloads and then combining these models to guide the optimization of current tuning model for new workloads. Lastly, the pre-training technique is commonly utilized in RL-based knob recommendation methods, as seen in works such as QTune (Li et al., 2019) and CDBTune (Zhang et al., 2021b). This process involves initially pre-training parameters of the actor and the critic within the RL algorithm using a set of historical tuning records. Subsequently, when facing a new workload, the pre-trained models will be further fine-tuned. By avoiding the necessity to train models from randomly initialization, this technique could expedite the tuning process.

We have chosen representative methods from each category as baselines. For workload mapping, we have selected the OtterTune method (Aken et al., 2017) integrated with the BO-based knob recommendation method VBO (Duan et al., 2009). In the model ensemble category, we are using the ResTune method (Zhang et al., 2021a) combined with the meta-learning knob recommendation method RGPE (Feurer, 2018). Lastly, for pre-training, we adopt QTune (Li et al., 2019) as the baseline, which aims to accelerate the RL-based knob recommendation method DS-DDPG. In this section, all knob recommendation methods undergo 400 iterations with or without model initialization techniques.

We first use the prompt illustrated in Figure 3 to sample 10 potentially effective configurations from LLMs. Subsequently, in the initial stages of VBO, we replace randomly sampled configurations with these 10 LLM-generated configurations to expedite its tuning process. To quantify the acceleration potential facilitated by LLMs, we consider the original VBO method for comparison. To generate a set of configurations from LLMs, we utilize nucleus sampling (Holtzman et al., 2020) with a temperature of 1.0 and a top-p value of 0.98. During our experimentation, we observe that certain sampled configurations are duplicated. As a result, we iteratively perform samplings until we acquire 10 distinct configurations.

We present the experimental results in Table 2. To provide a more intuitive understanding of the effectiveness of different initialization methods, we adopt the approach outlined in (Zhang et al., 2022a) to introduce two additional metrics: performance enhancement (PE) and speedup. Specifically, we represent the TES and ODP values for the base knob recommendation method without initialization as $TES_{orig}$ and $ODP_{orig}$, and for the method with initialization as $TES_{init}$ and $ODP_{init}$. In the OLTP benchmark, a higher TPS value represents better performance. Therefore, the performance enhancement is calculated as follows:

and the speedup is defined as follows:

The PE metric assesses whether the initialization technique can aid the base knob recommendation method in identifying superior configurations. Subsequently, the speedup metric measures the degree to which the initialization technique expedites the tuning process. Higher values for both PE and speedup indicate improved performance of an initialization method.

The experimental findings are detailed in Table 2. Initially, we observe that the workload mapping technique yields a modest speedup of 11.71%. This outcome could be attributed to the limited utilization of historical tuning records. Moving on to the model ensemble technique, despite delivering a substantial 42.67% speedup, it still necessitates 215 iterations to reach peak performance. The outcomes of the pre-training technique are surprising. While it does lead to a significant performance improvement of 33.10%, it also causes a drastic decrease in speedup by -216.16%, a result that is unacceptable. In summary, these conventional initialization techniques do not clearly expedite the tuning process, which still necessitates 200-300 iterations to achieve the optimal performance.

For using LLMs in the model initialization task, we first observe that some LLMs (GPT-3.5, GPT-4o, and Claude-3-Opus) exhibit relatively poor performance enhancement and largely under-perform the base model (i.e., VBO). After analyzing the sampling configurations of these LLMs, we find that despite providing 20 knobs in the prompt, these models predominantly adjust only a handful of knobs, leaving the rest at default settings. Consequently, the 10 LLM-generated configurations exhibit significant similarities. Utilizing these configurations to initialize VBO might limit its capacity to explore uncharted areas and thus result in poor performance enhancement. To address this issue, one approach is to increase the temperature value to inject more randomness during the sampling phase. Among the other LLMs, including GPT-4-Turbo, Llama3-8B-Instruct, Llama3-70B-Instruct, and Qwen2-7B, a slight performance decrease (less than 2%) is observed, falling within an acceptable range. Moreover, initializing VBO with LLM-generated configurations leads to a noticeable acceleration in convergence speed. Notably, LLMs have shown an average speedup of 71.42%, with some achieving an impressive 99.37% boost. In essence, utilizing LLMs for initializing VBO requires a careful balance between ODP and TES. Given the minor performance impact and significant tuning acceleration, this trade-off is deemed acceptable.

Our main findings of this section are summarized as follows:

• •

  Existing model initialization methods do not demonstrate an obvious speedup in the tuning process, often necessitating hundreds of iterations to identify a suitable configuration. On the other hand, LLMs have shown their capability to greatly expedite the convergence of BO-driven knob recommendation approaches through the generation of initial configurations.

• •

  In contrast to the findings in knob pruning, we note that open-source LLMs outperform closed-source LLMs in this particular task. This disparity arises from the tendency of closed-source LLMs to frequently generate similar configurations, thereby constraining the exploration and exploitation capacities of the subsequent BO-based knob recommendation method.

• •

  Hence, a promising direction for future exploration lies in leveraging LLMs to generate a variety of valuable and distinct configurations to initialize the base knob recommendation methods.

The knob recommendation phase stands out as the core component of the entire tuning system, exerting a direct influence on the final tuning performance. Within this section, we evaluate LLMs’ knob recommendation capability against three widely-used traditional knob tuners: Vanilla Bayesian Optimization (VBO) (Duan et al., 2009), Sequential Model-based Algorithm Configuration (SMAC) (Hutter et al., 2011), and Deep Deterministic Policy Gradient (DDPG) (Lillicrap et al., 2016). VBO is a BO-based konb recommendation method utilizing a vanilla Gaussian Process (GP) as its surrogate model. The vanilla GP aims to model the relationship between the configuration and the database performance (Duan et al., 2009; Aken et al., 2017). On the other hand, SMAC is another BO-based approach that utilizes a random forest algorithm as its surrogate model to guide the tuning process (Breiman, 2001). DDPG is an RL-based method that has been widely integrated into existing knob tuning frameworks. It distinguishes itself from traditional RL algorithms like deep Q-learning (Maglogiannis et al., 2018) by its ability to operate in both discrete and continuous action spaces. DDPG involves the training of two neural networks: the actor network, responsible for selecting actions (i.e., configurations) based on database states, and the critic network, which evaluates the chosen action’s reward (e.g., latency or transactions per second). This reward signal updates the actor network, enabling it to make better decisions in subsequent iterations. Following the settings used in Section 6.2, all these base knob tuners undergo 400 iterations.

As outlined in Section 2, we frame the knob recommendation task for LLMs as an “iteratively refining” process. Specifically, starting from the default configuration, we iteratively refine the current configuration using feedback from the database. In our evaluation, we also introduce a new metric called “IR TPS” to measure the database performance after the Initial Refinement step. Considering the high cost associated with using closed-source models, we restrict the LLMs to undergo a maximum of 30 rounds of refinement. Furthermore, we set the temperature to 0 to generate deterministic LLM outputs.

We present experimental results in Table 3. In the realm of traditional methods, two BO-based strategies, SMAC and VBO, surpass the singular RL-based method, DDPG, in terms of ODP, leading to enhanced database performance. Nevertheless, in terms of TES, both BO-based methods require over 300 iterations, a considerable increase compared to DDPG, which achieves convergence in just 99 iterations.

For LLMs in the knob recommendation task, we observe remarkable results. Specifically, it’s impressive that some LLMs, including GPT-4-Trubo, GPT-4o, and Qwen2-7B, show a great performance enhancement over the default configuration within only one-step refinement (see “IR TPS”). This finding reveals that the LLM has the potential to be an end-to-end knob recommendation method without extensive iterations required in existing methods. Furthermore, leveraging feedback from the database, LLMs can iteratively suggest enhanced configurations, attaining comparable database performance to traditional methods but with significantly fewer iterations. Notably, in the instances of GPT-4-Turbo and Qwen2-7B, their refined configurations closely rival the top-performing traditional method, SMAC (155 versus 157), yet GPT-4-Turbo and Qwen2-7B achieve optimal performance in just 9 and 14 iterations, respectively, compared to SMAC’s 375 iterations. These findings indicate that LLMs possess the capability to comprehensively understand and utilize database feedback to refine configurations for enhanced overall database performance, showcasing remarkable exploration and exploitation capabilities.

Furthermore, LLMs demonstrate better interpretability in contrast to traditional black-box approaches. Illustrated in Figure 5, when assigned the role of configuration recommendation, the LLM consistently provides detailed rationales and considerations in the “chain-of-thought” (Wei et al., 2022) manner for each adjustment made to the knobs. This attribute not only bolsters practical applicability but also enhances the reliability of the results. On the other hand, by presenting the reasoning behind its recommendations, LLMs can help DBAs in making informed decisions, fostering a collaborative and streamlined process of knob recommendation.

Finally, by comparing “IR TPS” and ODP metric in Table 3, we can observe that the “iterative refinement” strategy can significantly improve the quality of the recommended configurations for some LLMs. Take Claude-3-Opus as an example: initially, the first refinement yields only 26 TPS. However, through the iterative refinement process, a significantly superior configuration yielding 148 TPS is identified. To examine the correlation between the number of iterations and database performance, we present the findings in Figure 6. Our analysis reveals that nearly all LLMs effectively leverage the database feedback to suggest improved configurations in the iteration process.

Our main findings of this section are summarized as follows:

• •

  In terms of tuning effectiveness, LLMs demonstrate a remarkable ability to achieve database performance on par with traditional methods. Moreover, LLMs can pinpoint promising configurations in significantly fewer iterations, showcasing impressive tuning efficiency.

• •

  Contrasted with traditional black-box methods, the knobs suggested by LLMs offer enhanced interpretability. For DBAs, these LLM-recommended knobs are more “traceable”, facilitating further adjustments based on their expert knowledge.

• •

  We do not observe a notable performance difference between open-source and closed-source LLMs in the knob recommendation task. Both variants demonstrate similar effectiveness.

• •

  The development of an end-to-end LLM-based knob recommendation approach could represent a promising research avenue. Such a method has the potential to eliminate the need for extensive iterations present in previous methods.

To establish new evaluation setups, we consider modifications across three key dimensions: benchmark, database engine, and machine instance.

On the other hand, TiDB is a new database engine (released in 2017) that focuses on the distributed database system, supporting Hybrid Transactional and Analytical Processing (HTAP) workloads (Huang et al., 2020). Given that TiDB is a relatively new player in the database engine landscape, with limited discussion available online, it offers an opportunity to assess the adaptability of LLMs to newer database engines. In practice, we use TiDB version 8.2 and instantiate the TiDB cluster with a complete topology, simulating the production deployment. This setup includes 3 TiKV instances (row-based storage engine), 1 TiFlash instance (column-based storage engine), 1 TiDB instance (distributed database server), 1 PD instance (placement driver), and 1 Monitor. Following the official documentation, we pinpoint 7 important knobs for tuning, leaving the remainder at their automatic, hardware-configured default settings. This contrasts with PostgreSQL, where TiDB’s default knob values adjust dynamically to match the hardware specifications.

• •

  Compared to conventional approaches, LLMs consistently deliver stable and commendable results across various benchmarks, database engines, and hardware configurations. Furthermore, employing LLMs in these new evaluation contexts requires minimal code adjustments due to their flexibility through prompting techniques. This insight highlights the potential of LLMs in navigating more complex and dynamic tuning environments and tasks.

• •

  Our observations indicate that, in most new evaluation settings, closed-source LLMs outperform their open-source counterparts, suggesting superior generalization capabilities in closed-source models.

• •

  LLMs demonstrate proficiency with well-established database engines like MySQL and PostgreSQL. In the case of newer database engines such as TiDB, LLMs can effectively adapt by incorporating specific knob and metric information within the prompts, facilitating a smooth transition to unfamiliar environments.

• •

  Among the LLMs evaluated, GPT-4-Turbo and Claude-3-Opus consistently exhibit the most reliable performance. Given their proprietary nature, a promising direction for future work involves creating an open-source LLM that matches or exceeds the capabilities of these closed-source models.