Database Tuning Research Articles

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

DB-BERT is a database tuning tool that exploits information gained via natural language analysis of manuals and other relevant text documents. It uses text to identify database system parameters to tune as well as recommended parameter values. DB-BERT applies large, pre-trained language models (specifically, the BERT model) for text analysis. During an initial training phase, it fine-tunes model weights in order to translate natural language hints into recommended settings. At run time, DB-BERT learns to aggregate, adapt, and prioritize hints to achieve optimal performance for a specific database system and benchmark. Both phases are iterative and use reinforcement learning to guide the selection of tuning settings to evaluate (penalizing settings that the database system rejects while rewarding settings that improve performance). In our experiments, we leverage hundreds of text documents about database tuning as input for DB-BERT. We compare DB-BERT against various baselines, considering different benchmarks (TPC-C and TPC-H), metrics (throughput and run time), as well as database systems (PostgreSQL and MySQL). The experiments demonstrate clearly that DB-BERT benefits from combining general information about database tuning, mined from text documents, with scenario-specific insights, gained via trial runs. The full source code of DB-BERT is available online at https://itrummer.github.io/dbbert/.

An Efficient Transfer Learning Based Configuration Adviser for Database Tuning

In recent years, a wide spectrum of database tuning systems have emerged to automatically optimize database performance. However, these systems require a significant number of workload runs to deliver a satisfactory level of database performance, which is time-consuming and resource-intensive. While many attempts have been made to address this issue by using advanced search optimizers, empirical studies have shown that no single optimizer can dominate the rest across tuning tasks with different characteristics. Choosing an inferior optimizer may significantly increase the tuning cost. Unfortunately, current practices typically adopt a single optimizer or follow simple heuristics without considering the task characteristics. Consequently, they fail to choose the most suitable optimizer for a specific task. Furthermore, constructing a compact search space can significantly improve the tuning efficiency. However, current practices neglect the setting of the value range for each knob and rely on a large number of workload runs to select important knobs, resulting in a considerable amount of unnecessary exploration in ineffective regions. To pursue efficient database tuning, in this paper, we argue that it is imperative to have an approach that can judiciously determine a precise space and search optimizer for an arbitrary tuning task. To this end, we propose OpAdviser, which exploits the information learned from historical tuning tasks to guide the search space construction and search optimizer selection. Our design can greatly accelerate the tuning process and further reduce the required workload runs. Given a tuning task, OpAdviser learns the geometries of search space, including important knobs and their effective regions, from relevant previous tasks. It then constructs the target search space from the geometries according to the on-the-fly task similarity, which allows for adaptive adjustment of the target space. OpAdviser also employs a pairwise ranking model to capture the relationship from task characteristics to optimizer rankings. This ranking model is invoked during tuning and predicts the best optimizer to be used for the current iteration. We conduct extensive evaluations across a diverse set of workloads, where OpAdviser achieves 9.2% higher throughput and significantly reduces the number of workload runs with an average speedup of ~3.4x compared to state-of-the-art tuning systems.

Can Large Language Models Predict Data Correlations from Column Names?

Recent publications suggest using natural language analysis on database schema elements to guide tuning and profiling efforts. The underlying hypothesis is that state-of-the-art language processing methods, so-called language models, are able to extract information on data properties from schema text. This paper examines that hypothesis in the context of data correlation analysis: is it possible to find column pairs with correlated data by analyzing their names via language models? First, the paper introduces a novel benchmark for data correlation analysis, created by analyzing thousands of Kaggle data sets (and available for download). Second, it uses that data to study the ability of language models to predict correlation, based on column names. The analysis covers different language models, various correlation metrics, and a multitude of accuracy metrics. It pinpoints factors that contribute to successful predictions, such as the length of column names as well as the ratio of words. Finally, the study analyzes the impact of column types on prediction performance. The results show that schema text can be a useful source of information and inform future research efforts, targeted at NLP-enhanced database tuning and data profiling.

FASTune: Towards Fast and Stable Database Tuning System with Reinforcement Learning

