DB-BERT: making database tuning tools “read” the manual

The field of NLP has recently seen significant advances across a range of long-standing problems [53]. These advances have been enabled, in particular, by the emergence of large, pre-trained language models [16], based on the Transformer architecture [51]. Such models address two pain points of prior NLP approaches: lack of task-specific training data and bottlenecks in computational resources for training. Language models are trained, using significant computational resources, on tasks for which training data is readily available in large quantities. For instance, masked language modeling [7] (i.e., predicting masked words in a sentence) can use arbitrary Web text for training. Instead of training new models from scratch for other NLP-related tasks, pre-trained models can be used as a starting point. Pre-trained models can be used either via fine-tuning or via prompting. Using fine-tuning, pre-trained models are trained further on task-specific training data. However, due to the use of pre-training, the number of required training samples and computational overheads are reduced by many orders of magnitude [16]. The latest generation of language models [6, 25, 58], including also OpenAI’s GPT model series [10], can often be used without task-specific training via prompting [4]. Instead of training data, it is sufficient to describe a new task to solve as part of the prompt, the text input to the model.

Natural language query interfaces [13, 14, 18, 26, 38] are the most popular application of pre-trained models in the context of databases. At the time of writing, corresponding approaches constitute the state of the art for text-to-SQL translation benchmarks (e.g., WikiSQL [59] or SPIDER [56]). The problem of translating text into queries shares certain characteristics with the problem of extracting tuning hints from text. In both cases, text is translated into a formal representation. However, whereas text-to-SQL methods typically translate a single sentence into one single SQL query, DB-BERT extracts multiple tuning hints from multi-sentence text passages. Also, DB-BERT must aggregate and prioritize conflicting hints obtained from multiple sources (a sub-problem that does not appear in the context of natural language query interfaces). Unlike most prior work on text-to-SQL translation, DB-BERT does not assume the presence of labeled training samples.

Recent work explores a variety of novel use cases for large language models in data management [46]. These include applications for data preparation and integration problems [2, 39, 41], data profiling and discovery [5, 19, 47], as well as novel database engine designs that exploit language models for data processing directly [37, 39, 42] or to synthesize code for processing [2, 44].

Reinforcement learning [40] addresses scenarios such as the following. An agent explores an environment, selecting actions based on observations. Those actions may influence the environment (whose dynamics are initially unknown to the agent) and result in reward values. The goal of the agent is to maximize reward, accumulated over time. In order to do so, the agent needs to balance exploration (trying out action sequences about which little is known) with exploitation (exploiting action sequences that seem to work well, based on observations so far). The area of reinforcement learning has produced various algorithms that balance this tradeoff in a principled manner. Specifically, DB-BERT uses the Double Deep Q-Networks [50] algorithm. This algorithm learns to estimate action values in specific states via deep learning, using two separate models for selecting actions and evaluating them.

Reinforcement learning has been used for various problems in the database domain [3, 15, 55, 57], including tuning problems (discussed in detail next). Different from prior work, we combine reinforcement learning with NLP to find promising parameter settings. More broadly, our work connects to prior work on leveraging text for reinforcement learning, in particular prior work on instruction following [27]. However, prior work does not consider performance tuning, specifically database tuning, as we do.

A recent vision paper [43] on NLP-enhanced database tuning, written by the same author as the current publication, relates most to the current work. The prior work trains a Transformer model to recognize sentences containing tuning hints via supervised learning. For sentences classified as tuning hints, it extracts parameters and values according to a simple heuristic. This approach uses only text input but no run time feedback. It extracts a fixed set of recommendations from a document collection, without being able to adapt to specific workloads and performance metrics. DB-BERT, on the other hand, uses hints extracted from text merely as a starting point. It supports a broader range of tuning hints (e.g., implicit hints) and does not require annotated tuning hints during training. We summarize some of the differences in Table 2 and compare both approaches experimentally in Sect. 9.

Machine learning is nowadays the method of choice for many database optimization problems, ranging from query optimization [9, 12, 20, 21, 28, 29, 32, 48] over physical design decisions [8, 15, 23, 55] up to database system parameter tuning [24, 34, 52, 57]. We address an extended version of the latter problem, expanding the input by natural language text documents.

