Performance Of Database Management Systems Research Articles

Enhancing Database Query Performance: Analysis of Indexing Techniques

The need to enhance query performance in databases within the contemporary information environment is crucial for ensuring efficient operations across various domains. This paper is dedicated to an analysis of database indexing techniques aimed at understanding their impact on query performance and efficiency. It meticulously examines various types of indexes, including B-trees, hash tables, and textual indexes, analyzing their advantages and limitations. The investigation into the influence of these techniques on query execution time reveals that performance depends on various factors such as query complexity and data volume. Special attention is given to selecting the optimal index type based on specific database needs and characteristics. The research underscores the importance of considering data volume and database structure in choosing effective indexing methods. Additionally, the advantages and disadvantages of different indexing techniques are scrutinized, taking into account their impact on query execution speed and efficiency. Of the study indicate that the correct selection and utilization of appropriate indexing strategies can significantly enhance the performance of database management systems, providing swift and efficient access to information for users. Ultimately, this work makes a significant contribution to understanding and practically applying indexing techniques to improve query performance in databases.

The Impact of Scalability and Consistency Management on Database Management System Performance in Big Data Environment in Indonesia

The swift expansion of technology start-up enterprises in Indonesia demands a deep comprehension of the variables impacting the functionality of Big Data Environments (BDE) and Database Management Systems (DMS). In the context of Indonesian start-ups, this study examines the effects of scalability and consistency management on DMS and BDE. Data from 134 participants were examined using Structural Equation Modeling (SEM-PLS) in a quantitative manner. The findings showed a strong favorable correlation between DMS -> BDE, Scalability -> DMS, and Consistency Management -> DMS. Strong reliability was exhibited by the measurement model, and discriminant validity was verified. While the model fit indices revealed places for improvement, the R Square values indicated an effective explanation of variation. An overview of Indonesian start-up characteristics that is representative was given by the demographic sample study. This research adds knowledge for improving big data and database operations in the dynamic startup environment.

Database management system performance comparisons: A systematic literature review

Efficiency has been a pivotal aspect of the software industry since its inception, as a system that serves the end-user fast, and the service provider cost-efficiently benefits all parties. A database management system (DBMS) is an integral part of effectively all software systems, and therefore it is logical that different studies have compared the performance of different DBMSs in hopes of finding the most efficient one. This study systematically synthesizes the results and approaches of studies that compare DBMS performance and provides recommendations for industry and research. The results show that performance is usually tested in a way that does not reflect real-world use cases, and that tests are typically reported in insufficient detail for replication or for drawing conclusions from the stated results.

Parameters tuning of multi-model database based on deep reinforcement learning

As we all know, the performance of database management system is directly linked to a vast array of knobs, which control various aspects of system operation, ranging from memory and thread counts settings to I/O optimization. Improper settings of configuration parameters are shown to have detrimental effects on performance, reliability and availability of the overall database management system. This is also true for multi-model databases, which use a single platform to support multiple data models. Existing approaches for automatic DBMS knobs tuning are not directly applicable to multi-model databases due to the diversity of multi-model database instances and workloads. Firstly, in cloud environment, they have difficulty adapting to changing environments and diverse workloads. Secondly, they rely on large-scale high-quality training samples that are difficult to obtain. Finally, they focus primarily on throughput metrics, ignoring tuning requirements for resource utilization. Therefore, in this paper, we propose a multi-model database configuration parameters tuning solution named MMDTune. It selects influential parameters, recommends the optimal configurations in a high-dimensional continuous space. For different workloads, the TD3 algorithm is improved to generate reasonable parameter adjustment plans according to the internal state of the multi-model databases. We conduct extensive experiments under 5 different workloads on real cloud databases to evaluate MMDTune. Experimental results show that MMDTune adapts well to a new hardware environment or workloads, and significantly outperforms the representative tuning tools, such as OtterTune, CDBTune.

Performance testing on Transparent Data Encryption for SQL Server's reliability and efficiency

Data security is being one of the most crucial aspects to be focused on system development. However, using such a feature to enhance the security of data might affect the system's performance. This study aims to observe how substantial Transparent Data Encryption as a solution for data security on Microsoft SQL Server will affect the database management system's performance. Each of the system performance is conducted with stress and load test. This paper concentrates on the upsides of using Transparent Data Encryption over standard database by finding how significant performance degradation has occurred in terms of Reliability and Efficiency.

Модификация иерархических битовых индексов для повышения производительности систем управления базами данных

Модификация иерархических битовых индексов для повышения производительности систем управления базами данных

Кэширование машинного кода в динамическом компиляторе SQL-запросов для СУБД PostgreSQL

