Commercial Database Systems Research Articles

DTT: An Example-Driven Tabular Transformer for Joinability by Leveraging Large Language Models

Many organizations rely on data from government and third-party sources, and those sources rarely follow the same data formatting. This introduces challenges in integrating data from multiple sources or aligning external sources with internal databases. Commercial database systems do not offer adequate support for integrating data from heterogeneous sources, and manual integration is both time-consuming and inefficient. State-of-the-art data integration approaches that rely on similarity functions and textual transformations often fail to handle challenging cases where multiple mappings are required, or the mappings go beyond simple textual transformations. In this paper, we study the potentials of deep neural models for transforming tables for joinability. In particular, we cast the problem as a prediction task and develop a framework that leverages large deep-learning language models to transform tabular data from a source formatting to a desired target representation. Our framework can efficiently learn the patterns for mapping a source formatting into an expected target using just a few examples, which can then be used for tasks such as table joining, filling in missing values, and error detection. Compared to state-of-the-art mapping and joining approaches, our framework delivers noticeably more accurate and scalable performance on both real-world and synthetic datasets. Our experimental evaluation also shows that the performance of the proposed framework using our fine-tuned model is at par or better than large language models such as GPT-3, despite the significant difference in size, and that using large language models within our framework improves their performance.

POLAR: Adaptive and Non-invasive Join Order Selection via Plans of Least Resistance

Join ordering and query optimization are crucial for query performance but remain challenging due to unknown or changing characteristics of query intermediates, especially for complex queries with many joins. Over the past two decades, a spectrum of techniques for adaptive query processing (AQP)---including inter-/intra-operator adaptivity and tuple routing---have been proposed to address these challenges. However, commercial database systems in practice do not implement holistic AQP techniques because they increase the system complexity (e.g., intertwined planning and execution) and thus, complicate debugging and testing. Additionally, existing approaches may incur large overheads, leading to problematic performance regressions. In this paper, we introduce POLAR, a simple yet very effective technique for a self-regulating selection of alternative join orderings with bounded overhead. We enhance left-deep join pipelines with alternative join orders, perform regret-bounded tuple routing to find and validate "plans of least resistance", and then process the majority of tuple batches through these plans. We study different join order selection techniques, different routing strategies, and a variety of workload characteristics. Our experiments with a POLAR prototype in DuckDB show runtime improvements of up to 9x and less than 7% overhead for all benchmark queries, while outperforming state-of-the-art AQP systems by up to 15x.

OpenGauss

Although learning-based database optimization techniques have been studied from academia in recent years, they have not been widely deployed in commercial database systems. In this work, we build an autonomous database framework and integrate our proposed learning-based database techniques into an open-source database system openGauss. We propose effective learning-based models to build learned optimizers (including learned query rewrite, learned cost/cardinality estimation, learned join order selection and physical operator selection) and learned database advisors (including self-monitoring, self-diagnosis, self-configuration, and self-optimization). We devise an effective validation model to validate the effectiveness of learned models. We build effective training data management and model management platforms to easily deploy learned models. We have evaluated our techniques on real-world datasets and the experimental results validated the effectiveness of our techniques. We also provide our learnings of deploying learning-based techniques.

Sharing opportunities for OLTP workloads in different isolation levels

OLTP applications are usually executed by a high number of clients in parallel and are typically faced with high throughput demand as well as a constraint latency requirement for individual statements. Interestingly, OLTP workloads are often read-heavy and comprise similar query patterns, which provides a potential to share work of statements belonging to different transactions. Consequently, OLAP techniques for sharing work have started to be applied also to OLTP workloads, lately. In this paper, we present an approach for merging read statements within interactively submitted multi-statement transactions consisting of reads and writes. We first define a formal framework for merging transactions running under a given isolation level and provide insights into a prototypical implementation of merging within a commercial database system. In our experimental evaluation, we show that, depending on the isolation level, the load in the system and the read-share of the workload, an improvement of the transaction throughput by up to a factor of 2.5X is possible without compromising the transactional semantics.

Understanding and benchmarking the impact of GDPR on database systems

