OLTP Systems Research Articles

Proposal for the Implementation of Database Systems Teaching in Pharmacy Courses in Terms of e-Health Support

In our paper, we will discuss the design and implementation of relational databases using MS Access. Relational database is one of the most widely used databases in terms of practice and information systems. We often encounter their implementation in many solutions, for example as hybrid OLAP databases of enterprise systems or also as OLTP systems - currently most of the classical database applications as we understand them. Their importance is therefore great from a modern point of view. In our paper, through the concrete example of a local pharmacy, we will try to create a practical application that will be fully functional, but will not require deep expertise; on the contrary, it will follow the modern trend that is noticeable in many other information systems, where the emphasis is on usage and not on lengthy training and close long-term professionalization. Our aim is to provide pharmacy students with knowledge, the acquisition of which will not go beyond a maximum of three 90-minute classes and yet provide practical knowledge of relational databases at more than a minimal level.

Transaction Scheduling: From Conflicts to Runtime Conflicts

This paper studies how to improve the performance of main memory multicore OLTP systems for executing transactions with conflicts. A promising approach is to partition transaction workloads into mutually conflict-free clusters, and distribute the clusters to different cores for concurrent execution. We show that if transactions in each cluster are properly scheduled, transactions that are traditionally considered conflicting can be executed without conflicts at runtime. In light of this, we propose to schedule transactions and reduce runtime conflicts, instead of partitioning based on the conventional notion of conflicts. We formulate the transaction scheduling problem to minimize runtime conflicts, and show that the problem is NP-complete. This said, we develop an efficient scheduling algorithm to improve parallelism. Moreover, for transactions that are not packed in batches, we show that runtime conflict analysis also helps reduce conflict penalties, by proposing a proactive deferring method. Using standard and enhanced benchmarks, we show that on average our scheduling and proactive deferring methods improve the throughput of existing partitioners and concurrency control protocols by 131% and 109%, respectively, up to 294% and 152%.

ByteHTAP

In recent years, at ByteDance, we see more and more business scenarios that require performing complex analysis over freshly imported data, together with transaction support and strong data consistency. In this paper, we describe our journey of building ByteHTAP, an HTAP system with high data freshness and strong data consistency. It adopts a separate-engine and shared-storage architecture. Its modular system design fully utilizes an existing ByteDance's OLTP system and an open source OLAP system. This choice saves us a lot of resources and development time and allows easy future extensions such as replacing the query processing engine with other alternatives. ByteHTAP can provide high data freshness with less than one second delay, which enables many new business opportunities for our customers. Customers can also configure different data freshness thresholds based on their business needs. ByteHTAP also provides strong data consistency through global timestamps across its OLTP and OLAP system, which greatly relieves application developers from handling complex data consistency issues by themselves. In addition, we introduce some important performance optimizations to ByteHTAP, such as pushing computations to the storage layer and using delete bitmaps to efficiently handle deletes. Lastly, we will share our lessons and best practices in developing and running ByteHTAP in production.

TO IMPLEMENT PARALLEL PERFORMANCE (PROCESSOR) IN DATA WAREHOUSE USING CLOUD COMPUTING SYSTEM

Databases today, irrespective of whether they are data warehouses, functioning data stores, or OLTP systems, enclose a huge volume of statistics. But, outcome and offering the correct statistics in an appropriate manner has to a challenge because of the huge amount of data complicated. Parallel performance is the competence that addresses this challenge. Cloud computing is developing of Parallel Performance (Processor) in Data warehouse. The development of cloud computing has made parallel processor derive into commons lives. Firstly, this paper develops the model of cloud computing and introduces two numerous traditional Parallel performance develops processing, When to Implement Parallel Performance or not and in-Memory Parallel Execution. The implementation of parallel performance has two levels. It has analysed and studied the principles, advantages and disadvantages of parallel performance develop processing. The results of this paper are intended to provide a reference for the development of parallel performance develops processing.

Machine learning‐based load distribution and balancing in heterogeneous database management systems