Algorithm 4 implements the transition function, used by DB-BERT to translate single hints (the pseudo-code is close to the implementation of the step function in the corresponding OpenAI Gym environment²). In Algorithm 4, and for a fixed tuning hint, the current state is characterized by a partially specified formula (f) and by variable d, the integer ID of the next decision to take. For each hint, we start with an empty formula f and \(d=0\). We represent actions (input a) as integer numbers from one to five. The semantics of actions depend on the value of d. For \(d=0\), the action decides whether the current hint is erroneous (constant NO_HINT) and, if not, whether the hint suggests a relative or absolute parameter value. Relative values are expressed as percentage of system properties such as main memory or the number of cores (stored in vector S with \(S_a\) representing a specific vector component). For relative values, we set f to the product between value v and the corresponding system property. We unify treatment of relative and absolute values by setting \(S_1=1\) (i.e., \(a=1\) represents an absolute value).

For \(d=1\), the action picks a multiplicator from M that allows deviating from the proposed value. Unlike prior work using extracted hints without changes [43], such multiplicators allow DB-BERT to adapt to specific benchmarks. In the next section, we introduce an additional decision that weighs hints. Here, we have fully specified the formula after two decisions. Next, we try setting parameter p to the formula evaluation result. If the setting is rejected by the DBMS, we directly advance to an end state (constant END). This case yields negative reward (motivating our agent to learn translating hints into admissible formulas). Otherwise, we evaluate performance on the input benchmark b. The result is a reward value. Higher rewards are associated with better performance. We calculate reward by comparing performance with a configuration to evaluate to performance with default settings. For OLAP benchmarks (e.g., TPC-H), we use the delta of run times (scaled by a constant). For OLTP benchmarks (e.g., TPC-C), we use the throughput delta.

We reward configurations that are admissible and increase performance. Those two metrics are immediately relevant for tuning. We use them when applying DB-BERT for tuning a specific system for a specific benchmark. Before applying DB-BERT for specific tuning tasks, we perform a training phase to fine-tune DB-BERT’s language models for hint translation in general. To speed up convergence, only during training, we add an additional component to the reward function. This component rewards settings that seem more likely, e.g., since they are in the same order of magnitude as the default settings for a parameter. Such heuristics replace manually generated hint translations, used in prior work [43]. Figure 4 illustrates the MDP behind the translation process (some of the states in Fig. 4 are not explicitly represented in Algorithm 4).

DB-BERT introduces a learning agent to choose actions in order to maximize rewards. In each state, the agent selects among a discrete set of options. Each option can be expressed as a natural language statement. We can find out which option is correct by comparing that statement against the tuning hint text. Hence, we model action selection as a “multiple choice question answering problem”. Pre-trained language models can be used to solve this problem (in our implementation, we use the BertForMultipleChoice Transformer model³). We fine-tune model weights during training, based on rewards received.

Algorithm 5 shows how the agent evaluates specific actions, based on observations. Besides the action to evaluate, the input includes a description of the current tuning hint (tuning text t, parameter p, and value v) as well as the current translation step (decision d). We associate each combination of an action and a decision with a label. The array containing those labels is represented via constant CHOICE_LABEL in the pseudo-code. The label is a natural language sentence, representing the semantics of the associated choice. It contains placeholders for the concrete parameter and value in the tuning hint. The Instantiate function replaces placeholders by concrete values.

The BERT model uses three inputs: an input text, a type tag associating input tokens with one of two input types, and a mask indicating tokens to consider. Here, we concatenate hint text and instantiated label to form the input text. Types separate hint text from label. By default, all input text is considered for processing. An exception occurs during our generic training phase (see Sect. 6.1 for more details). Here, we want to avoid learning the names of specific parameters as they do not generalize across systems. Hence, we mask all occurrences of the current parameter name (Function Mask). On the other side, if learning system and benchmark-specific configurations for a concrete tuning problem, there are no reasons to hide information. Algorithm 5 uses a Boolean flag (MASKED_MODE) to switch between these two modes.

Table 3 shows labels associated with different actions and the first decision level. At this level, we decide whether a candidate hint represents an actual hint and, if so, whether the value is relative or absolute. Finally, we illustrate the translation by an example.