Configuration tuning is vital to achieving high performance for a database management system (DBMS). Recently, automatic tuning methods using Reinforcement Learning (RL) have been explored to find better configurations compared with database administrators (DBAs) and heuristics. However, existing RL-based methods still have several limitations: (1) Excessive overhead due to reliance on cloned databases; (2) trial-and-error strategy may produce dangerous configurations that lead to database failure; (3) lack the ability to handle dynamic workload. To address the above challenges, a fast and stable RL-based database tuning system, FASTune, is proposed. A virtual environment is proposed to evaluate configurations which is an equivalent yet more efficient scheme than the cloned database. To ensure stability during tuning, FASTune adopts an environment proxy to avoid dangerous configurations. In addition, a Multi-State Soft Actor–Critic (MS-SAC) model is proposed to handle dynamic workloads, which utilizes the soft actor–critic network to tune the database according to workload and database states. The experimental results indicate that, compared with the state-of-the-art methods, FASTune can achieve improvements in performance while maintaining stability in the tuning.

LlamaTune

Tuning a database system to achieve optimal performance on a given workload is a long-standing problem in the database community. A number of recent works have leveraged ML-based approaches to guide the sampling of large parameter spaces (hundreds of tuning knobs) in search for high performance configurations. Looking at Microsoft production services operating millions of databases, sample efficiency emerged as a crucial requirement to use tuners on diverse workloads. This motivates our investigation in LlamaTune, a tuner design that leverages domain knowledge to improve the sample efficiency of existing optimizers. LlamaTune employs an automated dimensionality reduction technique based on randomized projections, a biased-sampling approach to handle special values for certain knobs, and knob values bucketization, to reduce the size of the search space. LlamaTune compares favorably with the state-of-the-art optimizers across a diverse set of workloads. It identifies the best performing configurations with up to 11X fewer workload runs, and reaching up to 21% higher throughput. We also show that benefits from LlamaTune generalize across both BO-based and RL-based optimizers, as well as different DBMS versions. While the journey to perform database tuning at cloud-scale remains long, LlamaTune goes a long way in making automatic DBMS tuning practical at scale.

K2vtune: Automatic Database Tuning with Knob Vector Representation

K2vtune: Automatic Database Tuning with Knob Vector Representation

Hbox {CDBTune}^{+}: An efficient deep reinforcement learning-based automatic cloud database tuning system

Configuration tuning is vital to optimize the performance of a database management system (DBMS). It becomes more tedious and urgent for cloud databases (CDB) due to diverse database instances and query workloads, which make the job of a database administrator (DBA) very difficult. Existing solutions for automatic DBMS configuration tuning have several limitations. Firstly, they adopt a pipelined learning model but cannot optimize the overall performance in an end-to-end manner. Secondly, they rely on large-scale high-quality training samples which are hard to obtain. Thirdly, existing approaches cannot recommend reasonable configurations for a large number of knobs to tune whose potential values live in such high-dimensional continuous space. Lastly, in cloud environments, existing approaches can hardly cope with the changes of hardware configurations and workloads, and have poor adaptability. To address these challenges, we design an end-to-end automatic CDB tuning system, {texttt {CDBTune}}^{+}, using deep reinforcement learning (RL). {texttt {CDBTune}}^{+} utilizes the deep deterministic policy gradient method to find the optimal configurations in a high-dimensional continuous space. {texttt {CDBTune}}^{+} adopts a trial-and-error strategy to learn knob settings with a limited number of samples to accomplish the initial training, which alleviates the necessity of collecting a massive amount of high-quality samples. {texttt {CDBTune}}^{+} adopts the reward-feedback mechanism in RL instead of traditional regression, which enables end-to-end learning and accelerates the convergence speed of our model and improves the efficiency of online tuning. Besides, we propose effective techniques to improve the training and tuning efficiency of {texttt {CDBTune}}^{+} for practical usage in a cloud environment. We conducted extensive experiments under 7 different workloads on real cloud databases to evaluate {texttt {CDBTune}}^{+}. Experimental results showed that {texttt {CDBTune}}^{+} adapts well to a new hardware environment or workload, and significantly outperformed the state-of-the-art tuning tools and DBA experts.