As the efficiency of main and external memory grows, alongside with decreasing hardware costs, the performance of database management systems (DBMS) on certain kinds of queries is more determined by CPU characteristics and the way it is utilized. Relational DBMS utilize diverse execution models to run SQL queries. Those models have different properties, but in either way suffer from substantial overhead during query plan interpretation. The overhead comes from indirect calls to handler functions, runtime checks and large number of branch instructions. One way to solve this problem is dynamic query compilation that is reasonable only in those cases when query interpretation time is larger than the time of compilation and optimized machine code execution. This requirement can be satisfied only when the amount of data to be processed is large enough. If query interpretation takes milliseconds to finish, then the cost of dynamic compilation can be hundreds of times more than the execution time of generated machine code. To pay off the cost of dynamic compilation, the generated machine code has to be reused in subsequent executions, thus saving the cost of code compilation and optimization. In this paper, we examine the method of machine code caching in our query JIT-compiler for DBMS PostgreSQL. The proposed method allows us to eliminate compilation overhead. The results show that dynamic compilation of queries with machine code caching feature gives a significant speedup on OLTP queries.

APOLLO

The practical art of constructing database management systems (DBMSs) involves a morass of trade-offs among query execution speed, query optimization speed, standards compliance, feature parity, modularity, portability, and other goals. It is no surprise that DBMSs, like all complex software systems, contain bugs that can adversely affect their performance. The performance of DBMSs is an important metric as it determines how quickly an application can take in new information and use it to make new decisions. Both developers and users face challenges while dealing with performance regression bugs. First, developers usually find it challenging to manually design test cases to uncover performance regressions since DBMS components tend to have complex interactions. Second, users encountering performance regressions are often unable to report them, as the regression-triggering queries could be complex and database-dependent. Third, developers have to expend a lot of effort on localizing the root cause of the reported bugs, due to the system complexity and software development complexity. Given these challenges, this paper presents the design of Apollo, a toolchain for automatically detecting, reporting, and diagnosing performance regressions in DBMSs. We demonstrate that Apollo automates the generation of regression-triggering queries, simplifies the bug reporting process for users, and enables developers to quickly pinpoint the root cause of performance regressions. By automating the detection and diagnosis of performance regressions, Apollo reduces the labor cost of developing efficient DBMSs.

Динамическая компиляция выражений в SQL-запросах для СУБД PostgreSQL

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.

Динамическая компиляция SQL-запросов для СУБД PostgreSQL

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 have developed a PostgreSQL extension that implements SQL query JIT compilation using LLVM compiler infrastructure. In this paper we show how to implement LLVM-analogues of the main operators of the PostgreSQL, how to replace Volcano iterator model abstraction (open(), next(), close()) by the abstraction that is more suitable to generate code for a particular query. Currently, with LLVM JIT we achieve up to 4.3x speedup on TPC-H Q1 query as compared to original PostgreSQL interpreter.

Automatic Mining and Processing Dormancy Data in the Database Management System for Small and Medium Enterprises

The paper studies how to mine automatically and process the dormancy data and then improve the performance of database management system. It analyzes the main mode of dormant data into the data warehouse, the innovative design of SQL capture and changer function, as well as gives the statistical method of recognition dormant data in data warehouse, and the SQL change and capture function migration to the application server, automatic capture dormancy data without professional intervention. It helps to solve the problem of the lack of staff and cumulative historical data influence performance of the database management system in the small and medium-sized enterprises.

An Adaptive Hybrid Controller for DBMS Performance Tuning

Performance tuning process of database management system (DBMS) is an expensive, complex and time consuming process to be handled by human experts. A proposed adaptive controller is developed that utilizes a hybrid model from fuzzy logic and regression analysis to tune the memory-resident data structures of DBMS. The fuzzy logic module uses flexible rule matrix with adaption techniques to deal with fluctuations and abrupt changes in the operation environment. The regression module predicts fluctuations in operation environment so the controller can take former action. Experimental results on standard benchmarks showed significant performance enhancement as compared to built-in self-tuning features.

점진적 맵 업데이트를 위한 모바일 DBMS의 플래시메모리 페이지 관리 기법

Recently the mobile DBMS (Database Management System) is popular to store and manage large data in a mobile device. Especially, the research and development about mobile storage structure and querying method for navigation map data in a mobile device have been performed. The performance of the mobile DBMS in which random data accesses are most queries if the NAND flash memory is used as storage media of the DBMS is degraded. The reason is that the performance of flash memory is good in writing sequentially but bad in writing randomly as the features of the NAND flash memory. So, new storage structure and querying policies of the mobile DBMS are needed in the mobile DBMS in which a flash memory is used as storage media. In this paper, we have studied the policy of the database page management to enhance the performance of the frequent random update and applied this policy to the navigation-specialized mobile DBMS which supports incremental map update. And also we have evaluated the performance of this policy by experiments.