Example 3   Consider tuning hint \(\langle t,p,v\rangle \) with \(t=\)“Set shared_buffers to 25% of RAM”, \(v=25\%\), and finally \(p=\)shared_buffers. First, the agent decides whether the hint is valid and whether it recommends an absolute or relative value. Using the labels from Table 3, the agent evaluates alternative actions based on the hint text. For instance, for action 1, the agent generates the input text “Set shared_buffers to 25% of RAM. shared_buffers and 25% relate to main memory.”, separating the two sentences via the type specification. If masked mode is activated, the two occurrences of the shared_buffers parameter are masked. To make a choice, the agent internally compares values resulting from applying BERT to the input for each possible action.

Algorithm 8 shows the algorithm used for analyzing tuning documents. Different from the prior variant of DB-BERT, this algorithm handles hint extraction and hint translation together. This means that reinforcement learning is not used anymore to translate text into tuning recommendations. Given a text passage, parameter, and system properties, Algorithm 8 first extracts parameters that relate to the input text. Again, parameter references may be either explicit (i.e., the name of the parameter is explicitly mentioned) or implicit (i.e., the text describes a desired effect while the name of the parameter has to be inferred).

Next, Algorithm 8 iterates over all extracted parameter names. For each parameter, the algorithm generates a question (using a simple template). The purpose of that question is to extract recommended settings for the current parameter. To answer this question, given the tuning text as context, standard methods from the NLP area can be used. The call to sub-function QA represents the invocation of a model for question answering, pre-trained, for instance, on the SQUAD benchmark [36] (or pre-trained on tasks such as text completion which enable the latest generation of language models to solve question answering tasks with a high precision [4]).

Given the lack of task-specific training for database tuning, the chance for spurious extraction may increase. Hence, Algorithm 8 filters answers using a confidence threshold \(\theta \), comparing \(\theta \) to the confidence score returned for the answer by the question answering model. Assuming that the answer confidence exceeds the threshold, a numerical value is extracted from the recommendation text. This value is either an absolute recommendation or a relative one, referring to system properties such as the amount of RAM, the amount of disk space, or the number of CPU cores. Given a collection of named system properties, the algorithm uses zero-shot classification to compare the recommendation text to a set of text labels (e.g., “RAM”, “disk”, “cores”). Here, language processing methods typically compare embedding vectors of text labels and a sample to find the class with minimal distance in the embedding vector space. Based on the results of zero-shot classification, the algorithm adapts the extracted value by multiplying with the system property value, associated with the result class.

Finally, the resulting tuning hint is added to the result set, returned by Algorithm 8. Note that tuning hints do not refer to the source text anymore as no further text processing takes place after extraction (different from the prior variant which iteratively translates tuning hints into formulas).

In this variant of DB-BERT, tuning hints are already translated as part of pre-processing. Hence, the number of decisions made via reinforcement learning reduces. Similarly to before, the algorithm iterates over tuning hints that are sorted using any of the simple heuristic discussed previously. For each of those hints, the reinforcement learning algorithm makes the following decisions:While the search space for reinforcement learning changes, the reward function (integrating rewards for appropriate value assignments as well as for performance improvements) remains the same. Similarly to before, weighted hints are aggregated into configurations to try, taking into account the weights associated with different hints for the same parameter.

We compare approaches for tuning system configuration parameters for MySQL 8.0 and PostgreSQL 13.2. We consider all numerical and Boolean tuning parameters that those systems offer: 232 parameters for PostgreSQL and 266 parameters for MySQL. We use TPC-H with scaling factors one (Sect. 9.4) and ten (Sect. 9.5) and TPC-C with scaling factor 20 as benchmarks. For TPC-C, we use ten terminals, unlimited arrival rate, and 60 s for both, warmup and measurement time (for each trial run). Besides those parameters, we use the default TPC-C configurations for PostgreSQL and MySQL from the OLTP benchmark.⁵ In addition to the two TPC benchmarks, we tune for the Join Order Benchmark (JOB) [22]. This benchmark is unusual in that it is designed to challenge the query optimizer in particular. Different from the TPC benchmarks, it has been proposed recently and encountering specialized tuning recommendations for this benchmark on the Web is unlikely. We use all queries of JOB on PostgreSQL and (as time for processing all queries with the default configuration exceeds the intended tuning time frame) the first 20 queries for MySQL. For the analytical benchmarks, each trial run executes all considered queries. For each tuning scenario and baseline, we execute five runs and allow for 25 min of tuning time per run (prior work uses the same time frame [57]). All experiments execute on a p3.2xlarge EC2 instance with 8 vCPUs, 61 GB of RAM, and a Tesla V100 GPU featuring 16 GB of memory. The EC2 instance uses the Amazon Deep Learning AMI with Ubuntu 18.04.

