High Query Performance Research Articles

Performance Comparison of Hybrid Storage on OLAP Database Server Virtualization

In today's digital era, efficient database management is important for companies to support analysis and decision-making processes. The use of On Line Analytical Processing (OLAP) in databases requires optimal storage configuration, especially in virtualization environments. This study aims to evaluate the performance of OLAP databases using hybrid storage configurations, combining Solid State Drives (SSD) and Hard Disk Drives (HDD), and identify the best configuration to improve database performance. The research method was carried out using the TPC-H benchmark on four database systems: Oracle, SQL Server, MySQL, and PostgreSQL. Four storage configurations were tested by allocating SSD for database storage and HDD for the operating system and temporary files, measured with 10 and 100 virtual users. The results showed that SQL Server provided the best performance in all configurations, with faster query execution times than other databases. The configuration with SSD for database storage and HDD for the operating system and temporary files proved to be the most efficient in reducing query latency. The conclusion of this study emphasizes the importance of using hybrid storage configurations in improving OLAP database performance in virtualization environments. Configuring with SSD as database storage and HDD for operating system and temporary files is the best choice to achieve high query performance.

Vexless: A Serverless Vector Data Management System Using Cloud Functions

Cloud functions, exemplified by AWS Lambda and Azure Functions, are emerging as a new computing paradigm in the cloud. They provide elastic, serverless, and low-cost cloud computing, making them highly suitable for bursty and sparse workloads, which are quite common in practice. Thus, there is a new trend in designing data systems that leverage cloud functions. In this paper, we focus on vector databases, which have recently gained significant attention partly due to large language models. In particular, we investigate how to use cloud functions to build high-performance and cost-efficient vector databases. This presents significant challenges in terms of how to perform sharding, how to reduce communication overhead, and how to minimize cold-start times. In this paper, we introduce Vexless, the first vector database system optimized for cloud functions. We present three optimizations to address the challenges. To perform sharding, we propose a global coordinator (orchestrator) that assigns workloads to Cloud function instances based on their available hardware resources. To overcome communication overhead, we propose the use of stateful cloud functions, eliminating the need for costly communications during synchronization. To minimize cold-start overhead, we introduce a workload-aware Cloud function lifetime management strategy. Vexless has been implemented using Azure Functions. Experimental results demonstrate that Vexless can significantly reduce costs, especially on bursty and sparse workloads, compared to cloud VM instances, while achieving similar or higher query performance and accuracy.

When Tree Meets Hash: Reducing Random Reads for Index Structures on Persistent Memories

Indexing structures are widely used in modern data-processing applications to support high-performance queries, and there are a variety of recent designs specifically optimized for the newly available persistent memory (PM). The primary focus of previous PM indexes is on reducing the expensive PM writes for persisting data. However, we find that in tree-based PM indexes, because of the smaller performance gap between writes and random reads on real PM devices, the read-intensive tree traversal phase dominates the overall latency. This observation calls for further optimizations on existing indexing structures for PM. In this paper, we propose Extendible Radix Tree (ERT), an efficient indexing structure for PM that significantly reduces tree heights to minimize random reads, while still maintaining fast in-node search speed. The key idea is to use extendible hashing for each node in a radix tree. This design allows us to have a relatively large fanout of the radix tree to keep the tree height small, and also to realize constant-time lookups within a node. Using extendible hashing also allows for incremental node modification without excessive writes during inserts and updates. Range queries are efficiently and robustly handled by enforcing partial ordering among the keys in the hash table of each node without introducing more hash collisions. Our experiments on both synthetic and real-world data sets demonstrate that ERT achieves up to 2.65×, 4.41×, and 2.43× speedups for search, insert, and range queries over the respectively state-of-the-art PM index.

DATA WAREHOUSE MODEL BASED ON KIMBALL METHODOLOGY TO SUPPORT DECISION MAKING IN ASSET MAINTENANCE