The case for NLP-enhanced database tuning

A large body of knowledge on database tuning is available in the form of natural language text. We propose to leverage natural language processing (NLP) to make that knowledge accessible to automated tuning tools. We describe multiple avenues to exploit NLP for database tuning, and outline associated challenges and opportunities. As a proof of concept, we describe a simple prototype system that exploits recent NLP advances to mine tuning hints from Web documents. We show that mined tuning hints improve performance of MySQL and Postgres on TPC-H, compared to the default configuration.

Tun-OCM: A model-driven approach to support database tuning decision making

Database tuning is a task executed by Database Administrators (DBAs) based on their practical experience and on tuning systems, which support DBA actions towards improving the performance of a database system. It is notoriously a complex task that requires precise domain knowledge about possible database configurations. Ideally, a DBA should keep track of several Database Management Systems (DBMS) parameters, configure data structures, and must be aware about possible interferences among several database (DB) configurations. We claim that an automatic tuning system is a decision support system and DB tuning may also be seen as a configuration management task. Therefore, we may characterize it by means of a formal domain conceptualization, benefiting from existing control practices and computational support in the configuration management domain. This work presents Tun-OCM, a conceptual model represented as a well-founded ontology, that encompasses a novel characterization of the database tuning domain as a configuration management conceptualization to support decision making. We develop and represent Tun-OCM using the CM-OPL methodology and its underlying language. The benefits of Tun-OCM are discussed by instantiating it in a real scenario.

An Approach to Fine Tuning Database Performance in Application Software

Database tuning is crucial step to enhance the performance of the application software. There are many tools available in Microsoft to evaluate the performance of the stored procedures and identify them. This paper presents a comparative performance of actual and tuned sample stored procedures in SQL Server 2008 R2 of Application software (Microsoft). The results showed there is a marginal gain in the efficiency after the database tuning.

A Session-based Approach to Autonomous Database Tuning

A Session-based Approach to Autonomous Database Tuning

Feedback control loop design for workload change detection in self-tuning NoSQL wide column stores

Database management systems are the main part of information systems that the size and complexity of these systems are increased in recent years. Due to the growing complexity of DBMSs, database administrators (DBAs) face increasingly more problems and challenges, and so managing these systems are difficult and laborious. More over the main part of the total cost of ownership includes the cost of expert database administrator who can manage these large and complicated systems. Autonomic database, by providing self-management functionality, leads to a reduction in the total cost of ownership for the database system. Self-management decisions such as automated schema database tuning are dependent on the database workload. Therefore, one of the important issues in realizing the database automated tuning is workload monitoring and analysis for changes detection and schema re-tuning with this changes. In this paper, a feedback control loop is designed for continuous monitoring and light-weight workload analysis in NoSQL wide column stores. This loop describes a design pattern for the self-tuning feature and it is used to detect workload changes that are necessary for the automated schema database re-tuning. Our concept is based on workload model construction using reconfigurable colored petri-net model. The results of the experiments show the effectiveness of the proposed approach in discovering significant workload changes.

QTune

Database knob tuning is important to achieve high performance (e.g., high throughput and low latency). However, knob tuning is an NP-hard problem and existing methods have several limitations. First, DBAs cannot tune a lot of database instances on different environments (e.g., different database vendors). Second, traditional machine-learning methods either cannot find good configurations or rely on a lot of high-quality training examples which are rather hard to obtain. Third, they only support coarse-grained tuning (e.g., workload-level tuning) but cannot provide fine-grained tuning (e.g., query-level tuning). To address these problems, we propose a query-aware database tuning system QTune with a deep reinforcement learning (DRL) model, which can efficiently and effectively tune the database configurations. QTune first featurizes the SQL queries by considering rich features of the SQL queries. Then QTune feeds the query features into the DRL model to choose suitable configurations. We propose a Double-State Deep Deterministic Policy Gradient (DS-DDPG) model to enable query-aware database configuration tuning, which utilizes the actor-critic networks to tune the database configurations based on both the query vector and database states. QTune provides three database tuning granularities: query-level, workload-level, and cluster-level tuning. We deployed our techniques onto three real database systems, and experimental results show that QTune achieves high performance and outperforms the state-of-the-art tuning methods.

