Parallel Query

An effective exploitation of distributed computing environments to improve the performance of large advanced database applications requires parallel query processing techniques. This is because parallel query execution can be achieved by operations of pipelining and data partitioning, as well as independent subqueries. In order to develop efficient parallel query services in distributed environments, we present a query optimization, a dynamic parallel query scheduling algorithm, and an implementation mechanism for querying distributed objects. Our proposal has four important features. First, the query schedule dynamically schedules independent subqueries in parallel. Second, distributed processors are allowed to execute independent subqueries in random. Third, it doesn't limit the number of processors to deal with parallel query processing in a network based computing environment. Fourth, it provides simple operations to synchronize parallel query programs. We give the query optimization for parallel processing, show an algorithm of dynamic parallel query scheduling, describe an implementation method for the parallel query processing in a network-based distributed computing environment, and present the design of communication and synchronization for the parallel query processing.

Workstation clusters have high performance interconnects with programmable network processors, which facilitate interesting opportunities to offload certain application specific computation on them and hence enhance the performance of the parallel application. Our earlier work in this direction achieves enhanced performance and balanced utilization of resources by exploiting the programmable features of the network interface in parallel database query execution. In this paper, we extend our earlier work for studying parallel query execution with Bloom filters. We propose and evaluate a scheme to offload the Bloom filter operations to the network processor. Further we explore offloading certain tuple processing activities on to the network processor by adopting a network interface attached disk scheme. The above schemes yield a speedup of up to 1.13 over the base scheme with Bloom filter where all processing is done by the host processor and achieve balanced utilization of resources. In the presence of a disk buffer cache, which reduces both the disk and I/O traffic, offloading schemes improve the speedup to 1.24.

The upcoming generation of computer hardware poses several new challenges for database developers and engineers. Software in general and database management systems (DBMSs) in particular will no longer benefit from performance gains of future hardware due to increase clock speed, as it was the case for the last 35 years; instead, the number of cores per CPU will increase steadily. Today’s approach is to run each query on a single core or only a few different cores using parallel query execution. This approach suffers from several problems (e.g. contention problem) and therefore leads to poor speed up and scale up behavior. These observations open several important research questions on how to use the new multi-core CPU architecture for improving the overall performance of DBMSs. This paper outlines our approach for query processing on multi-core CPU architectures. We present an abstract architecture view for multi-core CPUs, meta operators to control and to interact with the hardware, and a new query operator model that makes use of the meta operators to control the parallel execution of a query over different cores. We illustrate how each of these parts fits in our framework for query processing on multi-core architectures.

Exadata doesn’t have a special way of executing parallel operations that is not available on other platforms running 11gR2 and 12c. However, parallel processing is a key component of Exadata because efficient handling of data warehouse workloads was a primary design goal for Exadata. In addition, because offloading/Smart Scan depends on direct path reads, which can be used by parallel query slaves, parallel operations take on a whole new importance. Traditionally, the efficient use of parallel query has required careful control of concurrency in order to maximize the use of available resources without overwhelming the system. Oracle’s previous attempts at throttling parallel operations to allow them to be used in multiuser environments have not been entirely successful. Version 11gR2 provided some new capabilities for controlling parallel operations. In particular, a queueing mechanism has been introduced that allows the number of concurrent parallel processes to be managed more effectively. This approach appears to be much better suited to allowing a high degree of parallelism without overwhelming the available resources than previous attempts. 11gR2 also introduced the ability for Oracle to automatically calculate a degree of parallelism on a statement-by-statement basis. Version 12c introduced a Parallel Statement Queueing enhancement and process memory usage control. Also introduced in version 12c is a Database Resource Manager (DBRM) directive which allows the critical queries to bypass the parallel statement queue, and a new database parameter that limits the total Program Global Area (PGA) size, for which the idea is to avoid excessive physical memory usage that leads to high rates of swapping.