ITSM e-Prime is an ICT service management application based on ITSM framework owned by Pusintek that includes service desk, incident management, problem management, change management, release management, and configuration management processes. Currently there is a problem in determining the number of devices that will be included in the device maintenance contract or determining the number of devices that need to be replaced in a given year. The objective of this research is to build an asset management data warehouse so that it can be utilized by the Data Analysis and Presentation Team to produce a dashboard that presents data on network infrastructure assets that need to be maintained or replaced for budget planning needs. This descriptive verification analysis research used nine out of ninety tables from the ITSM e-Prime application and applied dimensional modeling Kimball to build a data warehouse because this methodology offers high query performance and understandable by end-user. The resulting data warehouse were tables in the form of star-schema. The tests were carried out by qualitative methods, namely quality testing by users (user acceptance test and blackbox testing) and quantitative method, namely comparing the number of infrastructure devices included in the maintenance contract in 2022. The final result of this research is a data warehouse consisting of fact table F_infrastructure and dimension table D_Merk, D_Area, D_Kategori, D_EoS, D_Garansi, and D_StatusPemeliharaan with acceptance percentage of 95% based on the test results.

PLIN

Non-Volatile Memory (NVM) has emerged as an alternative to next-generation main memories. Although many tree indices have been proposed for NVM, they generally use B+-tree-like structures. To further improve the performance of NVM-aware indices, we consider integrating learned indexes into NVM. The challenges of such an integration are two fold: (1) existing NVM indices rely on small nodes to accelerate insertions with crash consistency, but learned indices use huge nodes to obtain a flat structure. (2) the node structure of learned indices is not NVM friendly, meaning that accessing a learned node will cause multiple NVM block misses. Thus, in this paper, we propose a new persistent learned index called PLIN. The novelty of PLIN lies in four aspects: an NVM-aware data placement strategy, locally unordered and globally ordered leaf nodes, a model copy mechanism, and a hierarchical insertion strategy. In addition, PLIN is proposed for the NVM-only architecture, which can support instant recovery. We also present optimistic concurrency control and fine-grained locking mechanisms to make PLIN scalable to concurrent requests. We conduct experiments on real persistent memory with various workloads and compare PLIN with APEX, PACtree, ROART, TLBtree, and Fast&Fair. The results show that PLIN achieves 2.08x higher insertion performance and 4.42x higher query performance than its competitors on average. Meanwhile, PLIN only needs ~30 μs to recover from a system crash.

Aggregated Boolean Query Processing for Document Retrieval in Edge Computing

Search engines use significant hardware and energy resources to process billions of user queries per day, where Boolean query processing for document retrieval is an essential ingredient. Considering the huge number of users and large scale of the network, traditional query processing mechanisms may not be applicable since they mostly depend on a centralized retrieval method. To remedy this issue, this paper proposes a processing technique for aggregated Boolean queries in the context of edge computing, where each sub-region of the network corresponds to an edge network regulated by an edge server, and the Boolean queries are evaluated in a distributed fashion on the edge servers. This decentralized query processing technique has demonstrated its efficiency and applicability for the document retrieval problem. Experimental results on two real-world datasets show that this technique achieves high query performance and outperforms the traditional centralized methods by 2–3 times.

To share or not to share vector registers?

Query execution techniques in database systems constantly adapt to novel hardware features to achieve high query performance, in particular for analytical queries. In recent years, vectorization based on the Single Instruction Multiple Data parallel paradigm has been established as a state-of-the-art approach to increase single-query performance. However, since concurrent analytical queries running in parallel often access the same columns and perform a same set of vectorized operations, data accesses and computations among different queries may be executed redundantly. Various techniques have already been proposed to avoid such redundancy, ranging from concurrent scans via the construction of materialized views to applying multiple query optimization techniques. Continuing this line of research, we investigate the opportunity of sharing vector registers for concurrently running queries in analytical scenarios in this paper. In particular, our novel sharing approach relies on processing data elements of different queries together within a single vector register. As we are going to show, sharing vector registers to optimize the execution of concurrent analytical queries can be very beneficial in single-threaded as well as multi-thread environments. Therefore, we demonstrate the feasibility and applicability of such a novel work sharing strategy and thus open up a wide spectrum of future research opportunities.