Selecting and tuning the optimal query form of different SQL commands

This paper presents the proposed approach of SQL tuning plan and tool in database systems based on experiments using testing data workload in Oracle 11g XE; to get an optimal execution plan for a given SQL, statements based on process time consumed and statistics about the object related to tables are referenced in the statement. These statistics include the SQL command, running time consumption, tables themselves, their indexes, access methods, and other derived objects. The quality of final execution plan depends primarily on the quality of the information found about the SQL command to improve a poorly-performed query. This work contributes to research knowledge by proposing necessary knowledge and information about database tuning using a useful tool to evaluate SQL commands. The proposed approach helps researchers, database administrators and developers to assess the impact of their decisions in the selection of the optimal SQL statement form. Oracle 11g XE is taken for the experimentation purpose.

Improvements in Serbian Speech Recognition Using Sequence-Trained Deep Neural Networks

This article presents the recent improvements in Serbian speech recognition that were obtained by using contemporary deep neural networks based on sequence-discriminative training to train robust acoustic models. More specifically, several variants of the new large vocabulary continuous speech recognition (LVCSR) system are described, all based on the lattice-free version of the maximum mutual information (LF-MMI) training criterion. The parameters of the system were varied to achieve best possible word error rate (WER) and character error rate (CER), using the largest speech database for Serbian in existence and the best n-gram based language model made for general purposes. In addition to tuning the neural network itself – its layers, complexity, layer splicing and more – other language-specific optimizations were explored, such as the usage of accent-specific vowel phoneme models, and its combination with pitch features to produce the best possible results. Finally, speech database tuning was tested as well – artificial database expansion by modifying speech speed in utterances, as well as volume scaling in an attempt to improve speech variability. The results suggest that 8-layer deep neural network with moderately sized 625-neuron layers works best in the given environment, without the need for speech database augmentation or volume adjustments, and that pitch features in combination with the introduction of accented vowel models provide the best performance out of all experiments.

TUNING DIFFERENT TYPES OF COMPLEX QUERIES USING THE APPROPRIATE INDEXES IN PARALLEL/DISTRIBUTED DATABASE SYSTEMS

In this paper, we discuss the most powerful techniques of tuning parallel/distributed databases.As in engineering, database tuning becomes an inescapable part of big projects since the conception phase ofresearch projects. The needs of companies including big data have increased to databases optimization.Systems that not take into account the optimization rules become heavy after five years of their production;these reasons were of a paramount of importance to prepare this paper. Indexing is the most suitable way tooptimize database systems, further one of the top ways of optimizing index is the application ofparallelization. In this paper, we will discuss parallelization and we will practice it with different complexqueries and sub-queries using different types of indexes; then we will compare the results gotten from eachindex. To top it all, the most suitable interference between the major types of index: B*Tree index, Bitmapindex, composite parallel index, local parallel index and global parallel index.

Analisis Alat Bantu Tuning Fisikal Basis Data pada Sql Server 2008

Nowadays every company has been faced with a business competition that requires the company to survive and be superior to its competitors. One strategy used by many companies is to use information technology to run their business processes. The use of information technology would require a storage which commonly referred to as a database to store and process data into useful information for the company. However, it was found that the greater the amount of data in the database, then the speed of the resulting process will decrease because the time needed to access the data will be much longer. The long process of data can cause a decrease in the company’s performance and the length of time needed to make decisions so that this can be a challenge to achieve the company’s competitive advantage. In this study performed an analysis of technique to improve the performance of the database system used by the company to perform tuning on SQL Server 2008 database physically. The purpose of this study is to improve the performance of the database used by speeding up the time it takes when doing query processing. The research methodology used was the method of analysis such as literature studies, analysis of the process and the workings of tuning tools that already exist in SQL Server 2008, and evaluation of applications that have been created, and also tuning methods that include query optimization and create index. The results obtained from this study is an evaluation of the physical application tuning tools that can integrate database functionality of other tuning tools such as SQL Profiler and Database Tuning Advisor.

