Multi-query SQL Progress Indicators

  • Gang Luo
  • Jeffrey F. Naughton
  • Philip S. Yu
Part of the Lecture Notes in Computer Science book series (LNCS, volume 3896)

Abstract

Recently, progress indicators have been proposed for SQL queries in RDBMSs. All previously proposed progress indicators consider each query in isolation, ignoring the impact simultaneously running queries have on each other’s performance. In this paper, we explore a multi-query progress indicator, which explicitly considers concurrently running queries and even queries predicted to arrive in the future when producing its estimates. We demonstrate that multi-query progress indicators can provide more accurate estimates than single-query progress indicators. Moreover, we extend the use of progress indicators beyond being a GUI tool and show how to apply multi-query progress indicators to workload management. We report on an initial implementation of a multi-query progress indicator in PostgreSQL and experiments with its use both for estimating remaining query execution time and for workload management.

Preview

Unable to display preview. Download preview PDF.

Unable to display preview. Download preview PDF.

References

  1. 1.
    Abbott, R.K., Garcia-Molina, H.: Scheduling Real-time Transactions: a Performance Evaluation. VLDB, 1–12 (1988)Google Scholar
  2. 2.
    Blazewicz, J., Ecker, K.H., Pesch, E., et al.: Scheduling Computer and Manufacturing Processes, Second Edition. Springer, Heidelberg (2001)Google Scholar
  3. 3.
    Carey, M.J., Krishnamurthi, S., Livny, M.: Load Control for Locking: The ’Half-and- Half’ Approach. In: PODS, pp. 72–84 (1990)Google Scholar
  4. 4.
    Chaudhuri, S., Kaushik, R., Ramamurthy, R.: When Can We Trust Progress Estimators for SQL Queries? In: SIGMOD Conf. (2005)Google Scholar
  5. 5.
    Cormen, T.H., Leiserson, C.E., Rivest, R.L., et al.: Introduction to Algorithms, Second Edition. MIT Press, Cambridge (2001)Google Scholar
  6. 6.
    Chaudhuri, S., Narasayya, V.R., Ramamurthy, R.: Estimating Progress of Long Running SQL Queries. In: SIGMOD Conf., pp. 803–814 (2004)Google Scholar
  7. 7.
    Faloutsos, C., Ng, R.T., Sellis, T.K.: Predictive Load Control for Flexible Buffer Allocation. In: VLDB 1991, pp. 265–274 (1991)Google Scholar
  8. 8.
    IBM Autonomic Computing homepage, http://www.research.ibm.com/autonomic
  9. 9.
    Khanna, S., Sebree, M., Zolnowsky, J.: Realtime Scheduling in SunOS 5.0. In: USENIX Winter 1992, pp. 375–390 (1992)Google Scholar
  10. 10.
    Lohman, G.M., Lightstone, S.: SMART: Making DB2 (More) Autonomic. In: VLDB 2002, pp. 877–879 (2002)Google Scholar
  11. 11.
    Luo, G., Naughton, J.F., Ellmann, C.J., et al.: Toward a Progress Indicator for Database Queries. In: SIGMOD Conf. 2004, pp. 791–802 (2004)Google Scholar
  12. 12.
    Luo, G., Naughton, J.F., Ellmann, C.J., et al.: Increasing the Accuracy and Coverage of SQL Progress Indicators. In: ICDE, pp. 853–864 (2005)Google Scholar
  13. 13.
    Microsoft AutoAdmin Project homepage, http://research.microsoft.com/dmx/autoadmin
  14. 14.
    McWherter, D.T., Schroeder, B., Ailamaki, A., et al.: Priority Mechanisms for OLTP and Transactional Web Applications. In: ICDE, pp. 535–546 (2004)Google Scholar
  15. 15.
  16. 16.
    Pinedo, M.: Scheduling: Theory, Algorithms, and Systems, Second Edition. Prentice Hall, Englewood Cliffs (2001)Google Scholar
  17. 17.
    PostgreSQL homepage (2005), http://www.postgresql.org
  18. 18.
    Ramamritham, K.: Real-Time Databases. Distributed and Parallel Databases 1(2), 199–226 (1993)CrossRefGoogle Scholar
  19. 19.
    Shasha, D., Bonnet, P.: Database Tuning: Principles, Experiments, and Troubleshooting Techniques. Morgan Kaufmann Publishers, San Francisco (2002)Google Scholar
  20. 20.
    Silberschatz, A., Galvin, P., Gagne, G.: Operating System Concepts, Sixth Edition. John Wiley, Chichester (2002)Google Scholar
  21. 21.
    Homepage, T.P.C.: TPC-R benchmark, http://www.tpc.org
  22. 22.
    Michael, W.: Watzke. Personal communication (2005)Google Scholar
  23. 23.
    Weikum, G., Hasse, C., Moenkeberg, A., et al.: The COMFORT Automatic Tuning Project. Inf. Syst. 19(5), 381–432 (1994)CrossRefGoogle Scholar
  24. 24.
    Zhao, W.: Special Issue on Real-Time Computing Systems. Operating Systems Review 23(3) (1989)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2006

Authors and Affiliations

  • Gang Luo
    • 1
  • Jeffrey F. Naughton
    • 2
  • Philip S. Yu
    • 1
  1. 1.IBM T.J. Watson Research Center 
  2. 2.University of Wisconsin-Madison 

Personalised recommendations