The General Data Protection Regulation (GDPR) provides new rights and protections to European people concerning their personal data. We analyze GDPR from a systems perspective, translating its legal articles into a set of capabilities and characteristics that compliant systems must support. Our analysis reveals the phenomenon of metadata explosion, wherein large quantities of metadata needs to be stored along with the personal data to satisfy the GDPR requirements. Our analysis also helps us identify new workloads that must be supported under GDPR. We design and implement an open-source benchmark called GDPRbench that consists of workloads and metrics needed to understand and assess personal-data processing database systems. To gauge the readiness of modern database systems for GDPR, we follow best practices and developer recommendations to modify Redis, PostgreSQL, and a commercial database system to be GDPR compliant. Our experiments demonstrate that the resulting GDPR-compliant systems achieve poor performance on GPDR workloads, and that performance scales poorly as the volume of personal data increases. We discuss the real-world implications of these .ndings, and identify research challenges towards making GDPR-compliance efficient in production environments. We release all of our so.ware artifacts and datasets at h.p://www:gdprbench:org

OLTPshare

In the past, resource sharing has been extensively studied for OLAP workloads. Naturally, the question arises, why studies mainly focus on OLAP and not on OLTP workloads? At first sight, OLTP queries - due to their short runtime - may not have enough potential for the additional overhead. In addition, OLTP workloads do not only execute read operations but also updates. In this paper, we address query sharing for OLTP workloads. We first analyze the sharing potential in real-world OLTP workloads. Based on those findings, we then present an execution strategy, called OLTPShare that implements a novel batching scheme for OLTP workloads. We analyze the sharing benefits by integrating OLTPShare into a prototype version of the commercial database system SAP HANA. Our results show for different OLTP workloads that OLTPShare enables SAP HANA to provide a significant throughput increase in high-load scenarios compared to the conventional execution strategy without sharing.

Parallel replication across formats in SAP HANA for scaling out mixed OLTP/OLAP workloads

Modern in-memory database systems are facing the need of efficiently supporting mixed workloads of OLTP and OLAP. A conventional approach to this requirement is to rely on ETL-style, application-driven data replication between two very different OLTP and OLAP systems, sacrificing real-time reporting on operational data. An alternative approach is to run OLTP and OLAP workloads in a single machine, which eventually limits the maximum scalability of OLAP query performance. In order to tackle this challenging problem, we propose a novel database replication architecture called Asynchronous Parallel Table Replication (ATR). ATR supports OLTP workloads in one primary machine, while it supports heavy OLAP workloads in replicas. Here, row-store formats can be used for OLTP transactions at the primary, while column-store formats are used for OLAP analytical queries at the replicas. ATR is designed to support elastic scalability of OLAP query performance while it minimizes the overhead for transaction processing at the primary and minimizes CPU consumption for replayed transactions at the replicas. ATR employs a novel optimistic lock-free parallel log replay scheme which exploits characteristics of multi-version concurrency control (MVCC) in order to enable real-time reporting by minimizing the propagation delay between the primary and replicas. Through extensive experiments with a concrete implementation available in a commercial database system, we demonstrate that ATR achieves sub-second visibility delay even for update-intensive workloads, providing scalable OLAP performance without notable overhead to the primary.

K-dominant skyline queries on incomplete data

The skyline query has been extensively explored as one of popular techniques to filter uninteresting data objects, which plays an important role in many real-life applications such as multi-criteria decision making and personalized services. This query has also been incorporated into commercial database systems for supporting preference queries. However, a skyline query may retrieve too many objects to analyze intensively especially for high-dimensional datasets. As a result, k-dominant skyline query has been introduced to control the number of the objects retrieved. Existing algorithms for k-dominant skyline queries only aim at complete data, which is not well-suited for incomplete data, even though incomplete data is pervasive in scientific research and real life, due to delivery failure, no power of battery, accidental loss, etc. In this paper, we systematically study the problem of k-dominant skyline queries on incomplete data (IkDS), where the data objects might miss their attribute values. We formalize the IkDS query and then present three efficient algorithms for finding k-dominant skyline objects over incomplete data. Several novel concepts/techniques are utilized including local skyline, dominance ability, and bitmap index on incomplete data to shrink the search space. In addition, we extend our techniques to tackle two interesting variants, i.e., weighted dominant skyline query and top-δ dominant skyline query, over incomplete data. Extensive experiments using both real and synthetic data sets demonstrate the performance of our proposed algorithms.

