Optimizing I/O-Intensive Transactions in Highly Interactive Applications Mohamed A. Sharaf ECE Department University of Toronto Toronto, Ontario, Canada msharaf@eecg.toronto.edu Panos K. Chrysanthis CS Department University of Pittsburgh Pittsburgh, PA, U.S.A. panos@cs.pitt.edu Alexandros Labrinidis CS Department University of Pittsburgh Pittsburgh, PA, U.S.A. labrinid@cs.pitt.edu Cristiana Amza ECE Department University of Toronto Toronto, Ontario, Canada amza@eecg.toronto.edu ABSTRACT The performance provided by an interactive online database system is typically measured in terms of meeting certain pre-specified Ser- vice Level Agreements (SLAs), with expected transaction latency being the most commonly used type of SLA. This form of SLA acts as a soft deadline for each transaction, and user satisfaction can be measured in terms of minimizing tardiness, that is, the deviation from SLA. This objective is further complicated for I/O-intensive transactions, where the storage system becomes the performance bottleneck. Moreover, common I/O scheduling policies employed by the Operating System with a goal of improving I/O throughput or average latency may run counter to optimizing per-transaction performance since the Operating System is typically oblivious to the application high-level SLA specifications. In this paper, we propose a new SLA-aware policy for scheduling I/O requests of database transactions. Our proposed policy synergistically com- bines novel deadline-aware scheduling policies for database trans- actions with features of Operating System scheduling policies de- signed for improving I/O throughput. This enables our proposed policy to dynamically adapt to workload and consistently provide the best performance. Categories and Subject Descriptors H.2.4 [Database Management]: Systems—Transaction process- ing. General Terms Algorithms, Design, Performance. Keywords Database Systems, Transaction Processing, I/O Scheduling. Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. To copy otherwise, to republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. SIGMOD’09, June 29–July 2, 2009, Providence, Rhode Island, USA. Copyright 2009 ACM 978-1-60558-551-2/09/06 ...$5.00. 1. INTRODUCTION In today’s highly interactive database applications, optimizing I/O intensive transactions is a challenge, as important now as it was at the emergence of database management systems. The reason for this is that today’s highly interactive applications, such as Web applications driven by a database or a data warehouse, must meet the users’ high performance expectations in light of the fact that these applications involve large number of data accesses. For ex- ample, in database-driven Web applications, a user during a single session accesses (interactively or at the “speed of thought”) web pages which are dynamically created from data in databases. Typ- ically, such a dynamic web page is composed by a number of con- tent fragments, each of which is materialized at every request by running several I/O intensive queries and executing lengthy code to produce HTML. As reported in [17], I/O access can constitute up to 90% of the transaction execution time. Similarly, in data warehousing applications, users interactively analyze massive amounts of data by means of OLAP (on-line ana- lytical processing) tools. Such tools typically query the underlying data warehouse, retrieve and process large tables of data, and finally provide users with summarized aggregate information. In such highly interactive applications, user satisfaction or posi- tive experience determines the applications’ success (and keeps the competitors “more than a click away” [25]). Service Level Agree- ments (SLAs) are used to quantify the user’s satisfaction. Trans- action latency expressed as a deadline is the most commonly used form of SLA, reflecting the user’s expectation for the transaction to finish within a certain amount of specified time. In order to maximize users’ satisfaction, the underlying data man- agement system should strive to maximally meet the pre-specified SLAs. However, this is a non-trivial task, especially given the bursty and unpredictable behavior of Web access which often leads to conditions of heavy load and high utilization. This goal is further complicated in I/O-bound transactions, where slow disk access be- comes the performance bottleneck and traditional caching cannot fully mitigate the problem. Previous work that attempts to meet the users’ pre-specified SLAs focuses on scheduling multiple transaction access to CPU (e.g., [25, 24, 26, 10]) as well as CPU scheduling of sub-transactions to meet a global transaction deadline (e.g., [14, 15, 16, 27]). In these poli- cies, as well as in most transaction scheduling policies, the sched- uling of I/O requests is handled by the underlying operating sys-