We compare against the DDPG++ algorithm [49] as representative for tuning without NLP-enhancement. We consider different value ranges for tuning parameters, ranging from a factor of two around the default value (i.e., d/2 to \(2\cdot d\) where d is the default) to 100. In the following plots, we only report results for the factor leading to optimal performance at the end of the tuning period. Also, we compare to two baselines described in a recent vision paper on NLP-enhanced database tuning [43]. In the following, Prior-Main denotes the main method proposed by that prior work, based on supervised learning. Also, we compare against a simple baseline, denoted as Prior-Simple, described in the same paper [43]. Furthermore, we compare to several rule-based tuning tools, specialized for the database management systems we use in our evaluation. For PostgreSQL, we use PgTuner,⁶ an online interface that allows users to tune PostgreSQL for specific workload types. In the interface, we provide the precise PostgreSQL version, as well as all relevant hardware properties of the target platform (RAM, disk, and CPUs) as input. For the analytical workloads (TPC-H and JOB), we use tuning recommendations for the data warehouse workload type. For TPC-C, we use recommendations for transactional processing. For MySQL, we use the MySQLTuner.⁷ We obtain recommendations by executing the MySQLTuner tool locally on the target platform. In the following plots, legend entry “Specialized” denotes results for the tuning tool (PgTuner or MySQLTuner) matching the tuned database system.

By default, we use the following configuration parameters for DB-BERT. DB-BERT uses reinforcement learning to select multiplicator values and weights for each hint from a fixed set of alternatives. For all experiments, DB-BERT selects the multiplicator from the set \(\{1/4,1/2,1,2,4\}\) and weights from \(\{0,2,4,8,16\}\). We use the same number of alternatives (five) in each case. This makes it easier to model the associated environment with OpenAI’s gym framework. We avoid using overly small or large multiplicators (if the optimal parameter value deviates by more than factor four from the proposed value in any direction, the associated hint should be disregarded). The set of weight alternatives allows DB-BERT to disregard hints (by using a weight of zero) as well as to make specific hints up to eight times more important, compared to other hints with non-zero weights. We set l to 10 in order to allow at most ten hints per episode and parameter. We consider at most 50 hints per episode in total (\(e=50\)) and evaluate two configurations per episode (\(n=2\)). DB-BERT splits text documents into segments of length at most 128 tokens.

We also evaluate a DB-BERT variant, described in Sect. 8, that parses manuals via a zero-shot approach. This means that no task-specific training is used (unlike for the primary DB-BERT variant). In the following plots, this variant is denoted as DB-BERT0. We use the same settings for all tuning parameters that are shared between DB-BERT and DB-BERT0, except for the parameter determining the number of hints processed per episode. As DB-BERT0 tends to extract less hints than DB-BERT from the same documents, we set the number of hints processed per episode to ten (\(e=10\)). For zero-shot classification, we use a BART model, pre-trained on the MNLI benchmark.⁸ For question answering, we use a Roberta model, pre-trained on the SQUAD benchmark.⁹ For the confidence threshold, we use \(\theta =0.05\).

All baselines (with the exception of specialized tuning tools) are implemented in Python 3.7, using Pytorch 1.8.1 and (for the NLP-enhanced tuning baselines) the Huggingface Transformers library [54]. DB-BERT uses Google’s programmable search engine API¹⁰ to retrieve text documents. Also, DB-BERT uses the Double Deep Q-Networks [50] implementation from the Autonomous Learning Library¹¹ as reinforcement learning algorithm.

DB-BERT comes with a script that retrieves text documents via Google search and transforms them into the input format required by DB-BERT. For most of the following experiments, we use two document collections retrieved via the queries “Postgresql performance tuning hints” (issued on April 11, 2021) and “MySQL performance tuning hints” (issued on April 15, 2021). We included the first 100 Google results for each of the two queries into the corresponding document collection (accounting for a total of 1.3 MB of text for PostgreSQL and 2.4 MB of text for MySQL). The results are diverse and cover blog entries, forum discussions (e.g., on Database Administrators Stack Exchange¹²), as well as the online manuals from both database systems. We call the document collection for PostgreSQL Pg100 and the one for MySQL Ms100 in the following.

