Performance Analysis of PostgreSQL, MySQL, Microsoft SQL Server Systems Based on TPC-H Tests

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.

Maintaining database management systems under optimum condition is crucial for any enterprise application as it affects the overall system performance. However, the optimization process is a non-trivial task even for experienced database administrators due to many underlying challenges. Due to the importance of database optimization, this domain has been researched for the past several years. In this analysis starting off with discussing the importance of an optimum database for the overall performance, then we focus on the reasons behind the poor performance of database management systems. Database optimization is divided under two main categories as Physical design based optimization and Configuration parameter based optimization. While both of these will be discussed, the priority is given for the Configuration parameter optimization. Configuration based optimization comes with its own set of challenges as of 1) Large configuration parameter space, 2) Interdependency of configuration parameters, and 3) Configuration change with different types of workloads. These challenges will be elaborated while carrying out an analysis on the different approaches used in preexisting work to overcome the challenges. Finally, the paper would focus on foundations of optimization techniques which can be utilized for the configuration based database optimization process.

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.

Very powerful data base management systems (DBMS) now exist which allow medical personnel access to patient record data bases. DBMS's make it easy to retrieve either complete or abbreviated records of patients with similar characteristics. In addition, statistics on data base records are immediately accessible. However, the price of this power is a large computer with the inherent problems of access, response time, and reliability. If a general purpose, time-shared computer is used to get this power, the response time to a request can be either rapid or slow, depending upon loading by other users. Furthermore, if the computer is accessed via dial-up telephone lines, there is competition with other users for telephone ports. If either the DBMS or the host machine is replaced, the medical users, who are typically not sophisticated in computer usage, are forced to learn the new system. Microcomputers, because of their low cost and adaptability, lend themselves to a solution of these problems. A microprocessor-based intelligent terminal has been designed and implemented at the USAF School of Aerospace Medicine to provide a transparent interface between the user and his data base. The intelligent terminal system includes multiple microprocessors, floppy disks, a CRT terminal, and a printer. Users interact with the system at the CRT terminal using menu selection (framing). The system translates the menu selection into the query language of the DBMS and handles all actual communication with the DBMS and its host computer, including telephone dialing and sign on procedures, as well as the actual data base query and response. Retrieved information is stored locally for CRT display, hard copy production, and/or permanent retention. Microprocessor-based communication units provide security for sensitive medical data through encryption/decryption algorithms and high reliability error detection transmission schemes. Highly modular software design permits adapation to a different DBMS and/or host computer with only minor localized software changes. Importantly, this portability is completely transparent to system users. Although the terminal system is independent of the host computer and its DBMS, it has been linked to a UNIVAC 1108 computer supporting MRI's SYSTEM 2000 DBMS.

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.

The article discusses a special type of integrity in databases – ancestral multiplicity integrity (AM-integrity), a concept introduced by the authors at the conceptual–ontological level within “entity–relationship” models. In this paper, this concept is extended to the relational model for the purpose of practical application in creating databases. Related notions are examined, including the lineage of ascending ancestry, the relation of ancestral multiplicity, and the corresponding integrity constraint. Transitioning to the relational model requires consideration of primary and foreign keys, referential integrity, and table triggers. As a universal means of ensuring integrity in the relational environment, the paper proposes an approach based on database triggers. These triggers detect and block insert and update operations that would lead to violations of integrity. The need for procedural programming of triggers is noted, along with the challenges of transferring data between different database management systems due to variations in procedural programming languages. An alternative approach is suggested, based on leveraging built-in mechanisms for maintaining referential integrity, in which ancestral multiplicity integrity is ensured as part of referential integrity. The specific solution depends on the use of simple or composite, natural or surrogate keys, as well as on the technical limitations of the database implementation environment. In some cases, redundant key components may need to be introduced to track ancestor instances along ancestral lineages. Test examples of relational models are provided for different relational systems (complete implementations in MySQL and MariaDB, and partial implementations in PostgreSQL, MS SQL Server, and Oracle Database).

In the article, basic information about databases are presented, including defining the most crucial notions and referring to the history of emerging standards in that field of IT knowledge. Starting from Codd’s database criteria, the differences between a database and DataBase Management System (DBMS) are elaborated on. The main functions of DBMS are also clarified. Upon the short classification of SQL, which is used to manipulate databases, the main differences and similarities between different software are presented, focusing on commercial products (MS SQL Server, Oracle) with some references to Open Source systems (MySQL, PostgreSQL). The comparison was made in three areas: data type, commands and SQL function. In the final part of the article, dissertations from the MILA College (currently Warsaw School of Computer Science) are presented. They all refer to the transformation of SQL code between particular dialects as well as creating packages to make the job of database makers easier in a multiproduct environment. 1 Mgr inz. Dariusz Olczyk jest wykladowcą w Warszawskiej Wyzszej Szkole Informatyki. ZESZYTY NAUKOWE 77-91

This paper presents an original approach to parallel processing of very large databases by means of encapsulation of partitioned parallelism into open-source database management systems (DBMSs). The architecture and methods for implementing a parallel DBMS through encapsulation of partitioned parallelism into PostgreSQL DBMS are described. Experimental results that confirm the effectiveness of the proposed approach are presented.

The development of the interval analysis theory and its applications in solving applied problems led to the need to collect, store and extract efficiently interval-significant data in relational databases. Modern relational database management systems (DBMS) either allow to work with built-in data types which however are not friendly for use or provide the ability to create your own user-defined data types in several ways. The article describes the creation of a user-defined data type iInterval, functions and aggregates to work with interval data in MS SQL Server using the Common Language Runtime (CLR).