An NVM SSD-based High Performance Query Processing Framework for Search Engines

Commercial search engines generally maintain hundreds of thousands of machines equipped with large sized DRAM which incurs high hardware cost since DRAM is expensive. Recently, NVM Optane SSD has been considered as a promising underlying storage device due to its price advantage and speed advantage. However, to achieve a comparable efficiency performance with in-memory index, applying NVM to both latency and I/O bandwidth critical applications still face non-trivial challenges, because NVM has much lower I/O speed and bandwidth compared to DRAM. In this paper, we propose an NVM SSD-optimized query processing framework, aiming to address both the latency and bandwidth issues of using NVM in search engines. First, we propose a pipelined query processing methodology which significantly reduces the I/O waiting time. Second, we propose a cache-aware query reordering algorithm which enables queries sharing more data to be processed adjacently. Third, we propose a data prefetching mechanism which reduces the extra thread waiting time and improves bandwidth utilization. Moreover, we propose intra-query parallel mechanisms for long-tail queries, including query subtask scheduling, heap concurrent access strategy, query parallelism prediction and adaptive pipelining. Extensive experimental studies show that our framework significantly outperforms the state-of-the-art baselines, which obtains comparable processing latency and throughput with DRAM in both inter-query and intra-query parallel scenarios.

Spatial Data Sequence Selection Based on a User-Defined Condition Using GPGPU

The size of spatial data is growing intensively due to the emergence of and the tremendous advances in technology such as sensors and the internet of things. Supporting high-performance queries on this large volume of data becomes essential in several data- and compute-intensive applications. Unfortunately, most of the existing methods and approaches are based on a traditional computing framework (uniprocessors) which makes them not scalable and not adequate to deal with large-scale data. In this work, we present a high-performance query for massive spatio–temporal data. The query consists of selecting fixed size raster subsequences, based on the average of their region of interest, from a spatio–temporal raster sequence satisfying a user threshold condition. In our paper, for the purpose of simplification, we consider that the region of interest is the entire raster and not only a subregion. Our aim is to speed up the execution using parallel primitives and pure CUDA. Furthermore, we propose a new method based on a sorting step to save computations and boost the speed of the query execution. The test results show that the proposed methods are faster and good performance is achieved even with large-scale rasters and data.

LineageChain: a fine-grained, secure and efficient data provenance system for blockchains

The success of Bitcoin and other cryptocurrencies is drawing significant interest to blockchains. A blockchain system implements a tamper-evident ledger for recording transactions that modify some global states. The system captures the entire evolution history of the states. The management of that history, also known as data provenance or lineage, has been studied extensively in database systems. However, querying data history in existing blockchains can only be done by replaying all transactions. This approach is applicable to large-scale, offline analysis, but is not suitable for online transaction processing. In this paper, we identify a new class of blockchain applications whose execution logics depend on provenance information at runtime. We first motivate the need for adding native provenance support to blockchains. We then present LineageChain, a fine-grained, secure and efficient provenance system for blockchains. LineageChain exposes lineage information to smart contracts runtime via simple and elegant interfaces that efficiently and securely support provenance-dependent contracts. LineageChain captures provenance during contract execution and stores it in a Merkle tree. LineageChain provides a novel skip list index designed for efficient provenance queries. We have implemented LineageChain on top of Fabric and a blockchain optimized storage system called ForkBase. Our extensive evaluation of LineageChain demonstrates its benefits to the new class of blockchain applications, its high query performance and its small storage overhead.

A presentation and retrieval hash scheme of images based on principal component analysis

