research-article

AppSleuth: a tool for database tuning at the application level

Authors:

Dennis ShashaAuthors Info & Claims

EDBT '13: Proceedings of the 16th International Conference on Extending Database Technology

March 2013

Pages 589 - 600

https://doi.org/10.1145/2452376.2452445

Published: 18 March 2013 Publication History

Abstract

Excellent work ([1]-[6]) has shown that memory management and transaction concurrency levels can often be tuned automatically by the database management systems. Other excellent work ([7]]-[14]) has shown how to use the optimizer to do automatic physical design or to make the optimizer itself more self-adaptive ([15]-[17]). Our performance tuning experience across various industries (finance, gaming, data warehouses, and travel) has shown that enormous additional tuning benefits (sometimes amounting to orders of magnitude) can come from reengineering application code and table design. The question is: can a tool help in this effort? We believe so. We present a tool called AppSleuth that parses application code and the tracing log for two popular database management systems in order to lead a competent tuner to the hot spots in an application. This paper discusses (i) representative application "delinquent design patterns", (ii) an application code parser to find them, (iii) a log parser to identify the patterns that are critical, and (iv) a display to give a global view of the issue. We present an extended sanitized case study from a real travel application to show the results of the tool at different stages of a tuning engagement, yielding a 300 fold improvement. This is the first tool of its kind that we know of.

References

[1]