Molecule database framework: a framework for creating database applications with chemical structure search capability

BackgroundResearch in organic chemistry generates samples of novel chemicals together with their properties and other related data. The involved scientists must be able to store this data and search it by chemical structure. There are commercial solutions for common needs like chemical registration systems or electronic lab notebooks. However for specific requirements of in-house databases and processes no such solutions exist. Another issue is that commercial solutions have the risk of vendor lock-in and may require an expensive license of a proprietary relational database management system. To speed up and simplify the development for applications that require chemical structure search capabilities, I have developed Molecule Database Framework. The framework abstracts the storing and searching of chemical structures into method calls. Therefore software developers do not require extensive knowledge about chemistry and the underlying database cartridge. This decreases application development time.ResultsMolecule Database Framework is written in Java and I created it by integrating existing free and open-source tools and frameworks. The core functionality includes:• Support for multi-component compounds (mixtures)• Import and export of SD-files• Optional security (authorization)For chemical structure searching Molecule Database Framework leverages the capabilities of the Bingo Cartridge for PostgreSQL and provides type-safe searching, caching, transactions and optional method level security. Molecule Database Framework supports multi-component chemical compounds (mixtures).Furthermore the design of entity classes and the reasoning behind it are explained. By means of a simple web application I describe how the framework could be used. I then benchmarked this example application to create some basic performance expectations for chemical structure searches and import and export of SD-files.ConclusionsBy using a simple web application it was shown that Molecule Database Framework successfully abstracts chemical structure searches and SD-File import and export to simple method calls. The framework offers good search performance on a standard laptop without any database tuning. This is also due to the fact that chemical structure searches are paged and cached. Molecule Database Framework is available for download on the projects web page on bitbucket: https://bitbucket.org/kienerj/moleculedatabaseframework.

QUERY TUNING IN ORACLE DATABASE

Oracle database is well suited to use for managing and exchanging of corporate data, especially for companies that have large-scale databases. Even though Oracle can work best, however, there are conditions where Oracle does not perform well and consume more time to perform the query transaction. This is when the database tuning process is needed. This process will solve the problems of low performance and high execution time when accessing data in the database. This study aims to propose an improvement process of query tuning that can reduce queries execution time. The cause of the problems can be discovered by reviewing the source code, List of Value (LOV) and view on the current system. To enhance the query, existing query scripts need to be modified to fit the tuning process. As an evaluation on the effectiveness of the proposed query tuning process, a comparison of study is done. The result of the study shows that before the tuning process, a system takes 156 milliseconds to execute the query and after implementing the proposed query tuning process, the execution time decreases to less than one millisecond. It means hundred times performance improvement on the query execution. The outcome of the study proved that the query execution time decreased after implementing the proposed query tuning process in the system.

Fuzzy Controlled Architecture for Performance Tuning of Database Management System

Management Systems deliver higher performance only when they are properly tuned. Database tuning is complicated due to the fact that several conflicting tuning parameters have to be adjusted simultaneously for a variety of workload types and the highly unpredictable traffic patterns. The Database Administrator(DBA) has to be an expert and using his experience and expertise must judiciously decide the extent of tuning of the most important tuning factors so as to ensure the required level of performance in terms of response time and throughput. The process of tuning being complex and to keep the cost of ownership low, it is desirable to build self tuning database systems. In this paper, a new tuning architecture based on fuzzy logic is presented, where in, the control action is expressed in linguistic terms. In this system the key performance indicators are fuzzified, appropriate fuzzy rules are employed to estimate the extent of tuning required for a few important tuning parameters. After defuzzification, a control action is initiated to scale up the system performance. The experimental results obtained for different workload types and the user load, indicate that it is possible to significantly improve the query response time using this technique.