Image representation and approximate query is always a research challenge and is affected greatly by the dimension and size of images. Since hash-based methods and binary encodings in combination with other techniques, such as kernel tricks, a longer binary code and mapping vectors rotation, can maintain a linear query time and query accuracy, they have been used in this area broadly. This paper develops principal component analysis hashing (PCAH) and unequal length of binary coding to divide images into more categories, denoted as PCA-MD, to improve accuracy of the representation and lookup of images. This paper firstly proves that the eigenvector mapping is locality sensitive, which is the basis for more classes division. For the anisotropy of the eigenvectors, PCA-MD utilizes an unequal length of binary coding and fewer eigenvectors, rather than an equal code, to divide the images mapped on every eigenvector to more categories. Moreover, L1-norm distance is applied to measure the distances of images to avoid the enormous computation of Euclidean distance. Theoretical analysis and extensive experimental results demonstrate that the PCA-MD has a higher query performance and a slight longer run time than the state-of-the-art approaches based on the Hamming distance. This in turn verifies that PCAH is a locality sensitive hash and that partitioning into more categories rather than only two categories is reasonable.

Optimizing bitmap index encoding for high performance queries

SummaryMany sources such as historical archives, sensor readings, health systems, and machine records produce ever‐increasing but often unchanging data. These accumulating data create a need for faster processing. Bitmap index, which can take advantage of multi‐core and multiprocessor systems, is designed to process data that increase over time but do not change frequently. It has a well‐known advantage, especially in queries on data with low cardinality. However, bitmap index can handle high cardinality data efficiently because it can use its own compression algorithm. Bitmap index has many encoding schemes that affect query processing time. In this study, we developed an algorithm that improves query performance by using optimal encoding among bitmap encodings. With this optimization algorithm, we witnessed up to 40% performance increase in queries made with bitmap indexes created with different encodings. Furthermore, in comparison with a commonly used relational database, we found significant improvements in the number of query operations per second performed on optimized encoded bitmap indexes generated by the introduced algorithm.

Cuckoo index

In modern data warehousing, data skipping is essential for high query performance. While index structures such as B-trees or hash tables allow for precise pruning, their large storage requirements make them impractical for indexing secondary columns. Therefore, many systems rely on approximate indexes such as min/max sketches (ZoneMaps) or Bloom filters for cost-effective data pruning. For example, Google PowerDrill skips more than 90% of data on average using such indexes. In this paper, we introduce Cuckoo Index (CI), an approximate secondary index structure that represents the many-to-many relationship between keys and data partitions in a highly space-efficient way. At its core, CI associates variable-sized fingerprints in a Cuckoo filter with compressed bitmaps indicating qualifying partitions. With our approach, we target equality predicates in a read-only (immutable) setting and optimize for space efficiency under the premise of practical build and lookup performance. In contrast to per-partition (Bloom) filters, CI produces correct results for lookups with keys that occur in the data. CI allows to control the ratio of false positive partitions for lookups with non-occurring keys. Our experiments with real-world and synthetic data show that CI consumes significantly less space than per-partition filters for the same pruning power for low-to-medium cardinality columns. For high cardinality columns, CI is on par with its baselines.

High Performance Query Execution and Graph Visualization for Effectual Big Data Analytics with Cloud

The domain of data science contains enormous approaches and high performance techniques in which there is need to evaluate the data from multiple dimensions so that the effectual outcomes and predictive knowledge can be extracted. Data Science and Analytics is now days one of the conspicuous streams of advanced knowledge discovery. Following are the key constituents and assorted elements which are required in the data science for cavernous and multi-dimensional analytics of the datasets including Streaming of Data from Multiple Sources and Channels, Pre-Processing and Cleaning of Real Time Streaming Data, Feature Engineering and Extraction of Prime Elements from Datasets, Numerical Analysis and Scientific Computations, Statistical Analytics on Datasets, Data Engineering Visualization, Plotting and Predictive Analytics. The paper is presenting the usage patterns and cavernous analytics of big data with the high performance visualization using Grafana.