Resource Bricolage for Parallel DBMSs on Heterogeneous Clusters

Running parallel database systems in an environment with heterogeneous resources has become increasingly common, due to cluster evolution and increasing interest in moving applications into public clouds or shared infrastructures. For database systems running in a heterogeneous cluster, the default uniform data partitioning strategy may overload some of the slow machines while at the same time it may underutilize the more powerful machines. Since the processing time of a parallel query is determined by the slowest machine, such an allocation strategy may result in a significant query performance degradation. We take a first step to address this problem by introducing a technique we call resource bricolage that improves database performance in heterogeneous environments. Our approach quantifies the performance differences among machines with various resources as they process workloads with diverse resource requirements. We formalize the problem of minimizing workload execution time and view it as an optimization problem, and then we employ linear programming to obtain a recommended data partitioning scheme. We verify the effectiveness of our technique with an extensive experimental study on a commercial database system.

Resource bricolage for parallel database systems

Running parallel database systems in an environment with heterogeneous resources has become increasingly common, due to cluster evolution and increasing interest in moving applications into public clouds. For database systems running in a heterogeneous cluster, the default uniform data partitioning strategy may overload some of the slow machines while at the same time it may under-utilize the more powerful machines. Since the processing time of a parallel query is determined by the slowest machine, such an allocation strategy may result in a significant query performance degradation. We take a first step to address this problem by introducing a technique we call resource bricolage that improves database performance in heterogeneous environments. Our approach quantifies the performance differences among machines with various resources as they process workloads with diverse resource requirements. We formalize the problem of minimizing workload execution time and view it as an optimization problem, and then we employ linear programming to obtain a recommended data partitioning scheme. We verify the effectiveness of our technique with an extensive experimental study on a commercial database system.

Adaptive self-tuning techniques for performance tuning of database systems: a fuzzy-based approach with tuning moderation

Self-tuning of database management systems (DBMS) offers important advantages such as improved performance, reduced total cost of ownership, eliminates the need for an expert database administrator (DBA), and improves 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 for self-tuning the shared memory of DBMS have high computational overheads as they use large statistical data. As a 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 an important soft-computing method, namely, 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 (Burlson and Donald 2010) 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 modified Monitor, Analyze, Plan and Execute (MAPE) feedback control loop framework termed Monitor, Estimate and Execute (MEE). The self-tuning approach using this method has been tested under various workload types. The results have been validated by comparing the performance of the proposed self-tuning system with the workload-analysis-based self-tuning feature of the commercial database system, Oracle 10g. The results show significant improvement in performance under two workload types, namely, TPC-C and TPC-E and user-load variations in the range 2---100. The system is also tested under TPC-D workload for the user-load 1---10. This improved self-tuning helps in simplifying the job of the DBA, and results in cost saving and betters the business prospectus of the enterprise. A novel tuning moderation technique is also presented in this paper, that provides the necessary stability to the system while the tuning parameters are dynamically altered.

Building efficient query engines in a high-level language

In this paper we advocate that it is time for a radical rethinking of database systems design. Developers should be able to leverage high-level programming languages without having to pay a price in efficiency. To realize our vision of abstraction without regret , we present LegoBase, a query engine written in the high-level programming language Scala. The key technique to regain efficiency is to apply generative programming: the Scala code that constitutes the query engine, despite its high-level appearance, is actually a program generator that emits specialized, low-level C code. We show how the combination of high-level and generative programming allows to easily implement a wide spectrum of optimizations that are difficult to achieve with existing low-level query compilers, and how it can continuously optimize the query engine. We evaluate our approach with the TPC-H benchmark and show that: (a) with all optimizations enabled, our architecture significantly outperforms a commercial in-memory database system as well as an existing query compiler, (b) these performance improvements require programming just a few hundred lines of high-level code instead of complicated low-level code that is required by existing query compilers and, finally, that (c) the compilation overhead is low compared to the overall execution time, thus making our approach usable in practice for efficiently compiling query engines.

