Transaction Processing Performance Council Research Articles

Adaptive and Scalable Database Management with Machine Learning Integration: A PostgreSQL Case Study

The increasing complexity of managing modern database systems, particularly in terms of optimizing query performance for large datasets, presents significant challenges that traditional methods often fail to address. This paper proposes a comprehensive framework for integrating advanced machine learning (ML) models within the architecture of a database management system (DBMS), with a specific focus on PostgreSQL. Our approach leverages a combination of supervised and unsupervised learning techniques to predict query execution times, optimize performance, and dynamically manage workloads. Unlike existing solutions that address specific optimization tasks in isolation, our framework provides a unified platform that supports real-time model inference and automatic database configuration adjustments based on workload patterns. A key contribution of our work is the integration of ML capabilities directly into the DBMS engine, enabling seamless interaction between the ML models and the query optimization process. This integration allows for the automatic retraining of models and dynamic workload management, resulting in substantial improvements in both query response times and overall system throughput. Our evaluations using the Transaction Processing Performance Council Decision Support (TPC-DS) benchmark dataset at scale factors of 100 GB, 1 TB, and 10 TB demonstrate a reduction of up to 42% in query execution times and a 74% improvement in throughput compared with traditional approaches. Additionally, we address challenges such as potential conflicts in tuning recommendations and the performance overhead associated with ML integration, providing insights for future research directions. This study is motivated by the need for autonomous tuning mechanisms to manage large-scale, heterogeneous workloads while answering key research questions, such as the following: (1) How can machine learning models be integrated into a DBMS to improve query optimization and workload management? (2) What performance improvements can be achieved through dynamic configuration tuning based on real-time workload patterns? Our results suggest that the proposed framework significantly reduces the need for manual database administration while effectively adapting to evolving workloads, offering a robust solution for modern large-scale data environments.

Forester: Approximate Processing of an Imperative Procedure for Query-Time Exploratory Data Analysis in a Relational Database

Query-time Exploratory Data Analysis (qEDA) is an increasingly demanding aspect of the data analysis process that entails visually and quantitatively summarizing, comprehending, and interpreting the primary characteristics of a dataset. Nowadays, an imperative procedure is popular in relational databases for EDA because it enables us to write multiple dependent declarative queries with imperative logic. As online analytical processing (OLAP) systems contain extremely large datasets, data scientists often need quick visualizations of data, using approximate processing of imperative procedures, before analyzing them in their entirety. We identify gaps in the existing techniques, in that they are unable to sample both declarative-dependent statements and control logic at the same time and perform multi-dependent sampling-based approximate processing within the permitted time in qEDA. Traditional approximate query processing (AQP) involves tuple sampling for a single query approximation and enables queries to be executed over arbitrary random samples of tables. However, available AQP methods cannot produce a further representative sample of the data distribution for the dependent statements to estimate accurately and quickly for multiple dependent statements. On the other hand, sampling control structures, like loops and conditional statements, are discussed separately, without regard to the imperative structure of statements in a procedure. In this study, we propose Forester, a novel agile approximate processing method for imperative procedures that performs imperative program-aware sampling, which includes both statements with control regions (i.e., branch and loop) and processes them approximately within the permitted time in qEDA. Our method produces more targeted samples for each relation, while maintaining the data and control flow of dependent queries and imperative logic and determining all the conditions for a relation across all the statements in the sample that guarantee the existence of relevant data for dependent data distribution. Utilizing a workload of multi-statement imperative procedures from the Transaction Processing Performance Council Decision Support (TPC-DS) database, our experiment demonstrates that Forester outperforms the existing system in sampling, producing minimum error, and improving response time.

TPC, Where Art Thou?

