An Analysis on Automatic Performance Optimization in Database Management Systems

Database knob tuning is a long-standing challenge in the database community, aimed at enhancing the performance of database management systems (DBMSs) by minimizing latency and maximizing throughput. Manual tuning, which relies heavily on human expertise, is often inefficient and impractical for large-scale or dynamic deployments. Recent work has explored automating this process using machine learning (ML) and large language models (LLMs). However, existing methods typically require hundreds of workload replays or rely on extensive training data, leading to low tuning efficiency or high preparation costs. Moreover, they also risk generating invalid configurations that can degrade performance or even crash the database. To address these limitations, we introduce AgentTune, the first agent-based knob tuning framework powered by LLMs, designed for efficiency, adaptability, and reliability. AgentTune decomposes the tuning process into four specialized agents: Workload Analyzer, Knob Selector, Range Pruner, and Configuration Recommender, each responsible for a distinct subtask. These agents collaborate through structured prompt chaining. AgentTune first analyzes the input workload to identify impactful knobs and reconstructs their valid ranges to reduce the search space. It then employs a tree-based search strategy to efficiently explore the configuration space and identify suitable knob values. We conduct extensive evaluations across diverse workloads (public benchmarks and real-world workloads), metrics (latency and throughput), DBMSs (PostgreSQL, MySQL, and TiDB), hardware environments, and database scales. Experimental results demonstrate that, compared to existing baselines, AgentTune is able to identify superior configurations using significantly fewer workload replays. Furthermore, AgentTune rarely generates invalid configurations during the tuning process, ensuring reliability and suitability for real-world deployments.

The development of information technologies requires the processing of an increasing amount of information. A great number of tools and DBMSs for working with big data have been created. Examples of DBMS are: MS SQL Server, Oracle Database, SciDB, VoltDB, PostgreSQL XL, Clusterix, etc. Most systems of the BigData class require serious computing power to ensure acceptable performance. This article discusses the TPC-H test and 22 of its queries, the execution of which allows you to analyze the performance of database management systems (DBMS). PostgreSQL, MySQL, and Microsoft SQL Server DBMS are considered and their performance is investigated based on TPC-H test queries with database (DB) of various sizes in the DbVisualizer Free version 11.0.6.

In recent years, as performance and capacity of main and external memory grow, performance of database management systems (DBMSes) on certain kinds of queries is more determined by raw CPU speed. Currently, PostgreSQL uses the interpreter to execute SQL queries. This yields an overhead caused by indirect calls to handler functions and runtime checks, which could be avoided if the query were compiled into native code "on-the-fly", i.e. just-in-time (JIT) compiled: at run time the specific table structure is known as well as data types and built-in functions used in the query as well as the query itself. This is especially important for complex queries, performance of which is CPU-bound. We’ve developed a PostgreSQL extension that implements SQL query JIT compilation using LLVM compiler infrastructure. In this paper we show how to implement JIT compilation to speed up sequential scan operator (SeqScan) as well as expressions in WHERE clauses. We describe some important optimizations that are possible only with dynamic compilation, such as precomputing tuple attributes offsets only for attributes used by the query. We also discuss the maintainability of our extension, i.e. the automation for translating PostgreSQL backend functions into LLVM IR, using the same source code both for our JIT compiler and the existing interpreter. Currently, with LLVM JIT we achieve up to 5x speedup on synthetic tests as compared to original PostgreSQL interpreter.

The upcoming generation of computer hardware poses several new challenges for database developers and engineers. Software in general and database management systems (DBMSs) in particular will no longer benefit from performance gains of future hardware due to increase clock speed, as it was the case for the last 35 years; instead, the number of cores per CPU will increase steadily. Today’s approach is to run each query on a single core or only a few different cores using parallel query execution. This approach suffers from several problems (e.g. contention problem) and therefore leads to poor speed up and scale up behavior. These observations open several important research questions on how to use the new multi-core CPU architecture for improving the overall performance of DBMSs. This paper outlines our approach for query processing on multi-core CPU architectures. We present an abstract architecture view for multi-core CPUs, meta operators to control and to interact with the hardware, and a new query operator model that makes use of the meta operators to control the parallel execution of a query over different cores. We illustrate how each of these parts fits in our framework for query processing on multi-core architectures.

In order to guarantee the stability and reliability of database performance, database monitoring system and program are necessary. The previous commercial database monitoring servers and programs make database overhead and degrade performance of database management system. And there were no alert message or warning message for database administrators in the previous commercial database monitoring system.

The problem of characterizing the number of granules (or blocks) accessed by a transaction is important in modeling the performance of database management systems and other applications. Different expressions for this quantity have appeared in the literature under different probabilistic assumptions. These expressions along with one new result are presented with a uniform notation and a clear statement of the assumptions underlying each. The partial order relating the predictions of the expected number of granules accessed is presented.

Performance prediction and adaptation for database management system workload using Case-Based Reasoning approach

This thesis studies the automatic design and optimization of high-performing robust controllers for mobile robots using exclusively on-board resources. Due to the often large parameter space and noisy performance metrics, this constitutes an expensive optimization problem. Population-based learning techniques have been proven to be effective in dealing with noise and are thus promising tools to approach this problem. We focus this research on the Particle Swarm Optimization (PSO) algorithm, which, in addition to dealing with noise, allows a distributed implementation, speeding up the optimization process and adding robustness to failure of individual agents. In this thesis, we systematically analyze the different variables that affect the learning process for a multi-robot obstacle avoidance benchmark. These variables include algorithmic parameters, controller architecture, and learning and testing environments. The analysis is performed on experimental setups of increasing evaluation time and complexity: numerical benchmark functions, high-fidelity simulations, and experiments with real robots. Based on this analysis, we apply the distributed PSO framework to learn a more complex, collaborative task: flocking. This attempt to learn a collaborative task in a distributed manner on a large parameter space is, to our knowledge, the first of such kind. In addition, we address the problem of noisy performance evaluations encountered in these robotic tasks and present a %new distributed PSO algorithm for dealing with noise suitable for resource-constrained mobile robots due to its low requirements in terms of memory and limited local communication.