Simplified schema queries for supporting BIM-based rule-checking applications

Following decades of research, the potential of automated rule-checking applications for building models to boost the productivity in the AECO industry has yet to fully materialize. Most efforts have been dedicated to dealing with the complexities associated with formalizing rules related to building designs for computability. To make automated rule checking applications successful, data availability and accessibility are critical components that must be addressed. Unfortunately, to-date they have not received the same level of attention. To lay a solid foundation for a robust environment of automated BIM-based rule-checking systems, this paper proposes a novel approach of transforming building data into a simplified schema (BIMRLSS) as a means of addressing this critical issue. The paper also describes an implementation of the proposed approach on a traditional database management system, which offers the primary advantage of facilitating high-performance queries and supporting a wide range of query types in real-time. More importantly, this approach not only allows alphanumeric data queries, but also enables queries on the geometry by using various spatial operators. Allowing complex spatial-based queries provides a means to significantly simplify the traditional programmatic approach to BIM-based rule-checking. This data-focused approach allows us to lower a significant barrier that currently prevents BIM-based rule-checking systems from reaching their full potential, making the checking process easier and more robust. To highlight the implications and practical benefits of the proposed method, this paper also demonstrates several application scenarios by utilizing a wide range of queries.

Constructing Effective Caches of Shortest Path Queries on Road Networks

How to effectively utilize caching technology to support high-performance shortest path queries on road networks has become an important research problem since the popularization of location-based services. Most of the existing methods design a statistical model to construct the cache with the shortest paths, and mainly consider the related query frequency obtained from the historical query logs. As a result, the paths in the cache may be concentrated in a few hot-spot areas of the road network, such as the prosperous areas of a city. Additionally, queries from remote areas may never be hit in the cache, which results in the cache hit ratio is not fully optimized. To address this problem, we define an effective model to quantify the effect of cached shortest paths; the model not only considers the related query frequency in the log but also the coverage of the paths on the road network. Then, we propose an efficient cache construction method to select the cached shortest paths from the query log. Furthermore, we design a hybrid index for the shortest paths in the cache, so that the efficiency of shortest path querying is significantly improved. Finally, we conduct comprehensive experimental studies on different datasets to verify that the cache hit ratio is greatly improved by using the proposed cache construction approaches.

PathQuery Pregel: high-performance graph query with bulk synchronous processing

High-performance graph query systems are a scalable way to mine information in Knowledge Graphs, especially when the queries benefit from a high-level expressive query language. This paper presents techniques to algorithmically compile queries expressed in a high-level language (e.g., Datalog) into a directed acyclic graph query plan and details how these queries can be run on a Pregel graph vertex-centric compute system. Our solution, called PathQuery Pregel, creates plans for any conjunctive or disjunctive queries with aggregation and negation; we describe how the query execution extracts graph results optimally while avoiding many join operations where parallel map execution is permitted. We provide details of how we scaled this system out to execute large set of queries in parallel over the Google Knowledge Graph, a graph of 70B edges, or facts; we describe our production experience with PathQuery Pregel.

Integration of large-scale data processing systems and traditional parallel database technology

In 2009 we explored the feasibility of building a hybrid SQL data analysis system that takes the best features from two competing technologies: large-scale data processing systems (such as Google MapReduce and Apache Hadoop) and parallel database management systems (such as Greenplum and Vertica). We built a prototype, HadoopDB, and demonstrated that it can deliver the high SQL query performance and efficiency of parallel database management systems while still providing the scalability, fault tolerance, and flexibility of large-scale data processing systems. Subsequently, HadoopDB grew into a commercial product, Hadapt, whose technology was eventually acquired by Teradata. In this paper, we provide an overview of HadoopDB's original design, and its evolution during the subsequent ten years of research and development effort. We describe how the project innovated both in the research lab, and as a commercial product at Hadapt and Teradata. We then discuss the current vibrant ecosystem of software projects (most of which are open source) that continued HadoopDB's legacy of implementing a systems level integration of large-scale data processing systems and parallel database technology.