Figure 5 shows the distribution of parameter mentions and proposed value assignments in those document collections, generated via DB-BERT’s candidate hint extraction mechanism (see Sect. 5). Clearly, the distribution of hints over documents and parameters is non-uniform. For both database systems, few parameters are mentioned in multiple documents while most parameters are mentioned only in a single document. Similarly, there are a few assignments proposed by multiple sources. On the other side, most value assignments are proposed only once.

Table 4 shows the most frequently mentioned parameters for both PostgreSQL and MySQL. Parameters related to buffer size (e.g., shared_buffers for PostgreSQL and innodb_buffer_pool_size for MySQL) feature prominently among them. Besides that, parameters related to the degree of parallelism (e.g., the parameter max_parallel_workers_per_gather) or logging (e.g., max_wal_size) are mentioned frequently as well.

Two of the compared algorithms, namely DB-BERT and Prior-Main, use training before run time. Prior-Main uses natural language tuning hints, annotated with associated formulas, as training data. We use the same training samples and training parameters as in the prior work [43]. Consistent with the experimental setup in the latter paper, we apply Prior-Main, trained on PostgreSQL samples, to tune MySQL and Prior-Main, trained on MySQL samples, to tune PostgreSQL. The goal is to demonstrate that NLP-enhanced database tuning does not require system-specific, annotated samples.

Prior-Main does not support extracting benchmark-specific tuning hints from a fixed document collection, a disadvantage if the same document collection is used for tuning multiple benchmarks. To allow at least some degree of variability, we train the Prior-Main model separately for each of our five benchmark runs. This leads to slightly different extractions in each run. Training Prior-Main on the platform outlined in Sect. 9.1 took 417 s for MySQL samples and 393 for PostgreSQL samples.

DB-BERT does not use annotated tuning hints for training. Instead, it uses the database system itself for run time feedback during the training phase. Similar to Prior-Main, we train DB-BERT on Pg100 to tune MySQL and on Ms100 to tune PostgreSQL. We activate the masked mode during training (see Sect. 6), meaning that parameter names are masked. This avoids learning system-specific parameter names (which are useless in our experimental setup) and focuses attention on the sentence structure of tuning hints instead. The reward signal of DB-BERT (see Sects. 6 and 7) combines reward for successfully changing parameter values according to tuning hints (meaning that the corresponding values are valid) and for performance obtained. To measure performance, we use a synthetic database containing two tables with two columns containing consecutive numbers from 1 to 1,000,000. We use a simple count aggregation query joining both tables with an equality predicate. Reward for performance is scaled down by a factor of 100 to avoid specialization to this artificial benchmark (it merely serves to penalize particularly bad configurations such as setting the buffer pool size to a minimal value). Finally, we add a small reward bonus for setting parameter values that are within the same order of magnitude as the default setting (assuming that extreme deviations from default values are possible but less likely). DB-BERT’s training starts from the BERT base model [7] with 110 million parameters. All model parameters are tuned during training.

We trained DB-BERT for 5000 iterations on Pg100 and for 10,000 iterations on Ms100 (due to the larger number of hints in this collection). Training took 43 min for Pg100 and 84 min for Ms100. Figure 6 shows progress for Pg100 as a function of the number of training steps.

Note that, in contrast to the primary variant, DB-BERT0 does not require any training.

We compare DB-BERT against baselines on TPC-H (see Fig. 7), JOB (see Fig. 8 with a logarithmic y-axis), and TPC-C (see Fig. 9). We tune PostgreSQL and MySQL for 25 min per run. We use throughput as optimization metric for TPC-C and execution time for TPC-H. We show performance of the best configuration found (y-axis) as a function of optimization time (x-axis). In these and the following plots, we report the arithmetic average as well as the 20th and 80th percentile of five runs (using error bars to show percentiles). The plots contain one data point per baseline for every 30 s of tuning time (not displaying any data points before results for the first trial run have become available for the corresponding baseline). This means that data points in the plots do not necessarily align with the start and end of trial runs.