The main challenge in database-as-a-service is the security and privacy of data because service providers are not usually considered as trustworthy. So, the data must be encrypted before storing into the database. Another challenge arises that the performance is degraded on the deployment of encryption algorithm on runtime. Furthermore, the connectivity through the Internet adds more delay. To tackle this, we have proposed parallel query execution methodology using multithreading technique up to 6 threads. We have conducted experiments up to 1000,000 (1 million) encrypted records. Our results are quite promising. For data encryption/decryption, we have used advance encryption standard with blocking length of 256 bits. We have designed our methodology in the context of parallel computation method proposed in the literature (Ho et al., in: Proceedings of the 2017 international conference on machine learning and soft computing, pp 47–52, 2017). We compared the results with state-of-art algorithms. The state-of-art algorithms execute the experiments on 10,120 encrypted records maximum which took about time of 1000 ms with 2 threads. But the proposed methodology is proved outstanding that executed the experiments which were performed on 100,000 encrypted records. It outperformed with 6 threads which took only 507 ms even with 2 threads, and the proposed methodology is much better which took only 994 ms. So, the efficiency and scalability of the proposed methodology are proved better as compared to state-of-the-art algorithms.

With the explosive growth of big data, data-intensive analysis services need a large data storage and high efficient parallel query processing techniques in cloud using lower-end machines, because most organizations adopt inexpensive low-end clusters. A key challenge is to optimize query processing in such a cloud environment. In this paper, we present a new hybrid data access architecture, called HyDB, for providing data-intensive analysis services, which is featured with a distributed data storage, parallel data access, and query optimization methods. First, we propose a new data partitioning method based on both workloads and co-located resources. The data partitioning method achieves higher consolidation and outperforms the existing approaches. Second, we provide a new parallel access method which includes parallel query processing, optimal query plan generation, and optimal path selection by using a plan tree pruning technique. We have implemented HyDB. Finally, we conduct extensive experimental studies and confirm the efficiency of our HyDB.

Graph Analytics (GA) is a rapidly growing field that holds significant importance in numerous applications that focuses on analysing the complex relations in a network. One of the crucial concepts in GA is Query Reachability which refers to the ability to find a path connecting two nodes (u, v) in graph G. The multiple attributes in reachability query processing play a significant role to discover some interesting patterns. Existing studies did not deal with processing complex queries in attributed graphs. To fulfill some of the challenges, We contributed to this paper with three ideas for solving complex queries, finding paths, and processing multiple queries in parallel and distributed paradigms. Therefore, 1) Proposed Distributed Set Reachability Query in Attributed Networks (DSRQ_AN) algorithm using distributed computing through the Neo4j graph data platform and Cypher Query Language, 2) Finding the path to the given complex queries, and 3) Proposed a Multiple Query Processing in Parallel and Distributed (MQP_PD) algorithm to execute in a parallel environment. This paper assessed the performance of the proposed approaches through a parallel paradigm on Neo4j slaves. Furthermore, the demonstrated results of parallel execution outperform sequential execution for multiple queries on attributed graphs.

How to build up an efficient simulation tool for complex parallel relational query processing based on High-level Petri nets

On-line analytical processing (OLAP) queries are strongly affected by the amount data needed to be accessed from the disk. Therefore, there is a need to employ techniques that can facilitate efficient execution of these queries. There has been a lot of work to optimize the performance of relational data warehouses. Among the two fragmentation techniques, vertical fragmentation is often considered more complicated than horizontal, it nearly impossible to obtain an optimal solution. Data partitioning concept that has been studied in the context of relational databases aims to reduce query execution time and facilitate the parallel execution of queries. In this paper, we develop a new framework based on genetic algorithm for applying the partitioning technique on relational DW schema (star schema) to minimize the total query execution cost. We develop an analytical cost model for executing a set of OLAP queries on a partitioned star schema. We conduct experiments to evaluate the utility of partitioning in efficiently executing OLAP queries. Finally, we show how partitioning can be used to facilitate parallel execution of OLAP queries.