Performance of program modification techniques that ensure serializable executions with snapshot isolation DBMS

Snapshot Isolation (SI) is a multiversion concurrency control that has been implemented by several open source and commercial database systems (Oracle, Microsoft SQL Server, and previous releases of PostgreSQL). The main feature of SI is that a read operation does not block a write operation and vice versa, which allows higher degree of concurrency than traditional two-phase locking. SI prevents many anomalies that appear in other isolation levels, but it still can result in non-serializable executions, in which database integrity constraints can be violated. Several techniques are known to modify the application code based on preanalysis, in order to ensure that every execution is serializable on engines running SI. We introduce a new technique called External Lock Manager (ELM). In using a technique, there is a choice to make, of which pairs of transactions need to have conflicts introduced. We measure the performance impact of the choices available, among techniques and conflicts.

Adaptive range filters for cold data

Bloom filters are a great technique to test whether a key is not in a set of keys. This paper presents a novel data structure called ARF. In a nutshell, ARFs are for range queries what Bloom filters are for point queries. That is, an ARF can determine whether a set of keys does not contain any keys that are part of a specific range. This paper describes the principles and methods for efficient implementation of ARFs and presents the results of comprehensive experiments that assess the precision, space, and latency of ARFs. Furthermore, this paper shows how ARFs can be applied to a commercial database system that partitions data into hot and cold regions to optimize queries that involve only hot data.

Storing and processing temporal data in a main memory column store

Managing and accessing temporal data is of increasing importance in industry. So far, most companies model the time dimension on the application layer rather than pushing down the operators to the database, which leads to a significant performance overhead. The goal of this PhD thesis is to develop a native support of temporal features for SAP HANA, which is a commercial in-memory column store database system. We investigate different alternatives to store temporal data physically and analyze the trade-offs arising from different memory layouts which cluster the data either by time or by space dimension. Taking into account the underlying physical representation, different temporal operators such as temporal aggregation, time travel and temporal join have to be executed efficiently. We present a novel data structure called Timeline Index and algorithms based on this index, which have a very competitive performance for all temporal operators beating existing best-of-breed approaches by factors, sometimes even by orders of magnitude. The results of this thesis are currently being integrated into HANA, with the goal of being shipped to the customers as a productive release within the next few months.

Recommending XML physical designs for XML databases

Database systems employ physical structures such as indexes and materialized views to improve query performance, potentially by orders of magnitude. It is therefore important for a database administrator to choose the appropriate configuration of these physical structures for a given database. XML database systems are increasingly being used to manage semi-structured data, and XML support has been added to commercial database systems. In this paper, we address the problem of automatic physical design for XML databases, which is the process of automatically selecting the best set of physical structures for a database and a query workload. We focus on recommending two types of physical structures: XML indexes and relational materialized views of XML data. We present a design advisor for recommending XML indexes, one for recommending materialized views, and an integrated design advisor that recommends both indexes and materialized views. A key characteristic of our advisors is that they are tightly coupled with the query optimizer of the database system, and they rely on the optimizer for enumerating and evaluating physical designs. We have implemented our advisors in a prototype version of IBM DB2 V9, and we experimentally demonstrate the effectiveness of their recommendations using this implementation.

An intelligent hybrid optimistic/pessimistic concurrency control algorithm for centralized database systems using modified GSA-optimized ART neural model

Concurrency control is the activity of synchronizing operations issued by concurrent executing transactions on a shared database. The aim of this control is to provide an execution that has the same effect as a serial (non-interleaved) one. The optimistic concurrency control technique allows the transactions to execute without synchronization, relying on commit-time validation to ensure serializability. Effectiveness of the optimistic techniques depends on the conflict rate of transactions. Since different systems have various patterns of conflict and the patterns may also change over time, so applying the optimistic scheme to the entire system results in degradation of performance. In this paper, a novel algorithm is proposed that dynamically selects the optimistic or pessimistic approach based on the value of conflict rate. The proposed algorithm uses an adaptive resonance theory–based neural network in making decision for granting a lock or detection of the winner transaction. In addition, the parameters of this neural network are optimized by a modified gravitational search algorithm. On the other hand, in the real operational environments we know the writeset (WS) and readset (RS) only for a fraction of transactions set before execution. So, the proposed algorithm is designed based on optional knowledge about WS and RS of transactions. Experimental results show that the proposed hybrid concurrency control algorithm results in more than 35 % reduction in the number of aborts in high-transaction rates as compared to strict two-phase locking algorithm that is used in many commercial database systems. This improvement is 13 % as compared to pure-pessimistic approach and is more than 31 % as compared to pure-optimistic approach.