DDPG++ [49] is a database tuning approach, based on reinforcement learning. It was shown to be competitive with various other state-of-the-art tuning approaches [49]. However, the prior publication evaluates DDPG++ for a few tens of tuning parameters and allocates 150 iterations per tuning session. Here, we consider hundreds of parameters for tuning and aim at a tuning time frame that allows only few iterations. Clearly, within the allocated time frame, DDPG++ does not find solutions of comparable quality to DB-BERT. In particular for TPC-H, DDPG++ often tries parameter changes that decrease performance significantly (e.g., changes to optimizer cost constants triggering different join orders). Hence, performance of the best configuration found remains almost constant for DDPG++ (close to the one achieved via the default configuration, tried before the first iteration). DDPG++ could benefit from specifying parameter-specific value ranges to consider during tuning. For instance, increasing buffer pool size by an order of magnitude, compared to the default settings, is often beneficial. For optimizer cost constants (e.g., random_page_cost in PostgreSQL), doing so is however dangerous. Our goal is to show that such input can be partially substituted by information mined automatically from text.

Specializing tuning tools to specific database systems and workload types is another option to avoid costly exploration. We compare to two tuning tools (MySQLTuner and PgTuner) that are specialized to the tuned database systems. Those tuning tools use hard-coded rules to map properties of the target platform and workload to recommended settings. A first advantage of those tools is that they do not require any trial runs with default configuration (hence, they minimize tuning time among all compared tuning tools). Overall, they achieve excellent performance on TPC-H and TPC-C. This is to be expected as those are two of the most popular benchmarks for database management systems. Hence, it can be assumed that the rules used by these tools are optimized to work well for such standard benchmarks. On the other hand, run times on JOB are higher than the optimum by a multiple. This shows that hard-coded tuning rules have limitations when applied to non-standard tuning scenarios.

Prior-Simple and Prior-Main are the two most related baselines as both use tuning text as input, similar to DB-BERT. Prior-Simple uses a naive heuristic for translation. Applying this heuristic is fast and Prior-Simple is typically the first baseline to return results. However, it only extracts the recommendation to set checkpoint_completion_target to 0.9 in Pg100 and no recommendations in Ms100. Hence, it does not improve over the default configuration. Prior-Main performs significantly better. Due to small differences in training, extractions differ across different runs, leading to high variance. For instance, for Pg100, Prior-Main is able to extract a tuning hint that recommends setting shared_buffers to 25% of main memory in two out of five runs. This can lead to significant performance improvements, in particular for TPC-H. However, average performance is significantly below the optimum. As Prior-Main classifies all sentences in the document collection before aggregating tuning hints, its run time is significantly higher than the one of Prior-Simple.

Both DB-BERT variants find attractive tradeoffs between tuning time and result quality, comparing to generic (i.e., non-specialized) tuning tools. For instance, when tuning for TPC-H, DB-BERT finds settings that lead to significant performance advantages after less than 200 (PostgreSQL), respectively, less than 400 s (MySQL). More precisely, at that point, both DB-BERT variants find settings that increase main memory allocation (e.g., PostgreSQL’s shared_buffers parameter) or increase the degree of parallellism (e.g., PostgreSQL’s max_parallel_workers_per_gather parameter), motivated by hints extracted from text, leading to a significant drop in execution time.

Unlike DDPG++, DB-BERT uses tuning text as input that allows identifying the most relevant parameters and candidate values quickly. Compared to Prior-Simple and Prior-Main, it finds significantly better solutions in average. In particular for MySQL, Prior-Main typically fails to find solutions of comparable quality. Furthermore, the time taken by Prior-Main to analyze all documents is typically higher by a factor of two to three, compared to the time until DB-BERT produces a near-optimal solution (i.e., within one percent of DB-BERT’s final optimum). Tables 5 and 6 show configurations found by DB-BERT when tuning PostgreSQL. Despite extracting hints from the same document collection, DB-BERT is able to find benchmark-specific configurations.

Compared to system-specific tuning tools, the DB-BERT variants, in particular DB-BERT0, shine on JOB. Hard-coded tuning rules are not flexible enough to optimize performance beyond standard benchmarks. For instance, in the default configuration, PostgreSQL is hampered by sub-optimal choices made by its query optimizer. This is to be expected as the data of JOB is skewed, invalidating assumptions made by the optimizer while estimating cost of candidate plans (e.g., assuming independent predicates). DB-BERT and DB-BERT0 both extract tuning recommendations about PostgreSQL’s effective_cache_size parameter from text. This parameter represents assumptions of the PostgreSQL planner on the size of the disk cache, available for each query. Both DB-BERT variants set this parameter to a value of 64 for trial runs, following recommendations in text. This value is significantly below the default of 524288. The new setting discourages index scans and leads to different query plans. Further analysis shows that changing the setting for this parameter alone reduces execution time approximately by factor two. Therefore, changing this parameter setting seems to have a similar effect as disabling nested loop joins, a change recommended in the original paper introducing JOB [22]. Unlike recommendations to, e.g., increase buffer pool size, hints concerning the effective_cache_size parameter are relatively rare. Enabling DB-BERT to access the “long tail” of tuning recommendations by parsing a large collection of text documents pays off in this case.