SummaryFor dynamic and continuous data analysis, conventional OLTP systems are slow in performance. Today's cutting‐edge high‐performance computing hardware, such as GPUs, has been used as accelerators for data analysis tasks, which traditionally leverage CPUs on classical database management systems (DBMS). When CPUs and GPUs are used together, the architectural heterogeneity, that is, leveraging hardware with different performance characteristics jointly, creates complex problems that need careful treatment for performance optimization. Load distribution and balancing are crucial problems for DBMSs working on heterogeneous architectures. In this work, focusing on a hybrid, CPU‐GPU database management system to process users' queries, we propose heuristical and machine‐learning‐based (ML‐based) load distribution and balancing models. In more detail, we employ multiple linear regression (MLR), random forest (RF), and Adaboost (Ada) models to dynamically decide the processing unit for each incoming query based on the response time predictions on both CPU and GPU. The ML‐based models outperformed the other algorithms, as well as the CPU and GPU‐only running modes with up to 27%, 29%, and 40%, respectively, in overall performance (response time) while answering intense real‐life working scenarios. Finally, we propose to use a hybrid load‐balancing model that would be more efficient than the models we tested in this work.

Spitz

Databases in the past have helped businesses maintain and extract insights from their data. Today, it is common for a business to involve multiple independent, distrustful parties. This trend towards decentralization introduces a new and important requirement to databases: the integrity of the data, the history, and the execution must be protected. In other words, there is a need for a new class of database systems whose integrity can be verified (or verifiable databases). In this paper, we identify the requirements and the design challenges of verifiable databases. We observe that the main challenges come from the need to balance data immutability, tamper evidence, and performance. We first consider approaches that extend existing OLTP and OLAP systems with support for verification. We next examine a clean-slate approach, by describing a new system, Spitz, specifically designed for efficiently supporting immutable and tamper-evident transaction management. We conduct a preliminary performance study of both approaches against a baseline system, and provide insights on their performance.

Workload aware incremental repartitioning of NoSQL for OLTP applications

Numerous applications are deployed on the web with the increasing popularity of internet. These include gaming and e-commerce web applications. These applications generate huge amount of the data. One particular machine cannot handle such data. It is difficult to scale out by using relational databases. OLTP system needs to be scalable and require fast response. Therefore the scalability becomes the challenge for the e-commerce applications. Data partitioning technique is used to improve the scalability of the system. Existing partitioning techniques does not consider the relation among tuples. These techniques do not handle incremental data and are suitable for those applications that required only sequential access to the data. It results in increasing the number of the distributed transactions. The work-load aware incremental repartitioning approach is used to balance the load among the partitions. Hypergraph representation technique represents transactional workload in graph form. In this technique, frequently used items are grouped together by using fuzzy C-means clustering algorithm. Tuple classification and migration algorithm is used for mapping clusters to partitions and after that tuples are migrated efficiently.

Workload aware incremental repartitioning of NoSQL for OLTP applications

Numerous applications are deployed on the web with the increasing popularity of internet. These include gaming and e-commerce web applications. These applications generate huge amount of the data. One particular machine cannot handle such data. It is difficult to scale out by using relational databases. OLTP system needs to be scalable and require fast response. Therefore the scalability becomes the challenge for the e-commerce applications. Data partitioning technique is used to improve the scalability of the system. Existing partitioning techniques does not consider the relation among tuples. These techniques do not handle incremental data and are suitable for those applications that required only sequential access to the data. It results in increasing the number of the distributed transactions. The work-load aware incremental repartitioning approach is used to balance the load among the partitions. Hypergraph representation technique represents transactional workload in graph form. In this technique, frequently used items are grouped together by using fuzzy C-means clustering algorithm. Tuple classification and migration algorithm is used for mapping clusters to partitions and after that tuples are migrated efficiently.

In-memory transaction processing: efficiency and scalability considerations

Traditional disk-resident OLTP systems were mainly designed for computers with relatively small memory. Driven by the advance of hardware, OLTP systems need to be redesigned for larger memory and multi-core environments. Compared to disk-resident systems, in-memory systems have significant performance advantages, from the perspectives of both transaction throughput and query latency. Their performance is no longer limited by disk I/Os. Instead, the efficiency and scalability over multi-core CPUs become more important. In this paper, we survey and summarize a wide spectrum of design and implementation considerations that may affect the efficiency or scalability of an in-memory OLTP system. These considerations are concerned with most of the main components of databases, including concurrency control, logging, indexing and transaction compilation. For each of the components, we provide some in-depth analysis based on recent research works. This survey also aims to provide some guidance for designing or implementing high-performance in-memory OLTP systems.