Storm, A. J., Garcia-Arellano, C., Lightstone, S., Diao, Y., and Surendra, M. Adaptive self-tuning memory in DB2. In Proceedings of the 32^nd International Conference on Very Large Data Bases (VLDB'06) (Seoul Korea, September 12--15, 2006). VLDB Endowment, pp 1081--1092.

Digital Library

[2]

Baryshnikov, B., Clinciu, C., Cunningham, C., Giakoumakis, L., Oks, S., and Stefani, S. Managing query compilation memory consumption to improve DBMS throughput. In Proceedings of he 3^rd Biennial Conference on Innovative Database Systems Research (CIDR'07) (Asilomar, CA, January 7--10, 2007). www.crdrdb.org, 2007, pp 275--280.

[3]

Dageville, B., and Zait, M. SQL memory management in Oracle 9i. In Proceedings of the 28^nd International Conference on Very Large Data Bases (VLDB'02) (Hong Kong China, August 20--23, 2002). VLDB Endowment, pp 962--973.

Digital Library

[4]

Microsoft Corporation. SQL Server 2005 books online: Dynamic memory management. SQL Server product documentation. (September 2007), DOI = http://msdn.microsoft.com/en-us/library/ms178145(SQL.90).aspx.

[5]

Larson, P., Graefe, G., Memory management during run generation in external sorting. In Proceedings of the 1998 ACM SIGMOD International Conference on Management of Data (SIGMOD'98) (Seattle, Washington, June 2--4, 1998). ACM Press, New York, NY, 1998, pp 472--483.

Digital Library

[6]

Weikum, G., Hasse, C., MoenKeberg, A., and Zabback, P. The COMFORT automatic tuning project. Invited Project Review. Inf. Syst., 19, 5 (Jan. 1994), pp 381--432.

Digital Library

[7]

Zilio, D., Rao, J., Lightstone, S., Lohman, G., Storm, A. J., Garcia-Arellano, C., and Fadden, S. DB2 Design Advisor: integrated automatic physical database design. In Proceedings of the 30^th International Conference on Very Large Data Bases (VLDB '04) (Toronto, Canada, August 31 -- September 3, 2004). Morgan Kaufmann, San Francisco, CA, 2004, pp 1110--1121.

Digital Library

[8]

Oracle Corporation. Performance tuning using the SQL Access Advisor. Oracle White Paper. (2007), DOI = http://otn.oracle.com.

[9]

Agrawal, S., Chaudhuri, S., Koll{\'a}r, L., Mathare, A. P., Narasayya, V. R., and Syamala, M. Database Tuning Advisor for Microsoft SQL Server 2005. In Proceedings of the 30^th International Conference on Very Large Data Bases (VLDB '04) (Toronto, Canada, August 31 -- September 3, 2004). Morgan Kaufmann, San Francisco, CA, 2004, pp 1110--1121.

[10]

Bruno, N., and Chaudhuri, S. Automatic physical database tuning: a relaxation-based approach. In Proceedings of the 2005 ACM SIGMOD International Conference on Management of Data (SIGMOD'05) (Baltimore, Maryland, June 13--16, 2005). ACM Press, New York, NY, 2005, pp 227--238.

Digital Library

[11]

Agrawal, S., Chaudhuri, S., Narasayya, V. R. Automated selection of materialized views and indexes in SQL databases. In Proceedings of the 26^nd International Conference on Very Large Data Bases (VLDB'00) (Cairo, Egypt, September 10--14, 2000). Morgan Kaufmann, San Francisco, CA, 2000, pp 496--505.

Digital Library

[12]

Kornacker, M., Shah, M., and Hellerstein, J. M., Amdb: a design tool for access methods. IEEE Data Engineering Bulletin, 26, 2 (Jun. 2003), pp 3--11.

[13]

Aboulnaga, A., Gebaly, K. EI., Robustness in automatic physical design. In Proceedings of the 11^th International Conference on Extending Database Technology (EDBT'08) (Nantes, France, March 25--29, 2008). ACM Press, New York, NY, 2008, pp 145--156.

Digital Library

[14]

Papadomanolakis, S., Dash, D., Ailamaki, A., Efficient use of the query optimizer for automated physical design. In Proceedings of the 33^th International Conference on Very Large Data Bases (VLDB '07) (University of Vienna, Austria, September 23--27, 2007). ACM Press, New York, NY, 2008, pp 1093--1104.

Digital Library

[15]

Babu, S., Bizarro, P., DeWitt, D., Proactive re-optimization. In Proceedings of the 2005 ACM SIGMOD International Conference on Management of Data (SIGMOD'05) (Baltimore, Maryland, June 13--16, 2005). ACM Press, New York, NY, pp 107--118.

Digital Library

[16]

Stillger, M., Lohman, G. M., Markl, V., Kandil, M., LEO: DB2's LEarning Optimizer. In Proceedings of the 27^th International Conference on Very Large Data Bases (VLDB '01) (Roma, Italy, September 11--14, 2001) Morgan Kaufmann, San Francisco, CA, 2001, pp 19--28.

Digital Library

[17]

Raman, V., Markl, V., Simmen, D., Lohman, G., and Pirahesh, H., Progressive optimization in action. In Proceedings of the 30^th International Conference on Very Large Data Bases (VLDB '04) (Toronto, Canada, August 31 -- September 3, 2004). Morgan Kaufmann, San Francisco, CA, 2004, pp 1337--1340.

Digital Library

[18]

Oracle Database Advanced Application Developer's Guide on Hierarchical Profiler. DOI = http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_profiler.htm#g3157198.

[19]

Dageville, B., Das, D., Dias, K., Yagoub, K., Zait, M., Ziauddin, M. Automatic SQL tuning in Oracle 10g. In Proceedings of the 30^th International Conference on Very Large Data Bases (VLDB '04) (Toronto, Canada, August 31 -- September 3, 2004). Morgan Kaufmann, San Francisco, CA, 2004, pp 1110--1121.

Digital Library

[20]

Oracle Corporation. The self-managing database: automatic performance diagnosis. Oracle White Paper, (2007), DOI = http://otn.oracle.com.

[21]

Dias, K., Ramacher, M., Shaft, U., Ventakaramani, V., and Wood, G., Automatic performance diagnosis and tuning in Oracle. In Proceedings of he 2^nd Biennial Conference on Innovative Database Systems Research (CIDR'05) (Asilomar, CA, January 4--7, 2005). www.crdrdb.org, 2005, pp 84--94.

[22]

Garcia-Arellano, C. M., Lightstone, S., Lohman, G., Markl, V., Storm, A., Autonomic features of the IBM DB2 Universal Database for Linux, UNIX, and Windows. IEEE Transactions on Systems, Man, and Cybernetics special issue on Engineering Autonomic Systems, 36, 3 (May 2006), pp 365--376.

Digital Library

[23]

Microsoft Corporation. SQL Server 2005 books online: Automating administrative tasks. SQL Server product documentation. (September 2007), DOI = http://msdn.microsoft.com/en-us/library/ms187061(SQL.90).aspx.

[24]

Quest Software. Toad: SQL Tuning, Database Development & Administration Software. (2012), DOI = http://www.quest.com/toad/, 2012.

[25]

Shasha, D., and Bonnet, P. Database Tuning: principles, experiments and troubleshooting techniques. Morgan Kaufmann, San Francisco, CA, 2002.

Digital Library

[26]

Microsoft Tansact-SQL reference. DOI = http://msdn.microsoft.com/en-us/library/ms178642.aspx

[27]

The SAMATE website. (2012), DOI =://samate.nist.gov/SATE.html

[28]

Arjun Dasgupta, Vivek Narasayya, Manoj Syamala, A Static Analysis Framework for Database Applications, ICDE '09 Proceedings of the 2009 IEEE International Conference on Data Engineering, pp 1403--1414

Digital Library

[29]

Surajit Chaudhuri, Vivek Narasayya, and Manoj Syamala, Bridging the Application and DBMS Profiling Divide for Database Application Developers, VLDB '07 Proceedings of the 33rd international conference on Very large data bases, pp 1252--1262

Digital Library

[30]

Surajit Chaudhuri, Vivek Narasayya, Manoj Syamala, Bridging the application and DBMS divide using static analysis and dynamic profiling, SIGMOD '09 Proceedings of the 2009 ACM SIGMOD International Conference on Management of data, pp 1039--1042

Digital Library

[31]

The Klocwork website. (2012), DOI = http://www.klocwork.com/

[32]

The Fortify website. (2012), DOI = https://www.fortify.com/

[33]

The Coverity website. (2012), DOI = http://www.coverity.com/

[34]

The Enterprise-architect homepage on Sparx Systems Website. (2012), DOI = http://www.sparxsystems.com.au/enterprise-architect.

[35]

The FindBugs homepage on Sourceforge. (2012), DOI = http://findbugs.sourceforge.net/

[36]

The PMD homepage on SourceForge. (2012), DOI =:http://pmd.sourceforge.net/pmd-5.0.0/

[37]

Cheung, A., Arden, O, Madden, S., Myers, A., Automatic Partitioning of Database Applications. In Proceedings of the 38^th International Conference on Very Large Data Bases (VLDB'12) (Istanbul, Turkey, August 27th -- 31st, 2012). Morgan Kaufmann, San Francisco, CA, 2012, pp 1471--1482

Digital Library

[38]

The GNU Bison Project (2012), DOI = http://www.gnu.org/software/bison

[39]

Paxson, V. Flex. DOI = http://flex.sourceforge.net/

Cited By

Lyu YVolokh SHalfond WTripp OCadar CZhang X(2021)SAND: a static analysis approach for detecting SQL antipatternsProceedings of the 30th ACM SIGSOFT International Symposium on Software Testing and Analysis10.1145/3460319.3464818(270-282)Online publication date: 11-Jul-2021
https://dl.acm.org/doi/10.1145/3460319.3464818
Dombrovskaya HCzaplewski JNovikov B(2020)Connecting Galaxies: Bridging the Gap Between Databases and ApplicationsSOFSEM 2020: Theory and Practice of Computer Science10.1007/978-3-030-38919-2_54(648-656)Online publication date: 17-Jan-2020
https://doi.org/10.1007/978-3-030-38919-2_54
BAO XXIAO NLU YCHEN Z(2016)A Configuration Management Study to Fast Massive Writing for Distributed NoSQL SystemIEICE Transactions on Information and Systems10.1587/transinf.2016EDP7104E99.D:9(2269-2282)Online publication date: 2016
https://doi.org/10.1587/transinf.2016EDP7104
Show More Cited By

Index Terms

AppSleuth: a tool for database tuning at the application level

Recommendations

Automatic Database Management System Tuning Through Large-scale Machine Learning
SIGMOD '17: Proceedings of the 2017 ACM International Conference on Management of Data

Database management system (DBMS) configuration tuning is an essential aspect of any data-intensive application effort. But this is historically a difficult task because DBMSs have hundreds of configuration "knobs" that control everything in the system, ...
Read More
An End-to-End Automatic Cloud Database Tuning System Using Deep Reinforcement Learning
SIGMOD '19: Proceedings of the 2019 International Conference on Management of Data

Configuration tuning is vital to optimize the performance of database management system (DBMS). It becomes more tedious and urgent for cloud databases (CDB) due to the diverse database instances and query workloads, which make the database administrator ...
Read More
Tuning in action
EDBT '13: Proceedings of the 16th International Conference on Extending Database Technology

Imagine that your database has all the right indexes. Its buffer manager has been tuned to give a high hit ratio, the buffer fits in RAM, and the data is well distributed on disk. You're done, right? Well, no, because the application code might be ...
Read More

Comments

Information & Contributors

Information

Published In

cover image ACM Other conferences

EDBT '13: Proceedings of the 16th International Conference on Extending Database Technology

March 2013

793 pages

ISBN:9781450315975

DOI:10.1145/2452376

General Chair:
Giovanna Guerrini
Università di Genova, Italy
,
Program Chair:
Norman W. Paton
University of Manchester, UK

Copyright © 2013 ACM.

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. Copyrights for components of this work owned by others than ACM must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected]

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 18 March 2013

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

EDBT/ICDT '13

EDBT/ICDT '13: Joint 2013 EDBT/ICDT Conferences

March 18 - 22, 2013

Genoa, Italy

Acceptance Rates

Overall Acceptance Rate 7 of 10 submissions, 70%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

4
Total Citations
View Citations
202
Total Downloads

Downloads (Last 12 months)5
Downloads (Last 6 weeks)1

Other Metrics

View Author Metrics

Citations

Cited By

Lyu YVolokh SHalfond WTripp OCadar CZhang X(2021)SAND: a static analysis approach for detecting SQL antipatternsProceedings of the 30th ACM SIGSOFT International Symposium on Software Testing and Analysis10.1145/3460319.3464818(270-282)Online publication date: 11-Jul-2021
https://dl.acm.org/doi/10.1145/3460319.3464818
Dombrovskaya HCzaplewski JNovikov B(2020)Connecting Galaxies: Bridging the Gap Between Databases and ApplicationsSOFSEM 2020: Theory and Practice of Computer Science10.1007/978-3-030-38919-2_54(648-656)Online publication date: 17-Jan-2020
https://doi.org/10.1007/978-3-030-38919-2_54
BAO XXIAO NLU YCHEN Z(2016)A Configuration Management Study to Fast Massive Writing for Distributed NoSQL SystemIEICE Transactions on Information and Systems10.1587/transinf.2016EDP7104E99.D:9(2269-2282)Online publication date: 2016
https://doi.org/10.1587/transinf.2016EDP7104
Kiruthika JKhaddaj S(2014)Performance Issues and Query Optimization in Big Multidimensional DataProceedings of the 2014 13th International Symposium on Distributed Computing and Applications to Business, Engineering and Science10.1109/DCABES.2014.8(24-28)Online publication date: 24-Nov-2014
https://dl.acm.org/doi/10.1109/DCABES.2014.8

View Options

Get Access

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Media

Figures

Other

Tables

View Table of Contents