Load balancing and data placement for multi-tiered database systems

A materialized view or Materialized Query Table (MQT) is an auxiliary table with precomputed data that can be used to significantly improve the performance of a database query. A Materialized Query Table Advisor (MQTA) is often used to recommend and create MQTs. The state-of-the-art MQTA works in a standalone database server where MQTs are placed on the same server as that in which the base tables are located. The MQTA does not apply to a federated or scaleout scenario in which MQTs need to be placed on other servers close to applications (i.e. a frontend database server) for off-loading the workload on the backend database server. In this paper, we propose a Data Placement Advisor (DPA) and load balancing strategies for multi-tiered database systems. Built on top of the MQTA, DPA recommends MQTs and advises placement strategies for minimizing the response time for a query workload. To demonstrate the benefit of the data placement advising, we implemented a prototype of DPA that works with the MQTA in the IBM (R) DB2 (R) Universal Database(TM) (DB2 UDB) and the IBM WebSphere (R) Information Integrator (WebSphere II). The evaluation results showed substantial improvements of workload response times when MQTs are intelligently recommended and placed on a frontend database server subject to space and load characteristics for TPC-H and OLAP type workloads. (C) 2006 Elsevier B.V. All rights reserved.