Improving optimistic concurrency control through transaction batching and operation reordering

OLTP systems can often improve throughput by batching transactions and processing them as a group. Batching has been used for optimizations such as message packing and group commits; however, there is little research on the benefits of a holistic approach to batching across a transaction's entire life cycle. In this paper, we present a framework to incorporate batching at multiple stages of transaction execution for OLTP systems based on optimistic concurrency control. Storage batching enables reordering of transaction reads and writes at the storage layer, reducing conflicts on the same object. Validator batching enables reordering of transactions before validation, reducing conflicts between transactions. Dependencies between transactions make transaction reordering a non-trivial problem, and we propose several efficient and practical algorithms that can be customized to various transaction precedence policies such as reducing tail latency. We also show how to reorder transactions with a thread-aware policy in multi-threaded OLTP architecture without a centralized validator. In-depth experiments on a research prototype, an opensource OLTP system, and a production OLTP system show that our techniques increase transaction throughput by up to 2.2x and reduce their tail latency by up to 71% compared with the start-of-the-art systems on workloads with high data contention.

Technology and implementation of a new OLTP system

Technology and implementation of a new OLTP system

Forecasting of customer demand for seasonal goods using retail curve vector

A method for forecasting the demand for seasonal goods using the vector of distribution of sales during the year, called retail curve vector is proposed. Components of retail curve vector are the weekly sales volumes of the considered or similar product, obtained on the basis of sales statistics for the previous calendar year. The condition of applicability of the proposed method is the fulfillment of the hypothesis about the convergence of the corresponding weekly sales volumes of two consecutive years and the hypothesis about the existence of goods groups with similar sales dynamics. The application of the method allows to build a demand forecast for the goods within the next week based on sales volumes data for the previous few weeks of the current and previous years, as well as sales volumes data for the same week of the previous year. Software architecture to implement proposed method for forecasting demand using a micro-service architecture based on the Google Cloud Platform is presented. Such components as Google Kubernetes Engine, Google BigQuery, Redis as used. To reduce the computational load on the main system, necessary data is copied to the OLAP system and required forecast is build without usage of the OLTP system. The results of numerical experiment on forecasting the demand for goods, obtained on the basis of real data, are presented. Comparison of the results of demand forecasting using the retail curve vector and the moving average method is performed. The possibility of using proposed method of demand forecasting as a component of an automated inventory control system in supply networks is shown.

Parallel replication across formats for scaling out mixed OLTP/OLAP workloads in main-memory databases

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. In order to tackle this challenging problem, we propose a novel database replication architecture called HANA 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) to enable real-time reporting by minimizing the propagation delay between the primary and replicas. It supports adaptive query routing depending on its predefined acceptable staleness range. Through extensive experiments with a concrete implementation available in a commercial product, we demonstrate that ATR achieves sub-second visibility delay even for update-intensive workloads, providing scalable OLAP performance without notable overhead to the primary. In addition, with extension of ATR to eager parallel replication, we demonstrate how the parallel log replay and its log-less replica recovery mechanisms improve run-time transaction performance under eager replication.

Designing Data Warehouse in Finance Company Study at PT ABC

Along with the development of technology and business needs that are more advanced, the more effective and efficient uses of Information Systems is an important thing for companies to continue competing in the era of globalization. Every company wants an appropriate Information System to run business activities smoothly. Using data warehouses can help PT ABC's problems in accommodating large amounts of data with its OLTP system which can then be processed in determining future strategies through OLAP systems. So as to get the reports needed in maintaining the consistency and improve the performance of the company.

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.

Exploiting Single-Threaded Model in Multi-Core In-Memory Systems

The widely adopted single-threaded OLTP model assigns a single thread to each static partition of the database for processing transactions in a partition. This simplifies concurrency control while retaining parallelism. However, it suffers performance loss arising from skewed workloads as well as transactions that span multiple partitions. In this paper, we present a dynamic single-threaded in-memory OLTP system, called LADS, that extends the simplicity of the single-threaded model. The key innovation in LADS is the separation of dependency resolution and execution into two non-overlapping phases for batches of transactions. After the first phase of dependency resolution, the record actions of the transactions are partitioned and ordered. Each independent partition is then executed sequentially by a single thread, avoiding the need for locking. By careful mapping of the tasks to be performed to threads, LADS is able to achieve a high degree of balanced parallelism. We evaluate LADS against H-Store, a partition-based database; DORA, a data-oriented transaction processing system; and SILO, a multi-core in-memory OLTP engine. The experimental study shows that LADS achieves up to 20x higher throughput than existing systems and exhibits better robustness with various workloads.