SQL Server 2014 is the latest release of Microsoft's enterprise-class database management system (DBMS). As the name implies, a DBMS is a tool designed to manage, secure, and provide access to data stored in structured collections in databases. Transact-SQL (T-SQL) is the language that SQL Server speaks. T-SQL provides query and data-manipulation functionality, data definition and management capabilities, and security administration tools to SQL Server developers and administrators. To communicate effectively with SQL Server, you must have a solid understanding of the language. In this chapter, you begin exploring T-SQL on SQL Server 2014.

SQL Server 2008 is the latest release of Microsoft’s enterprise-class database management system (DBMS). As the name implies, a DBMS is a tool designed to manage, secure, and provide access to data stored in structured collections within databases. T-SQL is the language that SQL Server speaks. T-SQL provides query and data manipulation functionality, data definition and management capabilities, and security administration tools to SQL Server developers and administrators. To communicate effectively with SQL Server, you must have a solid understanding of the language. In this chapter, we will begin exploring T-SQL on SQL Server 2008.KeywordsData DomainStructure Query LanguageDatabase ObjectAmerican National Standard InstituteDeclarative LanguageThese keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

The focus of the present paper is on the data‐knowledge integration problem, whereby data and knowledge can be made radically more useful within the same framework. Most tools for developing knowledge‐based systems do not integrate well with data bases. This lack of integration is usually hampered by slow data base queries or by overly limited data base interfaces. On the other hand, since data base management systems are already well developed, leveraging knowledge‐based systems with data base management systems would provide significant advantages like speed, security, and portability across data formats (e.g. easy conversion between dBase and ASCII formats). In the present paper, we describe a novel approach to the data‐knowledge integration problem. This approach entails the simple but powerful extension of a data base system to increase its ability to represent and manipulate knowledge. We describe Kbase, an environment for developing knowledge‐based systems using facilities and extended constructs of the familiar dBase III data base programming environment. At the end, we use an implemented example, IPDOS, to support our hypothesis.

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.

The rapid dissemination of antimicrobial resistance (AMR) has emerged as a serious health problem on an unprecedented global scale. AMR is predicted to kill more than 10 million people annually by 2050 leading to huge economic losses worldwide. Therefore, urgent action is required at the national as well as international levels to avert this looming crisis. Effective surveillance can play an important role in the containment of AMR spread by providing data to help determine AMR hotspots, predict an outbreak, maintain proper stewardship and propose immediate and future plans of action in this respect. Although many existing databases provide genetic and molecular information on AMR in microorganisms, there is no dedicated database of AMR from non-clinical samples. The FEAMR database is a one-of-its-kind database to provide manually collated and curated information on the prevalence of AMR in food and the environment. For designing the FEAMR webpage, Microsoft Visual Studio with HTML, CSS, ASP.NET, Bootstrap for the front-end and C# for the back-end were used. The FEAMR database is a free access resource (https://feamrudbt-amrlab.mu.ac.in/), accepting verified food- and environment-related AMR submissions from across the globe. To the best of our knowledge, it is probably the first database providing AMR-related surveillance data from non-clinical samples. It is designed from the ‘One Health Approach’ perspective to help in the containment of global AMR spread.Graphical Flowsheet of steps for making FEAMR database 1. Research articles relating to Antimicrobial Resistance (AMR) were searched on the internet. 2. Data relating to AMR were retrieved from these articles and stored in an MS-Excel sheet. 3. The web pages of the FEAMR database (DB) were created using Microsoft Visual Studio (MVS) and its various tools. HTML, CSS, ASP.NET and Bootstrap were used for the front end and C# used for the back-end of the website. 4. The DB of FEAMR was created using MS SQL Server which was controlled by SQL Server Management Studio (SSMS). 5. The data from the MS-Excel sheet in step 2 was stored in the SQL server and displayed on the web page using GridView tool of MVS and C#. The database created was then uploaded on the University of Mumbai (UoM) website, where it can be accessed by all users having the link to the DB (https://feamrudbt-amrlab.mu.ac.in/).

Introduction. The banking sector assigns high priority to data storage, as it is a critical aspect of business operations. The volume of data in this area is steadily growing. With the increasing volume of data that needs to be stored, processed and analyzed, it is critically important to select a suitable data storage solution and develop the required architecture. The presented research is aimed at filling the gap in the existing knowledge of the data base management system (DBMS) suitable for the banking sector, as well as to suggest ways for a fault-tolerant data storage cluster. The purpose of the work is to analyze the key DBMS for analytical queries, determine the priorities of the DBMS for the banking sector, and develop a fault-tolerant data storage cluster. To meet the performance and scalability requirements, a data storage solution with a fault-tolerant architecture that meets the requirements of the banking sector has been proposed.Materials and Methods. Domain analysis allowed us to create a set of characteristics that a DBMS for analytical queries (OnLine Analytical processing — OLAP) should correspond to, compare some popular DBMS OLAP, and offer a fault-tolerant cluster configuration written in xml, supported by the ClickHouse DBMS. Automation was done using Ansible Playbook. It was integrated with the Gitlab version control system and Jinja templates. Thus, rapid deployment of the configuration on all nodes of the cluster was achieved.Results. For OLAP databases, criteria were developed and several popular systems were compared. As a result, a reliable cluster configuration that met the requirements of analytical queries has been proposed for the banking industry. To increase the reliability and scalability of the DBMS, the deployment process was automated. Detailed diagrams of the cluster configuration were also provided.Discussion and Conclusions. The compiled criteria for the DBMS OLAP allowed us to determine the need for this solution in the organization. Comparison of popular DBMS can be used by organizations to minimize costs when selecting a solution. The proposed configuration of the data warehouse cluster for analytical queries in the banking sector will improve the reliability of the DBMS and meet the requirements for subsequent scalability. Automation of cluster deployment by the mechanism of templating configuration files in Ansible Playbook provides configuring a ready-made cluster on new servers in minutes.