Performance has and still is perceived by customers as one of the main differentiators of computer systems. Hence, having access to widely accepted, objective and verifiable performance data is paramount for customers in making informed buying decisions. The last ten years have seen the rise of many new DBMSs. With them came a flurry of benchmark definitions. Even if technically sound, these benchmarks alone cannot achieve the goal of delivering widely accepted, objective and verifiable performance data. In addition they need to be agreed upon by a large body of vendors, and they need an independent oversight authority that assures correctness of their results. There are two major standard organizations that work on achieving these goals: Standard Performance Evaluation Corporation (SPEC) and Transaction Processing Performance Council (TPC). This article centers around the TPC, its history, challenges and steps it is taking to stay relevant in the years ahead.

An efficient resource sharing technique for multi-tenant databases

Multi-tenancy is one of the key components of cloud computing environment. Multi-tenant database system in SaaS (Software as a Service) has gained a lot of attention in academics, research and business arena. These database systems provide scalability and economic benefits for both cloud service providers and customers(organizations/companies referred as tenants) by sharing same resources and infrastructure in isolation of shared databases, network and computing resources with Service level agreement (SLA) compliances. In a multitenant scenario, active tenants compete for resources in order to access the database. If one tenant blocks up the resources, the performance of all the other tenants may be restricted and a fair sharing of the resources may be compromised. The performance of tenants must not be affected by resource-intensive activities and volatile workloads of other tenants. Moreover, the prime goal of providers is to accomplish low cost of operation, satisfying specific schemas/SLAs of each tenant. Consequently, there is a need to design and develop effective and dynamic resource sharing algorithms which can handle above mentioned issues. This work presents a model embracing a query classification and worker sorting technique to efficiently share I/O, CPU and Memory thus enhancing dynamic resource sharing and improvising the utilization of idle instances proficiently. The model is referred as Multi-Tenant Dynamic Resource Scheduling Model (MTDRSM) .The MTDRSM support workload execution of different benchmark such as TPC-C(Transaction Processing Performance Council), YCSB(The Yahoo! Cloud Serving Benchmark)etc. and on different database such as MySQL, Oracle, H2 database etc. Experiments are conducted for different benchmarks with and without SLA compliances to evaluate the performance of MTDRSM in terms of latency and throughput achieved. The experiments show significant performance improvement over existing Mute Bench model in terms of latency and throughput.

Big Data Analytics and Processing Platform in Czech Republic Healthcare

Big data analytics (BDA) in healthcare has made a positive difference in the integration of Artificial Intelligence (AI) in advancements of analytical capabilities, while lowering the costs of medical care. The aim of this study is to improve the existing healthcare eSystem by implementing a Big Data Analytics (BDA) platform and to meet the requirements of the Czech Republic National Health Service (Tender-Id. VZ0036628, No. Z2017-035520). In addition to providing analytical capabilities on Linux platforms supporting current and near-future AI with machine-learning and data-mining algorithms, there is the need for ethical considerations mandating new ways to preserve privacy, all of which are preconditioned by the growing body of regulations and expectations. The presented BDA platform, has met all requirements (N > 100), including the healthcare industry-standard Transaction Processing Performance Council (TPC-H) decision support benchmark in compliance with the European Union (EU) and the Czech Republic legislations. Currently, the presented Proof of Concept (PoC) that has been upgraded to a production environment has unified isolated parts of Czech Republic healthcare over the past seven months. The reported PoC BDA platform, artefacts, and concepts are transferrable to healthcare systems in other countries interested in developing or upgrading their own national healthcare infrastructure in a cost-effective, secure, scalable and high-performance manner.

English

