Douglas W. Cornell

On coupling multi-systems through data sharing

The demand for larger transaction rates and the inability of single-system-based transaction processors to keep up with demand have resulted in the growth of multi-processor-based database systems. The focus here is on coupling in a locally distributed system through multi-system data sharing in which all systems have direct access to the data. This paper addresses the following questions; i) How does a workload running on a single system today perform if migrated to a multi-system? ii) What are the multi-system locking design issues that limit multi-system performance and what is the maximum number of systems that may be effectively coupled? iii) Can alternative locking designs increase the number of systems that may be effectively coupled? Our analysis is based on traces from large mainframe systems running IBMs IMS database management system. We have developed a hierarchical modeling methodology that starts by synthesizing a multi-system IMS lock trace and a reference trace from single-system traces. The multisystem traces are used in trace-driven simulations to predict lock contention and database I/O increase in multi-system environment and to generate workload parameters. These parameters are used in event-driven simulation models to examine the overall performance under different system structures. Performance results are presented for realistic system parameters to determine the performance impact of various design parameters. Lock contention is found to be the critical factor in determining the coupling effectiveness and the effect of alternative locking design to reduce lock contention is studied. The limit on coupling is explored and the analysis indicates that, for this workload, on the order of 6 to 12 systems may be effectively coupled through data sharing, depending on system structure and locking design.

An effective approach to vertical partitioning for physical design of relational databases

Vertical partitioning can be used to enhance the performance of relational database systems by reducing the number of disk accesses. The authors identify the key parameters for capturing the behavior of an access plan and propose a two-step methodology consisting of a query analysis step to estimate the parameters and a binary partitioning step which can be applied recursively. The partitioning uses an integer linear programming technique to minimize the number of disk accesses. Significant performance benefit would be achieved for join if the partitioned (inner) relation could fit into the memory buffer under the inner-outer loop join method, or if the partitioned relation could fit into the sort buffer under the sort-merge join method, but not the original relation. For cases where a segment scan or a cluster index scan is used, vertical partitioning of the relation with the algorithm described is still often found to lead to substantial performance improvement. >

On multisystem coupling through function request shipping

In a multisystem database system with a function request shipping approach, the databases are partitioned among the multiple systems and a facility is provided to support the shipping of database requests among the systems. This is in contrast to a data sharing multisystem approach in which all systems have direct access to the shared database. The performance of the two approaches is compared, emphasizing generic issues that affect the function shipping approach. A methodology is presented for partitioning the databases and routing transactions among the systems so as to minimize the fraction of remote function calls, while balancing the load among systems. Estimates of the resulting remote function calls, mirror transaction setups, and multisystem two-phase commits are obtained. Results of simulation and approximate analysis are presented.

On optimal site assignment for relations in the distributed database environment

In a distributed database environment, the site assignment of relations is a critical issue. When the joint operations in a query involve relations over multiple sites, the site to carry out the joint operation can have a significant impact on the performance. Based on the query descriptions and arrival frequency to each site, a methodology is developed to assign relations and determine joint sites simultaneously. The methodology first decomposes queries into relation steps and then makes site assignments based on either a linear integer programming technique to minimize the amount of intersystem communication while balancing resource utilizations across systems, or a heuristic technique to minimize average response time under similar resource constraints. >

Buffer management based on return on consumption in a multi-query environment

In a multi-query environment, the marginal utilities of allocating additional buffer to the various queries can be vastly different. The conventional approach examines each query in isolation to determine the optimal access plan and the corresponding locality set. This can lead to performance that is far from optimal. As each query can have different access plans with dissimilar locality sets and sensitivities to memory requirement, we employ the concepts of memory consumption and return on consumption (ROC) as the basis for memory allocations. Memory consumption of a query is its space-time product, while ROC is a measure of the effectiveness of response-time reduction through additional memory consumption. A global optimization strategy using simulated annealing is developed, which minimizes the average response over all queries under the constraint that the total memory consumption rate has to be less than the buffer size. It selects the optimal join method and memory allocation for all query types simultaneously. By analyzing the way the optimal strategy makes memory allocations, a heuristic threshold strategy is then proposed. The threshold strategy is based on the concept of ROC. As the memory consumption rate by all queries is limited by the buffer size, the strategy tries to allocate the memory so as to make sure that a certain level of ROC is achieved. A simulation model is developed to demonstrate that the heuristic strategy yields performance that is very close to the optimal strategy and is far superior to the conventional allocation strategy.