S-Store

Stream processing addresses the needs of real-time applications. Transaction processing addresses the coordination and safety of short atomic computations. Heretofore, these two modes of operation existed in separate, stove-piped systems. In this work, we attempt to fuse the two computational paradigms in a single system called S-Store. In this way, S-Store can simultaneously accommodate OLTP and streaming applications. We present a simple transaction model for streams that integrates seamlessly with a traditional OLTP system, and provides both ACID and stream-oriented guarantees. We chose to build S-Store as an extension of H-Store - an open-source, in-memory, distributed OLTP database system. By implementing S-Store in this way, we can make use of the transaction processing facilities that H-Store already provides, and we can concentrate on the additional features that are needed to support streaming. Similar implementations could be done using other main-memory OLTP platforms. We show that we can actually achieve higher throughput for streaming workloads in S-Store than an equivalent deployment in H-Store alone. We also show how this can be achieved within H-Store with the addition of a modest amount of new functionality. Furthermore, we compare S-Store to two state-of-the-art streaming systems, Esper and Apache Storm, and show how S-Store can sometimes exceed their performance while at the same time providing stronger correctness guarantees.

Lean and Efficient Business Tier for Performance Scaling

Large mission critical real-time legacy OLTP systems supporting the service sectors like banking, telecom, and financial services are monolithic in nature and thereby not flexible to enable business transformation which is the need of the hour due to emerging dynamic changes to business ecosystem. Broadly speaking, these large mission critical applications can be classified into three stages of activities: pre-processing, core business processing and post processing activities. This paper focuses on making the Business processing activities tier lean and efficient. By leveraging recent advances in technologies, a methodology is described by which the OLTP applications can be successfully transformed into agile systems. The methodology enables a view of the Business Tier in five dimensions. The first dimension is identifying the business‟s core critical path and how to make it lean. The second dimension is how to enhance concurrency of the activities in the critical path. The third dimension is to improve the parallelism in execution of concurrency. The fourth is to separate I/O operations off the critical path. The fifth being how to minimize contentions for shared resources to ensure higher efficiencies. The paper also presents, the results of the experiments carried out by applying the above recommendation and the performance improvements to decide the optimal setup for an environment for given workload. Addressing the five dimensions of Business Tier, this paper demonstrates the transformation that can be achieved which will enable the business to be agile and respond to market ecosystem demands in a very efficient and effective manner.

Extraction of High Utility Itemsets using Utility Pattern with Genetic Algorithm from OLTP System

Extraction of High Utility Itemsets using Utility Pattern with Genetic Algorithm from OLTP System

Development of Decision-Support System for Patient Affairs Management and Building of Data Mart

Objective: A purpose of this study was developing the Decision-Support Patient affairs management and building of Data-mart for individually specified department. Because it had a OLTP system where we were researching the hospital. It can not be analyzied variously because the data for the management, accounts, patient affairs and medical record were changing monthly/annually in conditions. also it can not have the specificity of the hospital management information. Methods: The Data Set that used for analysis process was examined with the patient's Database for a year. They were in hospital where locates in Sun Cheon. For developing patient affairs management program, The Microsoft Visual Basic 6.0 was used. and A analysis method of ROLAP which is based on Oracle relation Database was used for general analysis of required works. Results: The patient affairs Data-mart and Decision-Support System was built with the main results of the study. Those took advantage of the analysis for the each department's revenue and doctor's actual results, it also can compare the patient's hospital using frequency. With the analyzied results, each department can have their own special marketing. They can offer to the resistants a lecture, consultant and free service for the health. Conclusion: The management, the accounts, patient affairs and the medical record can have been analyzied various changing through the study. The host System had enormous volume, so it takes lots of times what they want to inquire. The host system usually took at night because of low transaction occurrence. The new system which was built with Data-mart had been improved the problem a lot.