Database performance throughput is one of the main measurements of the application effectiveness or a system, in terms of speed and delivery. Higher application efficiency is always a goal for any organization; therefore, finding the way to improve the database performance has priority. There are two main factors that can contribute to performance gain or slowness. Firstly, how well designed the database structure is in terms of data types, referential integrity, and indexes. Secondly, the underlying hardware infrastructure that consists of a server, storage, and network. During the application design lifecycle, the database schema is one area that continuously evolves. There are numerous configurational parameters that need to be adjusted according to database schema design and underlying infrastructure, in which in-memory is an important feature. In-memory database stores all data in physical memory, because of keeping entire data and changes of data in physical memory, the in-memory database provides very short response time and transactional throughput as compared to the disk-based database. This study provides comparison between in-memory and disk-based performance with impact analysis of concurrent users and parallelism using TPC-C benchmarking. A comprehensive benchmark guideline benefits for specific database environment was provided in order to track performance changes while migrating to in-memory from disk-based database. These benchmarking statistics provided comparative standpoint that can be verified during performance bottleneck. This study shows that transitioning from disk-based database to in-memory database decreased the response time and reduced the lock contention, however, requires detailed review for index design changes. Key words: Microsoft Structured Query Language (SQL) Server, in-memory, disk-based, transaction processing performance council (TPC-C), benchmarking, database performance, performance comparison, response time, parallelism, concurrent users.

Secure logical schema and decomposition algorithm for proactive context dependent attribute based inference control

Inference problem has always been an important and challenging topic of data privacy in databases. In relational databases, the traditional solution to this problem was to define views on relational schemas to restrict the subset of attributes and operations available to the users in order to prevent unwanted inferences. This method is a form of decomposition strategy, which mainly concentrates on the granularity of the accessible fields to the users, to prevent sensitive information inference. Nowadays, due to increasing data sharing among parties, the possibility of constructing complex indirect methods to obtain sensitive data has also increased. Therefore, we need to not only consider security threats due to direct access to sensitive data but also address indirect inference channels using functional and probabilistic dependencies (e.g., deducing gender of an individual from his/her name) while creating security views. In this paper, we propose a proactive and decomposition based inference control strategy for relational databases to prevent direct or indirect inference of private data. We introduce a new kind of context dependent attribute policy rule, which is named as security dependent set, as a set of attributes whose association should not be inferred. Then, we define a logical schema decomposition algorithm that prevents inference among attributes in security dependent set. The decomposition algorithm takes both functional and probabilistic dependencies into consideration in order to prevent all kinds of known inferences of relations among the attributes of security dependent sets. We prove that our proposed decomposition algorithm generates a secure logical schema that complies with the given security dependent set constraints. Since our proposed technique is purely proactive, it does not require any prior knowledge about executed queries and do not need to modify any submitted queries. It can also be embedded into any relational database management system without changing anything in the underlying system. We empirically compare our proposed method with the state of art reactive methods. Our extensive experimental analysis, conducted using TPC-H11TPC-H is an ad-hoc decision support benchmark of Transaction Processing Performance Council. benchmark scheme, shows the effectives our proposed approach.

在大规模系统上优化 TPC-C 评测程序

Transaction processing performance council benchmark C (TPC-C) is the de facto standard for evaluating the performance of high-end computers running on-line transaction processing applications. Differing from other standard benchmarks, the transaction processing performance council only defines specifications for the TPC-C benchmark, but does not provide any standard implementation for end-users. Due to the complexity of the TPC-C workload, it is a challenging task to obtain optimal performance for TPC-C evaluation on a large-scale high-end computer. In this paper, we designed and implemented a large-scale TPC-C evaluation system based on the latest TPC-C specification using solid-state drive (SSD) storage devices. By analyzing the characteristics of the TPC-C workload, we propose a series of system-level optimization methods to improve the TPC-C performance. First, we propose an approach based on SmallFile table space to organize the test data in a round-robin method on all of the disk array partitions; this can make full use of the underlying disk arrays. Second, we propose using a NOOP-based disk scheduling algorithm to reduce the utilization rate of processors and improve the average input/output service time. Third, to improve the system translation lookaside buffer hit rate and reduce the processor overhead, we take advantage of the huge page technique to manage a large amount of memory resources. Lastly, we propose a locality-aware interrupt mapping strategy based on the asymmetry characteristic of non-uniform memory access systems to improve the system performance. Using these optimization methods, we performed the TPC-C test on two large-scale high-end computers using SSD arrays. The experimental results show that our methods can effectively improve the TPC-C performance. For example, the performance of the TPC-C test on an Intel Westmere server reached 1.018 million transactions per minute.