Analysis of affinity based routing in multi-system data sharing

Abstract The rapid growth in the transaction rate requirement of high volume database systems has forced users and vendors to (a) couple multiple database systems to run against a common database, and (b) to implement each single system with faster processors. Multiple system coupling incurs performance degradation due to inter-system interference: inter-system (global) lock contention and database buffer invalidation. At high transaction rates, the level of inter-system interference can have a severe impact on performance. In this paper, we exploit transaction routing as a means of reducing inter-system interference and quantify its effect. A methodology, employing an integer linear programming technique, is developed to classify incoming transactions into affinity groups based on their database call reference pattern. The key idea of affinity based routing is to determine affinity groups and route transactions in the same affinity group to the same system. Based on traces from two of IBMs high volume single system customers, we find that, at high transaction rates, affinity based routing significantly reduces lock contention probability and leads to a substantial reduction in transaction response time. Improvement in hierarchical locking by taking advantage of affinity based routing is demonstrated. Further, the reduction in inter-system data contention produces a large impact on the performance of an optimistic type concurrency control strategy.

Modelling of centralized concurrency control in a multi-system environment

The performance of multiple systems sharing a common data base is analyzed for an architecture with concurrency control using a centralized lock engine. The workload is based on traces from large mainframe systems running IBMs IMS database management system. Based on IMS lock traces the lock contention probability and data base buffer invalidation effect in a multi-system environment is predicted. Workload parameters are generated for use in event-driven simulation models that examine the overall performance of multi-system data sharing, and to determine the performance impact of various system parameters and design alternatives. While performance results are presented for realistic system parameters, the emphasis is on the methodology, approximate analysis technique and on examining the factors that affect multi-system performance.

Site assignment for relations and joint operations in the distributed transaction processing environment

An integrated strategy for choosing the sites where relations are to be stored while simultaneously determining where joint operations are to take place is considered. Based on the transaction characteristic and arrival frequency to each site, a methodology to formulate the strategy is developed to assign relations to systems and determine joint sites so as to minimize the amount of intersystem communication while simultaneously balancing resource utilization among systems. The methodology first decomposes queries into relation steps and then makes site assignments based on linear integer-programming techniques.<<ETX>>

Performance comparison of IO shipping and database call shipping: schemes in multisystem partitioned databases

Abstract In a multi-system partioned database, the database is split between the multiple systems and a facility is provided to support the shipping of function requests among the systems. Two alternate levels of function requests, called IO request shipping and database call shipping, are analyzed and compared. The analysis is based on traces from large mainframe systems running IBMs IMS database management systems. A methodology is presented for deriving optimal database partitions and transaction assignment so as to minimize the average system response time. The methodology decomposes the optimization problem into an integer linear programming problem for partitioning the databases and a simplex reflection method for probabilistic transaction routing. The issues addressed include the effectiveness of routing and the limits of partitioning. The IO request shipping approach is preferable when high communications bandwidth is available as in a machine room. Furthermore, IO request shipping can take advantage of probabilistic routing to enhance its performance, while database call shipping gains little from probabilistic routing as compared to fixed routing. However, IO request shipping is more susceptible to lock contention which may become a problem for an environment with a high transaction rate. For systems with lower communications bandwidth spread beyond the machine room, the database call shipping approach is the method of choice.

Optimal buffer allocation in a multi-query environment

The concepts of memory consumption and return on consumption (ROC) are used as the basis of memory allocations. A global optimization strategy using simulated annealing is developed which minimizes the average response time over all queries under the constraint that the total memory consumption rate has to be less than the buffer size. It selects the optimal join method and memory allocation for all queries simultaneously. By analyzing the way that the optimal strategy makes memory allocations, a heuristic threshold strategy is proposed. The threshold strategy is based on the concept of ROC. As the memory consumption rate by all queries is limited by the buffer size, the strategy tries to allocate the memory so as to make sure that a certain level of ROC is achieved. A simulation model is developed to demonstrate that the heuristic strategy yields performance that is very close to the optimal strategy and is far superior to the conventional allocation strategy.<<ETX>>