A Low-cost Disk Solution Enabling LSM-tree to Achieve High Performance for Mixed Read/Write Workloads

LSM-tree has been widely used in data management production systems for write-intensive workloads. However, as read and write workloads co-exist under LSM-tree, data accesses can experience long latency and low throughput due to the interferences to buffer caching from the compaction, a major and frequent operation in LSM-tree. After a compaction, the existing data blocks are reorganized and written to other locations on disks. As a result, the related data blocks that have been loaded in the buffer cache are invalidated since their referencing addresses are changed, causing serious performance degradations. To re-enable high-speed buffer caching during intensive writes, we propose Log-Structured buffered-Merge tree (simplified as LSbM-tree) by adding a compaction buffer on disks to minimize the cache invalidations on buffer cache caused by compactions. The compaction buffer efficiently and adaptively maintains the frequently visited datasets. In LSbM, strong locality objects can be effectively kept in the buffer cache with minimum or no harmful invalidations. With the help of a small on-disk compaction buffer, LSbM achieves a high query performance by enabling effective buffer caching, while retaining all the merits of LSM-tree for write-intensive data processing and providing high bandwidth of disks for range queries. We have implemented LSbM based on LevelDB. We show that with a standard buffer cache and a hard disk, LSbM can achieve 2x performance improvement over LevelDB. We have also compared LSbM with other existing solutions to show its strong cache effectiveness.

Energy Efficiency in Large Scale Information Retrieval Systems

Web search engines are large scale information retrieval systems, which provide easy access to information on the Web. High performance query processing is fundamental for the success of such systems. In fact, web search engines can receive billions of queries per day. Additionally, the issuing users are often impatient and expect sub-second response times to their queries (e.g., 500 ms). For such reasons, search companies adopt distributed query processing strategies to cope with huge volumes of incoming queries and to provide sub-second response times. Web search engines perform distributed query processing on computer clusters composed by thousands of computers and hosted in large data centers. While data center facilities enable large-scale online services, they also raise economical and environmental concerns. Therefore, an important problem to address is how to reduce the energy expenditure of data centers. Moreover, another problem to tackle is how to reduce carbon dioxide emissions and the negative impact of the data centers on the environment. A large part of the energy consumption of a data center could be accounted to inefficiencies in its cooling and power supply systems. However, search companies already adopt state-of-the art techniques to reduce the energy wastage of such systems, leaving little room for more improvements in those areas. Therefore, new approaches are necessary to mitigate the environmental impact and the energy expenditure of web search engines. One option is to reduce the energy consumption of computing resources to mitigate the energy expenditure and carbon footprint of a search company. In particular, reducing the energy consumption of CPUs represents an attractive venue for web search engines. Currently, CPU cores frequencies are typically managed by operating system components, called frequency governors. We propose to delegate the CPU power management from the OS frequency governors to the query processing application [2]. Such search engine-specific governors can reduce up to 24% a server power consumption, with only limited (but uncontrollable) drawbacks in the quality of search results with respect to a system running at maximum CPU frequency. Since users can hardly notice response times that are faster than their expectations we advise that web search engine should not process queries faster than user expectations and, consequently, we propose the Predictive Energy Saving Online Scheduling (PESOS) algorithm, to select the most appropriate CPU frequency to process a query by its deadline, on a per-core basis [3]. PESOS can reduce the CPU energy consumption of a query processing server from 24% up to 48% when compared to a high performance system running at maximum CPU core frequency. To reduce the carbon footprint of web search engines, another option consists in using green energy to partially power their data centers. Stemming from these observations, we propose a new query forwarding algorithm that exploits both the green energy sources available at different data centers and the differences in market energy prices [1]. The proposed solution maintains a high query throughput, while reducing by up to 25% the energy operational costs of multi-center search engines.