A software cybernetics approach to self-tuning performance of on-line transaction processing systems

We construct two layer fuzzy rules based on the collected data.We develop algorithms from software cybernetics view to reconfigure the performance control parameters.We show self-tuning performance for On-Line Transaction Processing. Self-tuning performance of On-Line Transaction Processing (OLTP) Systems is a challenging and time-consuming task since multiple performance parameters are needed to be automatically configured in Database Management Systems (DBMSs). In this paper, we present a software cybernetics approach to self-tune the performance of DBMSs. A DBMS is designed with an adaptive control based on fuzzy logic such that it has the capability to control objects, i.e., the performance parameters, and update the controller itself, i.e., a set of fuzzy rules in our case. The principles and concepts in software cybernetics are applied to guide the synthesis of software controllers for monitoring and adapting system behaviors. Experimental results for On-Line Transaction Processing using TPC-C, a benchmark of the Transaction Processing Performance Council, show that the proposed method is feasible and effective.

Keeping the TPC relevant!

The Transaction Processing Performance Council (TPC) is a nonprofit organization founded in 1988 to define transaction processing and database benchmarks. Since then, the TPC has played a crucial role in providing the industry with relevant standards for total system performance, price-performance, and energy-efficiency comparisons. TPC benchmarks are widely used by database researchers and academia. Historically known for database-centric standards, the TPC has developed a benchmark for virtualization and is currently developing a multisource data integration benchmark. The technology landscape is changing at a rapid pace, challenging industry experts and researchers to develop innovative techniques for evaluating, measuring, and characterizing the performance of modern application systems. The Technology Conference series on Performance Evaluation and Benchmarking (TPCTC), introduced in 2009, and the new TPC-Express initiatives are steps taken by the TPC to be relevant in the coming years and beyond.

The Linked Data Benchmark Council Project

Despite the fast growth and increasing popularity, the broad field of RDF and Graph database systems lacks an independent authority for developing benchmarks, and for neutrally assessing benchmark results through industry-strength auditing which would allow to quantify and compare the performance of existing and emerging systems.Inspired by the impact of the Transaction Processing Performance Council (TPC) Benchmarks on relational databases, the LDBC consortium formed by University and Industry researchers and practitioners has recently launched a European Commision sponsored project that will offer the first comprehensive set of open and vendor-independent benchmarks for RDF and Graph technologies. The consortium will incorporate the Linked Data Benchmark Council (LDBC) which will survive the project and will supervise the process of obtaining and reporting results as well as fostering the creation and maintenance of new and existing benchmarks. This paper describes the state-of-the-art benchmarks in RDF and Graph databases and overviews the technical challenges that should be addressed in the development of such benchmarks. With this paper we would like to invite the readers to participate in the LDBC effort towards the development of Linked Data Benchmarks, both from the user prospective (by sharing available usage scenarios, datasets, query workloads) and the vendor perspective (by reporting the results of systems and research prototypes).

Benchmarking Big Data Systems and the BigData Top100 List.

"Big data" has become a major force of innovation across enterprises of all sizes. New platforms with increasingly more features for managing big datasets are being announced almost on a weekly basis. Yet, there is currently a lack of any means of comparability among such platforms. While the performance of traditional database systems is well understood and measured by long-established institutions such as the Transaction Processing Performance Council (TCP), there is neither a clear definition of the performance of big data systems nor a generally agreed upon metric for comparing these systems. In this article, we describe a community-based effort for defining a big data benchmark. Over the past year, a Big Data Benchmarking Community has become established in order to fill this void. The effort focuses on defining an end-to-end application-layer benchmark for measuring the performance of big data applications, with the ability to easily adapt the benchmark specification to evolving challenges in the big data space. This article describes the efforts that have been undertaken thus far toward the definition of a BigData Top100 List. While highlighting the major technical as well as organizational challenges, through this article, we also solicit community input into this process.

