Analysis of parallel scan processing in Shared Disk database systems [chapter]

Erhard Rahm, Thomas Stöhr
1995 Lecture Notes in Computer Science  
Shared Disk database systems offer a high flexibility for parallel transaction and query processing. This is because each node can process any transaction, query or subquery because it has access to the entire database. Compared to Shared Nothing database systems, this is particularly advantageous for scan queries for which the degree of intra-query parallelism as well as the scan processors themselves can dynamically be chosen. On the other hand, there is the danger of disk contention between
more » ... ubqueries, in particular for index scans. We present a detailed simulation study to analyze the effectiveness of parallel scan processing in Shared Disk database systems. In particular, we investigate the relationship between the degree of declustering and the degree of scan parallelism for relation scans, clustered index scans, and non-clustered index scans. Furthermore, we study the usefulness of disk caches and prefetching for limiting disk contention. Finally, we show that disk contention in multi-user mode can be limited for Shared Disk database systems by dynamically choosing the degree of scan parallelism. SE refers to the use of shared-memory multiprocessors (symmetric multiprocessing) for database processing. Since it is limited to relatively few processors, SN and SD are generally considered the most important approaches for parallel database systems [Pi90, DG92]. Both architectures consist of multiple loosely coupled processing nodes (distributed memory) connected by a high-speed network. The software architecture is homogeneous in that each node runs an identical copy of the DBMS software. Through cooperation between these DBMS instances, complete distribution transparency (single system image) is achieved for database users and application programs. SN is based on a physical partitioning of the database among nodes, while SD allows each DBMS instance to access all disks and thus the entire database. The latter approach therefore requires a global concurrency control protocol (introducing communication overhead and delays) to achieve serializability. Furthermore, buffer coherency must be maintained since database pages may be replicated in multiple DBMS buffers [Ra86, Yu87, MN91]. On the other hand, SN requires communication for distributed query processing, commit processing and global deadlock detection. The differences between SN and SD with respect to the database allocation have farreaching consequences for parallel query processing [Ra93b]. This is particularly the case for scan operations that operate on base relations 1 . Scan is the simplest and most common relational operator. It produces a row-and-column subset of a relation by applying a selection predicate and filtering away attributes not requested by the query. If predicate evaluation cannot be supported by an index, a complete relation scan is necessary where each tuple of the relation must be read and processed. An index scan accesses tuples via an index and restricts processing to a subset of the tuples; in the extreme case, no tuple or only one tuple needs to be accessed (e.g., exact-match query on unique attribute). In SN systems, a scan operation on relation R typically has to be processed by all nodes to which a partition of R has been assigned 2 . Hence, the degree of scan parallelism and thus the associated communication overhead are already determined by the largely static database allocation. Furthermore, there is no choice of which nodes should process a scan operation. As a result, SN does not support dynamic load balancing for scan and thus for most operations. SD, on the other hand, permits us to dynamically choose the degree of scan parallelism as well as the scan processors since each processor can access the entire relation R. Of course, R must be declustered across multiple disks to support I/O parallelism. In contrast to SN however, SD offers the flexibility to choose a degree of processing parallelism different from the degree of I/O parallelism. This flexibility of the SD architecture is already significant for parallel query processing in single-user mode. This is because different scan operations on R have their response time minimum for different degrees of parallelism. For instance, a selective index scan accessing only one tuple is best processed on a single processor, while a relation scan accessing all tuples may require 100 processors to provide sufficiently short response times. SN requires to statically choose the degree of declustering and thus the degree of scan parallelism for an average load profile [Gh90]. If both scan queries of our example are processed with equal probability, the relation would thus have to be partitioned among 50 nodes resulting in sub-optimal performance for both query types (enormous communication overhead for the index scan relative to the actual work; sub-optimal degree of parallelism for the relation scan). SD, on the other hand, allows both query types to be processed by the optimal number of nodes (1 for the index scan, 100 for the relation scan), provided the relation is declustered across 100 disks. 1. Operations on derived data, e.g. join, can be parallelized similarly in both architectures by dynamically redistributing the operations' input data among processors. 2. Selections on the partitioning attribute, used to define the relation's partitioning, may be restricted to a subset of the data processors.
doi:10.1007/bfb0020487 fatcat:njrgyqxdfzf37jmdyarp26wcdy