Load balancing and data placement for multi-tiered database systems

• Published in: Data & knowledge engineering 2007-09, Vol.62 (3), p.523-546
• Main Authors: Li, Wen-Syan, Zilio, Daniel C., Batra, Vishal S., Zuzarte, Calisto, Narang, Inderpal
• Format: Article
• Language: English
• Subjects: Caching; Data placement; Materialized views; OLAP
• ISSN: 0169-023X; 1872-6933
• DOI: 10.1016/j.datak.2006.11.002

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 offloading 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 ® DB2 ® Universal Database™ (DB2 UDB) and the IBM WebSphere ® 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.