A methodology for auto-recognizing DBMS workloads

ABSTRACT

The type of the workload on a database management system (DBMS) is a key consideration in tuning the system. Allocations for resources such as main memory can be very different depending on whether the workload type is Online Transaction Processing (OLTP) or Decision Support System (DSS). A DBMS also typically experiences changes in the type of workload it handles during its normal processing cycle. Database administrators must, therefore, recognize the significant shifts of workload type that demand reconfiguring the system in order to maintain acceptable levels of performance. We envision autonomous, self-tuning DBMSs that have the capability to manage their own performance by automatically recognizing the workload type and then reconfiguring their resources accordingly. In this paper, we present an approach to automatically identifying a DBMS workload as either OLTP or DSS. We build a classification model based on the most significant workload characteristics that differenti ate OLTP from DSS and then use the model to identify any change in the workload type. We construct and compare classifiers built from two different sets of industry-standard workloads, namely the TPC-C and TPC-H benchmarks, and the Browsing and Ordering profiles from the TPC-W benchmark. We conduct various sets of experiments that show that our workload classifiers are reliable, and have high accuracy in recognizing the type of the workload mix and in estimating the degree of its concentration.

REFERENCES

• 1: Anastassia Ailamaki , David J. DeWitt , Mark D. Hill , David A. Wood, DBMSs on a Modern Processor: Where Does Time Go?, Proceedings of the 25th International Conference on Very Large Data Bases, p.266-277, September 07-10, 1999
• 2: Luiz André Barroso , Kourosh Gharachorloo , Edouard Bugnion, Memory system characterization of commercial workloads, Proceedings of the 25th annual international symposium on Computer architecture, p.3-14, June 27-July 02, 1998, Barcelona, Spain
• 3: Asit Dan , Philip S. Yu , Jen Yao Chung, Characterization of database access pattern for analytic prediction of buffer hit probability, The VLDB Journal — The International Journal on Very Large Data Bases, v.4 n.1, p.127-154, January 1995 [doi>10.1007/BF01232474]
• 4: {4} S. Elnaffar, P. Martin. Characterizing Computer Systems' Workloads. Submitted to ACM Computing Surveys Journal.
• 5: W. W. Hsu , A. Jay Smith , H. C. Young, Characteristics of production database workloads and the TPC benchmarks, IBM Systems Journal, v.40 n.3, p.781-802, March 2001
• 6: {6} DB2 Intelligent Miner for Data, http://www- 4.ibm.com/software/data/iminer/fordata/abou t.html, IBM, 1999.
• 7: {7} DB2 Universal Database Version 7 Administration Guide: Performance, IBM Corporation, 2000.
• 8: {8} C. Nikolaou, A. Labrinidis, V. Bohn, D. Ferguson, M. Artavanis, C. Kloukinas, and M. Marazakis. The Impact of Workload Clustering on Transaction Routing. Technical Report FORTH-ICS TR-238, December 1998.
• 9: {9} Oracle9iDatabase Performance Guide and Reference, Release 1(9.0.1), Part# A87503- 02, Oracle Corp., 2001.
• 10: John C. Shafer , Rakesh Agrawal , Manish Mehta, SPRINT: A Scalable Parallel Classifier for Data Mining, Proceedings of the 22th International Conference on Very Large Data Bases, p.544-555, September 03-06, 1996
• 11: Carsten Sapia, PROMISE: Predicting Query Behavior to Enable Predictive Caching Strategies for OLAP Systems, Proceedings of the Second International Conference on Data Warehousing and Knowledge Discovery, p.224-233, September 04-06, 2000
• 12: {12} TPC Benchmark D Standard Specification Revision 2.1, Transaction Processing Performance Council, 1999.
• 13: {13} TPC Benchmark C Standard Specification Revision 5.0, Transaction Processing Performance Council, February 2001.
• 14: {14} TPC Benchmark H Standard Specification Revision 1.3.0, Transaction Processing Performance Council, 1999.
• 15: {15} TPC Benchmark W (Web Commerce) Standard Specification Revision 1.7, Transaction Processing Performance Council, October 2001.
• 16: P. S. Yu , A. Dan, Performance Analysis of Affinity Clustering on Transaction Processing Coupling Architecture, IEEE Transactions on Knowledge and Data Engineering, v.6 n.5, p.764-786, October 1994 [doi>10.1109/69.317706]
• 17: Sreerama K. Murthy, Automatic Construction of Decision Trees from Data: A Multi-Disciplinary Survey, Data Mining and Knowledge Discovery, v.2 n.4, p.345-389, December 1998 [doi>10.1023/A:1009744630224]
• 18: {18} P. Horn. Autonomic Computing: IBM's Perspective on the State of Information Technology.http://www.research.ibm.com/au tonomic/manifesto/autonomic_computing.pdf , October 15 2001.
• 19: {19} Karen Paulsell. Sybase ® Adaptive ServerTMEnterprise Performance and Tuning Guide. Sybase Inc., Adaptive Server Enterprise Version 12, Document ID: 32614-01-1200- 01-01/32615-01-1200-01, October 1999.
• 20: Allan N. Packer , Sun Microsystems Press, Configuring and Tuning Databases on the Solaris Platform, Prentice Hall PTR, Upper Saddle River, NJ, 2001
• 21: Phil Bernstein , Michael Brodie , Stefano Ceri , David DeWitt , Mike Franklin , Hector Garcia-Molina , Jim Gray , Jerry Held , Joe Hellerstein , H. V. Jagadish , Michael Lesk , Dave Maier , Jeff Naughton , Hamid Pirahesh , Mike Stonebraker , Jeff Ullman, The Asilomar report on database research, ACM SIGMOD Record, v.27 n.4, p.74-80, Dec. 1998 [doi>10.1145/306101.306137]
• 22: Philip S. Yu , Ming-Syan Chen , Hans-Ulrich Heiss , Sukho Lee, On Workload Characterization of Relational Database Environments, IEEE Transactions on Software Engineering, v.18 n.4, p.347-355, April 1992 [doi>10.1109/32.129222]
• 23: Kimberly Keeton , David A. Patterson , Yong Qiang He , Roger C. Raphael , Walter E. Baker, Performance characterization of a Quad Pentium Pro SMP using OLTP workloads, Proceedings of the 25th annual international symposium on Computer architecture, p.15-26, June 27-July 02, 1998, Barcelona, Spain
• 24: W. W. Hsu , A. Jay Smith , H. C. Young, Characteristics of production database workloads and the TPC benchmarks, IBM Systems Journal, v.40 n.3, p.781-802, March 2001
• 25: Luiz André Barroso , Kourosh Gharachorloo , Edouard Bugnion, Memory system characterization of commercial workloads, Proceedings of the 25th annual international symposium on Computer architecture, p.3-14, June 27-July 02, 1998, Barcelona, Spain
• 26: {26} K. Keeton and D. Patterson. Towards A Simplified Database Workload for Computer Architecture Evaluations. Workload Characterization for Computer System Design, L. John and A. Maynard (Editors), Kluwer Academic Publishers, 2000.

INDEX TERMS

Primary Classification:
  C. Computer Systems Organization
  C.4 PERFORMANCE OF SYSTEMS
      Subjects: Modeling techniques

Additional Classification:
  C. Computer Systems Organization
  C.4 PERFORMANCE OF SYSTEMS
      Subjects: Performance attributes

  H. Information Systems
  H.2 DATABASE MANAGEMENT
      H.2.4 Systems
          Subjects: Transaction processing
  H.3 INFORMATION STORAGE AND RETRIEVAL
      H.3.4 Systems and Software
          Subjects: Performance evaluation (efficiency and effectiveness)

General Terms:
Algorithms, Design, Experimentation, Measurement, Performance, Theory