First Report: Application of MALDI-TOF MS within an External Quality Assurance Exercise for the Discrimination of Highly Pathogenic Bacteria from Contaminant Flora

In this article the authors describe the application of Matrix Assisted Laser Desorption/Ionization Time-of-Flight (MALDI-TOF) Intact Cell Mass Spectrometry (ICMS) within a proficiency test organized by a European Union (EU)-funded project on the topic of Establishment of Quality Assurances for Detection of Highly Pathogenic Bacteria of Potential Bioterrorism Risk (EQADeBa). In total 15 samples with native bacteria, including risk group 3 (RG3) target agents and non-RG3 bacteria as potential contaminants, were analyzed by five different labs with ICMS. Whereas the technique was used by all labs and showed good performance in the identification of the non-RG3 contaminants, only one lab used mass spectrometry (MS) for RG3 identification. One reason for this restricted application of the technique is the limited performance of the commercial database systems in the field of highly pathogenic organisms. Furthermore, the relatively new technology is in competition with well-established and highly effective mol...

A Query Optimization Framework for Fuzzy Relational Databases

Ever since the development of relational model, relational database systems have been extensively studied and several commercial relational database systems are currently available. Relational model usually take care of only well defined data. In order to capture more meaning to the data an extension of the classical relational model called fuzzy relational model was proposed. The key reasons for the success of relational database lies in the power of declarative languages and execution strategies used in query optimization. Estimating the cost of fuzzy query based on system catalog introduces error due to approximation involved and insufficient information at query execution time. So there is need for a query optimization framework that addresses the issues of query execution in fuzzy relational databases. This paper deals with a framework for building fuzzy cost model to obtain a good execution strategy for a query.

AMGA GUI Client 툴킷 구현 : AMGA Manager

EMI gLite 미들웨어 컴포넌트인 AMGA 서비스는 과학기술 연구자들에 의해 메타데이터 저장소로 분산환경의 대규모 실험 데이터 분석에 넓게 활용되고 있고 그 사용은 메타데이터 카탈로그를 필요로 하는 일반산업에까지 확대되고 있는 추세이다. 하지만, 리눅스와 그리드 사용자 인터페이스 기반의 AMGA는 다른 상용 데이터베이스 시스템과 비교해서 범용성 사용자 인터페이스의 부재라는 단점이 있으며, 기능의 우수함에도 불구하고 활용 및 확산하는데 어려움이 있다. 본 논문에서는, AMGA 사용의 제약을 최소화하고 사용자 친화적 인터페이스를 제공하기 위해 객체지향적 모델링 언어(UML)를 이용하여 AMGA GUI 툴킷을 개발하였다. 현재 Belle II, WISDOM, MDM 등과 같이 많은 사용자 커뮤니티에서 AMGA는 주요 컴포넌트로 사용되고 있지만 본 개발을 통해 AMGA 신규 사용자의 진입장벽을 낮춰줄 수 있을 뿐만 아니라 보다 많은 커뮤니티로 확대가 기대된다. AMGA service, which is one of the EMI gLite middleware components, is widely used for analysis of distributed large scale experiments data as metadata repository by scientific and technological researchers and the use of AMGA is extended farther to include general industries needing metadata Catalogue as well. However AMGA, based unix and Grid UI, has the weakness of being absence of general-purpose user interfaces in comparison to other commercial database systems and that's why it's difficult to use and diffuse it although it has the superiority of the functionality. In this paper, we developed AMGA GUI toolkit to provide work convenience using object-oriented modeling language(UML). Currently, AMGA has been used as the main component among many user communities such as Belle II, WISDOM, MDM, and so on, but we expect that this development can not only lower the barrier to entry for AMGA beginners to use it, but lead to expand the use of AMGA service over more communities.