Märtens, Holger

Rahm, Erhard

Stöhr, Thomas

English

Qucosa - Publikationsserver der Universität Leipzig

On Flexible Allocation of Index and Temporary Data inParallel Database SystemsErhard RahmHolger Märtens, Thomas StöhrUniversity of Leipzig, Germany1  IntroductionData placement is a key factor for high performance database systems. This is particularly truefor parallel database systems where data allocation must support both I/O parallelism and pro-cessing parallelism within complex queries and between independent queries and transactions.Determining an effective data placement is a complex administration problem depending onmany parameters including system architecture, database and workload characteristics, hard-ware configuration, etc. Research and tool support has so far concentrated on data placementfor base tables, especially for Shared Nothing (SN), e.g. [MD97]. On the other hand, to ourknowledge, data placement issues for architectures where multiple DBMS instances share ac-cess to the same disks (Shared Disk, Shared Everything, specific hybrid architectures) have notyet been investigated in a systematic way. Furthermore, little work has been published on effec-tive disk allocation of index structures and temporary data (e.g., intermediate query results).However, these allocation problems gain increasing importance, e.g. in order to effectively uti-lize parallel database systems for decision support / data warehousing environments.In the next section we discuss the index allocation problem in more detail and introduce a clas-sification of various approaches that are already supported to some degree in commercialDBMS. While SN offers only few options, the other architectures provide a higher flexibilitybecause index allocation can be independent from the base table allocation. For certain index-supported queries, this can allow for order-of-magnitude savings in I/O and communicationcost. We then turn to the disk allocation of intermediate query results for which the allocationparameters can be chosen dynamically at query run time. For the case of parallel hash joins, weoutline how to determine an optimal approach supporting a high degree of parallelism. Thework discussed is performed within a project aiming at developing strategies to automaticallydetermine optimal data allocation strategies in order to simplify system administration in highperformance environments.2  Index AllocationDetermining an index allocation comprises tasks similar to determining the data placement oftables, namely specifying the distribution granules (fragments), calculating the degree of de-clustering, and allocating the fragments to disks or (in SN systems) to processing nodes. Thelatter subproblem can be solved by standard techniques, e.g. to achieve a balanced distributionof access frequencies.SN systems typically use a horizontal partitioning of tables based on a hash or range partitioningon an attribute TPA (table partitioning attribute). Index allocation follows the table allocationin order to allow each processing node to locally access its data. As a result, index support on aparticular attribute IA results in a local subindex per node on attribute IA. For an index queryon IA (different from TPA), each node would perform a local index scan on its subindex. Forselective queries this is clearly much more efficient than a complete table scan. Still, the com-munication overhead to start and terminate p subqueries and the I/O cost of p local index scans1can be substantial, e.g. for exact match queries on the primary key (example: account table withbranchID as TPA and acctNo as IA).For Shared Disk (SD) - as well as in other architectures where multiple DBMS instances candirectly access the same data - there are more options for index allocation. This is because thereis no need to partition an index structure among processing nodes and because index partition-ing may be different from table partitioning. The main index allocation alternatives are classi-fied along two dimensions in Fig. 1. At the first level, we consider logical index partitioningaffecting processing (query) parallelism and the size of the index search space. At level two, weconsider physical aspects such as the degree of declustering affecting I/O parallelism.Approach 1 is to have no logical index partitioning but to simply use a single index tree1 (1 root)as in centralized DBMS. Such an approach is also referred to as a global index. Global indicesallow minimal storage and access cost because there is only 1 index tree. Depending on indexsize and access frequency, a global index may be completely stored (clustered) on a single diskor to the minimal number of disks if the index size exceeds the disk capacity, or it may physis-cally be declustered across multiple disks, e.g., at the page level. Physical clustering is the sim-plest approach that avoids the need to specify a degree of declustering, size of the declusteringgranules etc. Furthermore, an index access is confined to a single disk so that occupying multi-ple disk arms is avoided which may restrict I/O rates. On the other hand, it supports the leastdegree of parallelism so that it is primarily of interest for small indices or highly selective que-ries. Even in these cases, mutli-user operation may lead to disk bottlenecks unless most of theindex data can be cached in main memory. Physical declustering can overcome these restric-tions by supporting improved I/O parallelism and load balancing in multi-user mode (inter-que-ry parallelism) [RS95, SWZ98]. It may be transparent to the DBMS, e.g., when supported by adisk array controller.Global indices allow optimal processing of selective queries. For instance, an exact match queryon a primary index can be processed with a single index traversal. SN systems, on the otherhand, may require p subqueries and p local index traversals for such queries causing order-of-magnitude higher processing overhead (communication, I/O). Intra-query parallelism for largerindex queries is more difficult to achieve2 and one of the reasons for logically partitioned indi-ces.1. Due to space restrictions we concentrate on B*-tree index structures.2. One possibility is to logically partition only the leaf level of a global index, similar to [KFK96].Fig. 1. Classification of index partitioning strategieslogicalphysicalLogically central-ized (global) in-dex (1 root)Index logically partitioned (p roots) ontable partition-ing attribute TPA(2a)index attributeIA (2b)Index partitioning for Parallel DBSphysicallyclusteredindexphysicallydeclusteredindex aspectsaspects(2)(1)other attribute(2c)2Logical index partitioning (approach 2) divides an index structure into multiple subindices (proots) with respect to an attribute IPA (index partitioning attribute). This approach can limit thesearch space for index queries referring to the IPA by only processing the relevant set of sub-indices. Furthermore, all selected subindices can be processed in parallel thus supporting intra-query parallelism without introducing (disk) contention between subqueries. Logical index par-titioning can be combined with physical partitioning by declustering a single logical partitionacross multiple disks to improve I/O parallelism.The IPA may be different from the index attribute IA. In particular, the IPA may correspond tothe TPA which is typically the case for SN. That is SN index allocation is usually restricted tocase 2a with the additional limitation that the fragmentation and processor allocation (degree ofdeclustering) coincides for tables and indices irrespective of index size and access frequencies.As already pointed out, this often leads to a maximal processing parallelism and high overheadwhich is particularly harmful for smaller index queries.SD-like architectures may also use a logical index partitioning on the table partitioning attribute,if the table is logically partitioned. In this case, table and index scans on the TPA can be restrict-ed to a subset of the table data / subindices in order to reduce the amount of work to be per-formed. Furthermore, range queries on the TPA covering multiple partitions can be processedin parallel without disk contention between subqueries. In contrast to SN, the number of indexpartitions can be different from the number of table partitions and the index data may be as-signed to different disks than the tables. Furthermore, the degree of intra-query parallelism canbe chosen smaller than the number of relevant subindices (to limit the communication over-head) and can be determined at query run time (e.g., depending on the query size and the currentload situation).Moreover, in SD-like architectures logical index partitioning may be based on the index at-tribute IA itself (case 2b) or other attributes than TPA or IA or attribute combinations (2c). Thisagain allows us to limit the index work to the really relevant subindices as well as to supportintra-query parallelism.All in all, we have a large spectrum of possible index allocation schemes with both logical par-titioning and physical declustering possibilities. To better understand the various trade-offs, weplan a comprehensive performance study for various workloads and configurations, includingdata warehousing scenarios with parallel star joins in multi-user mode. Current SD implemen-tations like Oracle Parallel Server [Or97] already support logically partitioned indices, howeverwithout giving sufficient help on how to use them optimally3.3  Disk Allocation of Temporary DataOne important aspect in a self-tuning PDBS is the efficient storage and retrieval of temporarydata, such as large intermediate query results. Frequently, such temporary data is exchanged be-tween operators running on different processing nodes. When its size exceeds the memory ca-pacity of the participating nodes, the temporary data or parts of it must be stored on disk.Declustering the temporary data across multiple disks is required in order to support I/O paral-lelism [Wu95]. This type of data allocation can be determined at query run time.In SD-like architectures, intermediate results can be written out by the sender nodes and directlyread in by the receivers. Such a disk-based data transfer is convenient and reduces the overhead3. In [Or97, Or99] it is just remarked that an index should be partitioned by TPA for decision support queries andby index attribute for OLTP queries, respectively. It remains unclear how to determine the degree of decluster-ing for tables and indices, where to allocate index partitions, how to incorporate physical declustering, how todeal with mixed workloads, etc.3of communication between processing nodes that can be substantial for SN4. But with each re-ceiver getting multiple fragments from every sender - as in many join, sort, and aggregation op-erations - a smart disk allocation is required to limit disk contention while supporting a highdegree of I/O parallelism. For the latter we propose to even decluster individual fragmentsacross multiple disks.We illustrate our approach [Mä99] for parallel hash join processing in a SD environment withn scan nodes and m join nodes (Fig. 2). The scan output is written to d disks where should beselected such that the disks can write as fast as the n scan nodes produce their data. We assumethe join nodes to perform local hash joins and thus we partition the join input into b buckets. Agiven bucket contains input from all scan nodes but is processed by exactly one join node. Buck-et partitioning, especially selection of b, is performed so that the hash table of any bucket canbe kept entirely in the main memory currently available on the join nodes. A major aspect thenbecomes how to perform the disk allocation of buckets, in particular how to determine the de-gree of declustering v per bucket, in order to support I/O parallelism and to control disk conten-tion.With these parameters, it can be shown that the minimal number of processors concurrently ac-cessing a disk is achieved by a matrix-like arrangement of the d disks into columns androws as illustrated in Fig. 2. If buckets are allocated to each row, each bucket can bedeclustered across the disks of that row. Now, scan nodes can be assigned to each col-umn and write their output to the bucket partitions stored there. Similarly, join nodescan then read and process the buckets in a row, each reading in parallel from  disks.Based on this general allocation scheme, we must now select the degree of bucket declustering,, in such a way as to minimize the overall disk access times. This involves a trade-off betweenparallel I/O and disk contention as can be seen from the following cases:4. Smaller intermediate results not requiring overflow I/O should always be transferred directly by inter-proces-sor communication. This is much faster than a disk-based data transfer and supports pipeline parallelism.dv d v⁄b v d⁄⋅v n v⁄m v d⁄⋅vscan nodesjoin nodesbucketsbucket partitionsdisksFig. 2. Example of the processing model and the allocation scheme. Eighty buckets(not all shown) are processed using six scan nodes and four join nodes (n=6, m=4,b=80). The buckets are declustered across twelve disks with a degree of three (d=12,v=3). To minimize access conflicts, each disk is used by just two scan nodes and onejoin node.σ σ σ σ σ σv4• A straight-forward approach is to distribute the buckets across all d disks, but to keep indi-vidual buckets on a single device (v = 1). However, this limits read parallelism because eachjoin node has to sequentially access each bucket from a single disk. In addition, there is ahigh degree of write contention because each scan node contributes to each bucket resultingin concurrent access of each disk by any of the n scan nodes.• v = d results in a maximal declustering of any bucket across all disks. This supports a highdegree of I/O parallelism but suffers from serious read contention. This is because each joinnode has to access all disks for each bucket.• An intermediate case with good read performance is obtained for . This supportsread parallelism without any read contention while at the same time the full bandwidth ofall  disks is used for join processing.In order to quantify the performance of the various allocation alternatives, we have developeda detailed analytical model for the disk access times as a function of [Mä99]. It turned out thatin most practical cases, the setting of is the best approach because it provides optimalread performance with acceptable write performance. The example in Fig. 2 is based on thiscase.Currently, we are in the process of validating our analytical model by simulation. Furthermore,we are adapting the allocation approach to other types of operations, such as merge joins, non-equi joins, sorting, and various types of aggregation.4  ConclusionsEffective data allocation for index and temporary data in parallel database systems is an impor-tant area that has not yet sufficiently been investigated in the research community. To ease sys-tem administration, commercial DBMS have to provide sophisticated tool support for indexallocation. This is particularly the case for SD-like architectures in order to exploit their highoptimization potential as illustrated by our classification scheme. In addition, flexible data allo-cation for large intermediate results is to be supported at query run time. Our approach for de-clustering temporary data may be a good starting point for this.ReferencesKFK96 Koudas, N., Faloutsos, C., Kamel, I.: Declustering Spatial Databases on a Multi-Computer Architecture. Proc. EDBT96, LNCS 1057, 592-614, 1996MD97 Mehta, M.; DeWitt, D.: Data Placement in Shared Nothing Parallel Database Sys-tems. VLDB Journal 6 (1), 1997Mä99 Märtens, H.: On Disk Allocation of Intermediate Query Results in Parallel Data-base Systems. Proc. Europar99 Conf., LNCS, Springer 1999Or97 Taking Advantage of Partitioning in Oracle8. Oracle Technical White Paper,www.oracle.com, 1997Or99 Miscellaneous Oracle8 server manuals.www.irm.vt.edu/oracle_803_docs (June 25, 1999)RS95 Rahm, E., Stöhr, T.: Analysis of Parallel Scan Processing in Shared Disk DatabaseSystems. Proc. Europar95, LNCS 966, 485-500, 1995SWZ98 Scheuermann, P., Weikum, G., Zabback, P.: Data Partitioning and Load Balancingin Parallel Disk Systems. VLDB Journal 7(1), 48-66, 1998Wu95 Wu, K. et al.: A Performance Study of Workfile Disk Management for ConcurrentMergesorts in a Multiprocessor Database Systems. Proc. VLDB95 conf., 1995v d m⁄=dvv d m⁄=5

On Flexible Allocation of Index and Temporary Data in Parallel Database Systems

https://ul.qucosa.de/api/qucosa%3A31979/attachment/ATT-0/

On Flexible Allocation of Index and Temporary Data in Parallel Database Systems

Abstract

Similar works

Full text

Available Versions

Qucosa - Publikationsserver der Universität Leipzig