Apache Hadoop system is a software framework with the capability to process large-scale datasets across a cluster of distributed machines using MapReduce programming model. However, there are two main challenges for system administrators to manage the Hadoop system, (1) system administrators are difficult to tune the parameters appropriately since the behaviors and characteristics of large-scale distributed systems are too complicated, (2) there are dozens of configuration parameters affecting the system performance which makes the configuration parameters tuning task becomes troublesome. In this paper, we focus on optimizing the Hadoop MapReduce job performance by tuning configuration parameters, and then we propose an analytical method to help system administrators choose approximately optimal configuration parameters depending on the characteristics of each application. Our approach has two key phases: prediction and optimization phase. The prediction phase is to estimate the performance of a MapReduce job, whereas the optimization phase is to search the approximately optimal configuration parameters strategically by invoking the predictor repeatedly. In our evaluation results, our work can help system administrators to improve the performance about 2X to 8X better than traditional methods.

In today's data server environments, multiple types of workloads can be present in a system simultaneously. Workloads may have different levels of business importance and unique performance goals. An autonomic workload management system controls the flow of the workloads to help the database management system (DBMS) meet the performance goals. A task of the autonomic workload management system is to prevent congestion in the DBMS, which can result in severe degradation in overall system performance. Autonomic workload management should detect that a system is becoming congested and then act to restore normal system operation. In this paper, we describe an approach to identify a set of database monitor metrics that can serve as indicators for potential congestion in a specific scenario. We present experiments to illustrate two cases of congestion in a DB2® DBMS and use our approach to derive the indicators.

Database management system (DBMS) configuration debugging, e.g., diagnosing poorly configured DBMS knobs and generating troubleshooting recommendations, is crucial in optimizing DBMS performance. However, the configuration debugging process is tedious and, sometimes challenging, even for seasoned database administrators (DBAs) with sufficient experience in DBMS configurations and good understandings of the DBMS internals (e.g., MySQL or Oracle). To address this difficulty, we propose Andromeda, a framework that utilizes large language models (LLMs) to enable automatic DBMS configuration debugging. Andromeda serves as a natural surrogate of DBAs to answer a wide range of natural language (NL) questions on DBMS configuration issues, and to generate diagnostic suggestions to fix these issues. Nevertheless, directly prompting LLMs with these professional questions may result in overly generic and often unsatisfying answers. To this end, we propose a retrieval-augmented generation (RAG) strategy that effectively provides matched domain-specific contexts for the question from multiple sources. They come from related historical questions, troubleshooting manuals and DBMS telemetries, which significantly improve the performance of configuration debugging. To support the RAG strategy, we develop a document retrieval mechanism addressing heterogeneous documents and design an effective method for telemetry analysis. Extensive experiments on real-world DBMS configuration debugging datasets show that Andromeda significantly outperforms existing solutions.

Optimal Design of a Reconfigurable Machine Tool Considering Machine Configurations and Configuration Changes

With the rapid development of technology, especially in the industrial sector and the widespread use of the internet in Indonesia, effective data storage media is needed. Like MySQL, database management systems (DBMS) have become an effective solution for managing and processing data. As a type of DBMS, MySQL has demonstrated the ability to support application development by both experienced and novice users. On the other hand, in the ever-evolving digital era, web and mobile applications have become important, and Node.js and REST APIs have become the basis for creating effective web applications. Technological advances have also produced an alternative to relationless data storage, known as NoSQL. MongoDB, for example, offers flexibility and fast performance when storing data in JSON document format. Load testing performance analysis between MySQL and MongoDB on Node.js applications is very important because they have different data models and workload types. This is an important technique for measuring and evaluating application performance. Search speed, data storage, and query complexity are influenced by the MySQL and MongoDB NoSQL relational data models. The goal of this load testing performance analysis is to gain an understanding of how MySQL and MongoDB perform when faced with different workloads, particularly in the context of Node.js applications. The results of this analysis are expected to provide a clear picture of the advantages and disadvantages of each database when used with Node.js applications, so that developers and system architects can make wise, data-driven decisions when they use Node.js applications.

Self-tuning of Database Management Systems(DBMS) offers important advantages such as improved performance, reduced Total Cost of Ownership(TCO), eliminating the need for an exert Database Administrator(DBA) and improved business prospects. Several techniques have been proposed by researchers and the database vendors to self-tune the DBMS. However, the research focus was confined to physical tuning techniques and the algorithms used in existing methods for self-tuning of memory need analysis of large statistical data. As result, these approaches are not only computationally expensive but also do not adapt well to highly unpredictable workload types and user-load patterns. Hence, in this paper a fuzzy based self-tuning approach has been proposed wherein, three inputs namely, Buffer-Hit-Ratio, Number of Users and Database size are extracted from the Database management system as sensor inputs that indicate degradation in performance and key tuning parameters called the effectors are altered according to the fuzzy-rules. The fuzzy rules are framed after a detailed study of impact of each tuning parameter on the response-time of user queries. The proposed self-tuning architecture is based on Monitor, Analyze, Plan and Execute(MAPE) feedback control loop framework [1] and has been tested under various workload types. The results have been validated by comparing the performance of the proposed self-tuning system with the auto-tuning feature of commercial database systems. The results show significant improvement in performance under various workload-types, user-load variations.

The effect of physical configuration on the performance of laboratory-scale oxidation ponds