In most cases, DB-BERT0 achieves similar performance to DB-BERT, despite the lack of a task-specific training phase. When tuning JOB on MySQL, DB-BERT0 even achieves significantly better results. An analysis of the logs shows that DB-BERT takes longer to find interesting parameter settings due to slightly more noisy extractions, causing DB-BERT to waste the initial trial runs with highly sub-optimal parameter settings that do not appear in text. As JOB requires most time per trial run, this delay prevents DB-BERT from trying efficient settings within the tuning time frame. A follow-up analysis shows that DB-BERT finds configurations with comparable performance to DB-BERT0 after around 2,000 s of tuning time. On the other hand, DB-BERT achieves better results when tuning MySQL for TPC-C. Here, DB-BERT0 consistently converges to a configuration that increases the amount of buffer space (innodb_buffer_pool_size) to 1 GB, while using default settings for other parameters. In contrast to that, DB-BERT changes settings for multiple parameters (e.g., parameter max_connections and parameter innodb_flush_log_at_trx_commit) for significantly higher throughput.

Altogether, DB-BERT0 is the most robust optimization tool across different systems and benchmarks. More precisely, the relative performance degradation of DB-BERT0 (i.e., the relative increase of run time, compared to the optimum, or relative decrease in throughput, compared to the optimum) never exceeds 34% across all scenarios. The degradation reaches its maximum for TPC-C on MySQL where DB-BERT0 only achieves 66% of the optimal throughput. However, all other baselines experience performance degradations of at least up to 93%, compared to the optimum (e.g., when optimizing JOB on MySQL). System-specific tuning tools work well for standard benchmarks but lack the ability to adapt to less common tuning scenarios. Exploiting both, information gained from text documents as well as information gathered via trial runs, gives DB-BERT advantages over methods that exploit only one of those two sources of information. DB-BERT performs similarly to DB-BERT0 in most cases but is slowed down by noisy text extractions in one of the tuning scenarios.

The best configurations for each benchmark and system, considering all runs and all DB-BERT variants, are reported online.¹³

We study the impact of different factors on tuning performance. First, we compare DB-BERT against two simplified variants in Fig. 10. We compare against a variant of DB-BERT that processes hints in document order (instead of prioritizing them as described in Sect. 5). Also, we compare against a variant that does not consider implicit hints (i.e., only hints where parameter names are explicitly mentioned). Clearly, both simplifications degrade tuning performance on TPC-H. Considering hints in document order prevents DB-BERT from tuning the most relevant parameters first. Discarding implicit hints reduces the total set of available hints.

Next, we study the impact of the input text. We replace Pg100, containing hundreds of generic tuning hints, by a single blog post.¹⁴ This post describes how to tune PostgreSQL specifically for TPC-H. Figure 5 compares performance with different input documents for all NLP-enhanced tuning baselines. While the performance of Prior-Simple does not change with the input text, the performance of Prior-Main degrades as we switch to the smaller document. Prior-Main benefits from large document collections as redundant hints can partially make up for imprecise extractions. For the smaller input document, it does not extract any hints. DB-BERT, however, benefits from more specialized tuning hints. Using benchmark-specific input text, it converges to near-optimal solutions faster and ultimately finds a slightly better solution (using a higher value for the shared_buffers parameter, compared to Table 5, as proposed in the blog entry).

Finally, we scale up the data size. Figure 12 reports results for TPC-H with scaling factor 10 (and using the TPC-H specific tuning text).¹⁵ Compared to Fig. 11, showing results for scaling factor one, it takes longer for DB-BERT to find near-optimal solutions. This is expected, as longer run times per benchmark evaluation reduce the number of DB-BERT’s iterations per time unit. Compared to other baselines, DB-BERT finds significantly better solutions again.