Skip to main content
SpringerLink
Log in

Is it DSS or OLTP: automatically identifying DBMS workloads

  • Published:
Journal of Intelligent Information Systems Aims and scope Submit manuscript

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 intelligent, autonomic 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. Using data mining techniques, we build a classification model based on the most significant workload characteristics that differentiate 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 workloads, namely the TPC-C and TPC-H benchmarks and the Browsing and Ordering profiles from the TPC-W benchmark. We demonstrate the feasibility and success of these classifiers with TPC-generated workloads and with industry-supplied workloads.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Similar content being viewed by others

References

  • Agrawal, S., Chaudhuri, S., & Narasayya, V. (2001). Automated selection of materialized views and indexes for SQL databases. In Proceedings of 27th VLDB conference (pp. 20–31), Rome, Italy.

  • Ailamaki, A., DeWitt, D., Hill, M., & Wood, D. (1999). DBMSs on a modern processor: Where does time go? In Proceedings of international conference on very large data bases (pp. 266–277).

  • Barroso, L., Gharachorloo, K., & Bugnion, E. (1998). Memory system characterization of commercial workloads. In Proceedings of the 25th international symposium on computer architecture (pp. 3–14).

  • Brown Associates, D. (2000). DB2 UDB vs. Oracle8i: Total cost of ownership. Port Chester, NY: D.H. Brown Associates, Inc. Retrieved from http://www.breakthroughdb2.com/.

  • Chaudhuri, S., & Narasayya, V. (2000). Automating statistics management for query optimizers. In Proceedings of 16th international conference on data engineering. San Diego, USA.

  • Dan, A., Yu, P., & Chung, J. (1995). Characterization of database access pattern for analytic prediction of buffer hit probability. Very Large Data Bases (VLDB) Journal, 4(1), 127–154.

    Article  Google Scholar 

  • Elnaffar, S. (2004). Towards workload-aware DBMSs: Identifying workload type and predicting its change. PhD thesis, Queens University at Kingston, Canada (November).

  • Elnaffar, S., & Martin, P. (2002). Characterizing computer systems’ workloads. Technical Report 2002-461, Queen’s University.

  • Elnaffar, S., & Martin, P. (2006). The psychic-skeptic prediction framework for effective monitoring of DBMS workloads (submitted).

  • Fox, A., & Patterson, D. (2003). Self-repairing computers. In Scientific American. Retrieved from http://www.sciam.com.

  • Ganek, A., & Corbi, T. (2003). The dawning of the autonomic computing era. IBM Systems Journal, 42(1), 5–18.

    Google Scholar 

  • Hsu, W., Smith, A., & Young, H. (2001). Characteristics of production database workloads and the TPC benchmarks. IBM Systems Journal, 40(3), 781–802.

    Article  Google Scholar 

  • IBM Corp. (1999). DB2 Intelligent Miner for Data. Retrieved from http://www-4.ibm.com/software/data/iminer/fordata/about.html.

  • IBM Corp. (2000). DB2 Universal Database Version 7 Administration Guide: Performance.

  • IBM Corp. (2003). DB2 Universal Database Version 8.1 Administration Guide: Performance.

  • Lightstone, S., Schiefer, B., Zillio, D., & Kleewein, J. (2003). Autonomic computing for relational databases: The ten-year vision. In Proceedings of workshop on autonomic computing principles and architectures. Banff, Alberta.

  • Lohman, G., Valentin, G., Zilio, D., Zuliani, M., & Skelly, A. (2000). DB2 advisor: An optimizer smart enough to recommend its own indexes. In Proceedings of 16th IEEE conference on data engineering. San Diego, CA.

  • Microsoft Corp. (2002). Microsoft SQL Server 2000 Documentation.

  • Murthy, S. (1998). Automatic construction of decision trees from data: A multi-disciplinary survey. Data Mining and Knowledge Discovery, 2, 345–389.

    Article  MathSciNet  Google Scholar 

  • Nikolaou, C., Labrinidis, A., Bohn, V., Ferguson, D., Artavanis, M., Kloukinas, C., et al. (1998). The impact of workload clustering on transaction routing. Technical Report FORTH-ICS TR-238.

  • Oracle Corp. (2001a). Oracle 9i Manageability Features. An Oracle White Paper. Retrieved from http://www.oracle.com/ip/deploy/database/oracle9i/collateral/ma_bwp10.pdf.

  • Oracle Corp. (2001b). Oracle9iDatabase Performance Guide and Reference, Release 1(9.0.1), Part# A87503-02.

  • Oracle Corp. (2001c). Oracle 9i Materialized Views. An Oracle White Paper. Retrieved from http://technet.oracle.com/products/oracle9i/pdf/o9i_mv.pdf.

  • Oracle Corp. (2002). Query Optimization in Oracle 9i. An Oracle White Paper. Retrieved from http://technet.oracle.com/products/bi/pdf/o9i_optimization_twp.pdf.

  • Sapia, C. (2000). PROMISE: Predicting query behavior to enable predictive caching strategies for OLAP systems. In Proceedings of the second international conference on data warehousing and knowledge discovery (pp. 224–233).

  • Shafer, J., Agrawal, R., & Mehta, M. (1996). SPRINT: A scalable parallel classifier for data mining. In Proceedings of the 22th international conference on very large databases. Mumbai (Bombay), India.

  • Stillger, M., Lohman, G., Markl, V., & Kandil, M. (2001). LEO—DB2’s LEarning optimizer. In Proceedings of 27th VLDB conference (pp. 19–28). Rome, Italy.

  • TPC (1999a). Benchmark D Standard Specification Revision 2.1, Transaction Processing Performance Council.

  • TPC (1999b). Benchmark H Standard Specification Revision 1.3.0, Transaction Processing Performance Council.

  • TPC (2001a). Benchmark C Standard Specification Revision 5.0, Transaction Processing Performance Council.

  • TPC (2001b). Benchmark W (Web Commerce) Standard Specification Revision 1.7, Transaction Processing Performance Council.

  • Yu, P., & Dan, A. (1994). Performance analysis of affinity clustering on transaction processing coupling architecture. IEEE Transactions on Knowledge and Data Engineering, 6(5), 764–786.

    Article  Google Scholar 

Download references

Author information

Authors and Affiliations

  1. College of IT, UAE University, Al-Ain, 17555, UAE

    Said Elnaffar

  2. School of Computing, Queen’s University, Kingston, ON, K7L 3N6, Canada

    Pat Martin

  3. IBM Canada, IBM Software Lab, Markham, ON, L6G 1C7, Canada

    Berni Schiefer & Sam Lightstone

Authors
  1. Said Elnaffar
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Pat Martin
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Berni Schiefer
    View author publications

    You can also search for this author in PubMed Google Scholar

  4. Sam Lightstone
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Said Elnaffar.

Rights and permissions

Reprints and permissions

About this article

Cite this article

Elnaffar, S., Martin, P., Schiefer, B. et al. Is it DSS or OLTP: automatically identifying DBMS workloads. J Intell Inf Syst 30, 249–271 (2008). https://doi.org/10.1007/s10844-006-0036-6

Download citation

  • Received:

  • Revised:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s10844-006-0036-6

Keywords

Search

Navigation