TPC-E vs. TPC-C

TPC-E is a new OLTP benchmark recently approved by the Transaction Processing Performance Council (TPC). In this paper, we compare TPC-E with the familiar TPCC benchmark in order to understand the behavior of the new TPC-E benchmark. In particular, we compare the I/O access patterns of the two benchmarks by analyzing two OLTP disk traces. We find that (i) TPC-E is more read intensive with a 9.7:1 I/O read to write ratio, while TPC-C sees a 1.9:1 read-to-write ratio; and (ii) although TPC-E uses pseudo-realistic data, TPC-E's I/O access pattern is as random as TPC-C. The latter suggests that like TPC-C, TPC-E can benefit from SSDs, which have superior random I/O support. To verify this, we replay both disk traces on an Intel X25-E SSD and see dramatic improvements for both TPC-C and TPC-E.

Application of Queueing Network Models in the Performance Evaluation of Database Designs

In this paper, we model database designs using queueing networks, giving visibility to the dynamic behaviour of the database design and allowing the database designer to experiment with different design decisions. Our approach is to abstract away the more detailed levels of the database system design by concentrating on the information that is available to the database designer at design time. It differs from other methods of database system performance evaluation in that the performance assessment is specifically targeted at the database design, not at the database system software architecture. We present a bottleneck evaluation of the Transaction Processing Performance Council TPC-C benchmark under different workload conditions and demonstrate how this affects database design decisions.

Energy cost, the key challenge of today's data centers

Historically, performance and price-performance of computer systems have been the key purchasing arguments for customers. With rising energy costs and increasing power use due to the ever-growing demand for computing power (servers, storage, networks), electricity bills have become a significant expense for today's data centers. In the very near future, energy efficiency is expected to be one of the key purchasing arguments. Some performance organizations, such as SPEC, have developed power benchmarks for single servers (SPECpower_ssj2008), but so far, no benchmark exists that measures the power consumption of transaction processing systems. In this paper, we develop a power consumption model based on data readily available in the TPC-C full disclosure report of published benchmarks. We verify our model with measurements taken from three fully scaled and optimized TPC-C configurations including client (middle-tier) systems, database server, and storage subsystem. By applying this model to a subset of 7 years of TPC-C results, we identify the most power-intensive components and demonstrate the existing power consumption trends over time. Assuming similar trends in the future, the hardware enhancements alone will not be able to satisfy the demand for energy efficiency. In its outlook, this paper looks at potential hardware and software enhancements to meet the energy efficiency demands of future systems. Realizing the importance of energy efficiency, the Transaction Processing Performance Council (TPC) has formed a working group to look into adding energy efficiency metrics to all its benchmarks. This paper is expected to complement this initiative.

A Simulation Model to Develop QoS Control Strategies for Application Servers

The development of new Quality of Service (QoS) control strategies is essential for improving the global quality of the services provided by application servers operating in business-to-business (B2B) scenarios. Although the development and validation of new QoS control strategies based on prototypes is costly and time-consuming, simulation techniques can noticeably reduce the development effort. A simulation model of the application server and its clients is required. Later, any QoS control strategy can be integrated into the model between the clients and the server. The available models are too simplistic to represent the complex applications currently executed in application servers. This article introduces a new model, which is capable of representing the current applications with a high degree of accuracy. As an application representative of the modern B2B environments, the TPC-App (Transaction Processing Performance Council) benchmark has been modeled.

Benchmarking data warehouses