With the exponential growth of digital information, efficient and accurate passage retrieval remains a crucial challenge in information retrieval systems.This paper presents an optimized Approximate Nearest Neighbor-Based Dense Retriever for Passage Ranking with Query Expansion (ANN-DRP-QE), designed to enhance retrieval effectiveness while significantly reducing latency and memory usage.The model integrates query expansion techniques to refine query representations, improving retrieval performance across diverse information needs.The ANN-DRP-QE model is evaluated using the MS MARCO Passage Ranking Dataset, where its performance is compared against BM25 (a sparse lexical retrieval baseline), ColBERT (a dense multi-vector retrieval model), and a Standard ANN-Based Dense Retriever.After optimization, ANN-DRP-QE achieves the highest ranking performance with MRR@10 of 0.420, Recall@10 of 0.765, and nDCG@10 of 0.445, surpassing ColBERT (0.360, 0.728, 0.412), BM25 (0.187, 0.685, 0.372), and the Standard ANN-Based Retriever (0.342, 0.710, 0.401).Additionally, major efficiency enhancements, including HNSW indexing, Float16-based embeddings, FAISS dot-product search, batch query encoding, MiniLM embeddings, parallel query processing, and query expansion, drastically reduce latency to ~0.02-0.1s(compared to 0.015s for ColBERT, 0.3-0.6sfor Standard ANN, and 1.3s for BM25) while lowering memory consumption to ~0.004MB (matching ColBERT and over 100x smaller than BM25's 55.26MB).Finally, the results establish ANN-DRP-QE as the most efficient and scalable retrieval solution, achieving a superior balance between ranking accuracy, speed, and memory efficiency.The model presents a highly effective alternative for real-time passage ranking in AI-driven search engines and large-scale document retrieval systems.

We designed a language, Parallel LERA, supporting self-scheduling query processing in a multiprocessor database system. This language results from a compilation phase which performs the optimization and parallelization of database queries. The design of the language follows a compositional approach by using fine grain operations combined within Parallel LERA constructs. This approach leads to simplify the run-time system and allows high performance of the compiled code due to optimization possibilities. Control strategies are introduced in the query code using basic control operations which perform dataflow control and query synchronization. The introduction of control operations provides a complete separation of data and control processing. Data dependent computations raise optimization opportunities handled into customized control strategies. The proposed control optimizations lead to a reduction in data and control communication and avoid unnecessary computations.

In this paper we study the use of networks of PCs to handle the parallel execution of relational database queries. This approach is based on a parallel extension, called parallel relational query evaluator, working in a coupled mode with a sequential DBMS. We present a detailed architecture of the parallel query evaluator and introduce Enkidu, the efficient Java-based prototype that has been build according to our concepts. We expose a set of measurements, conducted over Enkidu, and highlighting its performances. We finally discuss the interest and viability of the concept of parallel extension in the context of relational databases and in the wider context of high performance computing.KeywordsNetworks of workstationsParallel DBMSJava

In this paper, a method for fast processing of data stream tuples in parallel execution of continuous queries over a multiprocessing environment is proposed. A copy of the query plan is assigned to each of processing units in the multiprocessing environment. Dynamic and continuous routing of input data stream tuples among the graph constructed by these copies (called the Query Mega Graph) for each input tuple determines that, after getting processed by each processing unit (e.g., processor), to which next processor it should be forwarded. Selection of the proper next processor is performed such that the destination processor imposes the minimum tuple latency to the corresponding tuple, among all of the alternative processors. The tuple latency is derived from processing, buffering and communication time delay which varies in different practical parallel systems. Parallel system architectures that would be suitable as the desired multiprocessing environment for employing the proposed Dynamic Tuple Routing (DTR) method are considered and analyzed. Also, practical challenges and issues for the proper parallel underlying system are discussed. Implementation of the desired parallel system on multi-core systems is provided and used for evaluating the proposed DTR method. Evaluation results show that the proposed DTR method outperforms similar method such as the Eddies in terms of tuple latency, throughput and tuple loss.

The article discusses main development aspects of the load balancer for the cluster. The goal is to increase the speed of queries processing to a database running with PostgreSQL DBMS on the Windows operating system. The developed balancing method was chosen by performance and reliability criteria. The software module distributes evenly the load between 4 cluster nodes depending on how much CPU is used on each of the work nodes. The dynamic load balancer processes SQL queries in parallel. The parallel database management module performs encryption of the queries results. The work speed of the developed balancing software module has been studied on a representative set of queries, the obtained results were compared with the work of load balancing configuration consisting of 2 work nodes, and with parallel and sequential processing of the representative set of queries on a single node.

The barriers to parallel query execution are start-up time of parallel operations, interference and poor load balancing among the processors due to skewed data distribution. In this paper, we have described how these problems are addressed in DBS3, a shared-memory database system implemented on a 72-node KSR1 multiprocessor.Our solution combines the advantages of static and dynamic partitioning. We use static partitioning of relations to reduce interference and dynamic allocation of processors to operations to reduce start-up time and improve load balancing. A major advantage of this solution is to be able to deal efficiently with skew by allowing each thread to dynamically choose which operation's instance it will execute. A performance analysis on our prototype with databases of the Wisconsin benchmark confirm these results. More information on this work can be found in http://rodin.inria.fr/bouganim.