Modeling of DBMS Memory for Performance Tuning

performance of Database Management System(DBMS) is significantly affected when the key tuning parameters are altered. Most DBMS come along with several hundred tuning parameters. It is therefore important to identify only a few important tuning parameters and evaluate their effect on the system performance. The effect of each of the tuning parameter must be thoroughly understood so as to predict the performance when these parameters are altered. It is also important to understand the range of the tuning parameter over which tuning is most effective. Over tuning may lead to poor utilization of system resources. In this paper, a mathematical model is presented to predict the effect of one of the most important tuning parameter, namely, the buffer cache size and the model output is compared with experimental result. The model shows very close match with the experimental results.

Query processing on multi-core architectures

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.

Evaluating the Dynamic Behavior of Database Applications

This paper explores the effect that changing access patterns has on the performance of database management systems. Changes in access patterns play an important role in determining the efficiency of key performance optimization techniques, such as dynamic clustering, prefetching, and buffer replacement. However, all existing benchmarks or evaluation frameworks produce static access patterns in which objects are always accessed in the same order repeatedly. Hence, we have proposed the Dynamic Evaluation Framework (DEF) that simulates access pattern changes using configurable styles of change. DEF has been designed to be open and fully extensible (e.g., new access pattern change models can be added easily). In this paper, we instantiate DEF into the Dynamic Object Evaluation Framework (DoEF) which is designed for object databases, that is, object-oriented or object-relational databases, such as multimedia databases or most XML databases. The capabilities of DoEF have been evaluated by simulating the execution of four different dynamic clustering algorithms. The results confirm our analysis that flexible conservative reclustering is the key in determining a clustering algorithm’s ability to adapt to changes in access pattern. These results show the effectiveness of DoEF at determining the adaptability of each dynamic clustering algorithm to changes in access pattern in a simulation environment. In a second set of experiments, we have used DoEF to compare the performance of two real-life object stores: Platypus and SHORE. DoEF has helped to reveal the poor swapping performance of Platypus.

Joint evaluation of recovery and performance of a COTS DBMS in the presence of operator faults

A major cause of failures in large database management systems (DBMS) is operator/administrator faults. Although most of the complex DBMS available today have comprehensive recovery mechanisms, the effectiveness of these mechanisms is difficult to characterize. On the other hand, the tuning of a large database is very complex and database administrators tend to concentrate on performance tuning and disregard the recovery mechanisms. Above all, database administrators seldom have feedback on how good a given configuration is concerning recovery. This paper proposes an experimental approach to characterize both the performance and the recoverability of DBMS. Our approach is presented through a concrete example of benchmarking the performance and recovery of an Oracle DBMS running the standard TPC-C benchmark, extended to include two new elements: a faultload based on operator faults and measures related to recoverability. A classification of operator/administrator faults in DBMS is proposed. A set of tools have been designed and built to reproduce operator faults in an Oracle 8 i DBMS, using exactly the same means used in the field by the real database administrator. This experimental approach is generic (i.e., can be applied to any DBMS) and is fully automatic. The paper ends with the discussion of the results and the proposal of guidelines to help database administrators in finding the balance between performance and recovery tuning.

Parallel Database Techniques

Parallel Database Techniques

Practical throughput estimation for parallel databases

Methods for estimating the performance of database management systems can aid the design of database systems by identifying potential performance bottle-necks or by predicting the relative performance of different designs. Performance estimation is critical in parallel database systems with distributed memory, where an effective overall performance depends on a good choice among a wide range of ways of placing data. An approach is described for performance estimation for shared-nothing parallel database systems. It estimates system throughput for a given benchmark or set of queries, and can exercise different data placement schemes to determine the data layout that provides the best throughput value.

An integrated data structure with multiple access paths for database systems and its performance

In the past a number of file organizations have been proposed for processing different types of queries efficiently. To our knowledge none of the existing file organizations is capable of supporting all types of accesses equally efficiently. In this paper we have taken a different approach for designing an integated data structure which offers multiple access paths for processing different types of queries efficiently. The data structure reported here can be implemented on disk based as well as main memory database systems, however, in this paper we report its behavior mainly in main memory database environment. Our approach is to fuse those data structures which offer an efficient access paths for a particular type. To show the feasibility of our scheme we fused the B +-tree, the grid file and extendible hashing structures, using a proper interface. We implemented and measured its performance through simulation modeling. Our results show that the data structure does improve concurrency and offers a higher throughput for a variety of transaction processing workloads. We argue that our scheme is different than creating secondary indexes for improving concurrency. In the absence of a data strucure which can provide all types of access equally efficiently, an integrated data structure is an acceptable solution which offers an efficient way for increasing the performance of database management systems.