Data warehouse architectural choices and optimization techniques are critical to decision support query performance. To facilitate these choices, the performance of the designed data warehouse must be assessed, usually with benchmarks. These tools can either help system users comparing the performances of different systems, or help system engineers testing the effect of various design choices. While the Transaction Processing Performance Council's standard benchmarks address the first point, they are not tunable enough to address the second one and fail to model different data warehouse schemas. By contrast, our Data Warehouse Engineering Benchmark (DWEB) allows generating various ad-hoc synthetic data warehouses and workloads. DWEB is implemented as a Java free software that can be interfaced with most existing relational database management systems. The full specifications of DWEB, as well as experiments we performed to illustrate how our benchmark may be used, are provided in this paper.

ADMiRe: an algebraic data mining approach to system performance analysis

Performance analysis of computing systems is an increasingly difficult task due to growing system complexity. Traditional tools rely on ad hoc procedures. With these, determining which of the manifold system and workload parameters to examine is often a lengthy and highly speculative process. The analysis is often incomplete and, therefore, prone to revealing faulty conclusions and not uncovering useful tuning knowledge. We address this problem by introducing a data mining approach called ADMiRe (analyzer for data mining results). In this scheme, regression analysis is first applied to performance data to discover correlations between various system and workload parameters. The results of this analysis are summarized in sets of regression rules. The user can then formulate intuitive algebraic expressions to manipulate these sets of rules to capture critical information. To demonstrate this approach, we use ADMiRe to analyze an Oracle database system running the TPC-C (Transaction Processing Performance Council) benchmark. The results generated by ADMiRe were confirmed by Oracle experts. We also show that by applying ADMiRe to Microsoft Internet Information Server performance data, we can improve system performance by 20 percent.

TPC-W: a benchmark for e-commerce

When choosing an e-commerce site's hardware and software configuration, we need to know how a specific combination of Web servers, commerce servers, database servers, and supporting hardware will handle a desired load level. Benchmarks let us compare competing alternatives. Researchers have extensively studied workloads on Web sites that provide information and have characterized their performance at the level of HTTP requests. My colleagues and I have also conducted studies to understand e-commerce site workloads and to search for invariants that cut across more than one type of e-commerce site. However, there is currently only one available benchmark for e-commerce sites: TPC benchmark W, designed by the Transaction Processing Performance Council. I explore TPC-W's main features, advantages, and limitations.

I/O reference behavior of production database workloads and the TPC benchmarks—an analysis at the logical level

As improvements in processor performance continue to far outpace improvements in storage performance, I/O is increasingly the bottleneck in computer systems, especially in large database systems that manage huge amoungs of data. The key to achieving good I/O performance is to thoroughly understand its characteristics. In this article we present a comprehensive analysis of the logical I/O reference behavior of the peak productiondatabase workloads from ten of the world's largest corporations. In particular, we focus on how these workloads respond to different techniques for caching, prefetching, and write buffering. Our findings include several broadly applicable rules of thumb that describe how effective the various I/O optimization techniques are for the production workloads. For instance, our results indicate that the buffer pool miss ratio tends to be related to the ratio of buffer pool size to data size by an inverse square root rule. A similar fourth root rule relates the write miss ratio and the ration of buffer pool size to data size. In addition, we characterize the reference characteristics of workloads similar to the Transaction Processing Performance Council (TPC) benchmarks C (TPC-C) and D(TPC-D), which are de facto standard performance measures for online transaction processing (OLTP) systems and decision support systems (DSS), respectively. Since benchmarks such as TPC-C and TPC-D can only be used effectively if their strengths and limitations are understood, a major focus of our analysis is to identify aspects of the benchmarks that stress the system differently than the production workloads. We discover that for the most part, the reference behavior of TPC-C and TPC-D fall within the range of behavior exhibited by the production workloads. However, there are some noteworthy exceptions that affect well-known I/O optimization techniques such as caching (LRU is further from the optimal for TPC-C, while there is little sharing of pages between transactions for TPC-D), prefetching (TPC-C exhibits no significant sequentiality), and write buffering (write buffering is lees effective for the TPC benchmarks). While the two TPC benchmarks generally complement one another in reflecting the characteristics of the production workloads, there remain aspects of the real workloads that are not represented by either of the benchmarks.