Abstract
When multiple queries are concurrently executed, the database management systems often encounter performance bottlenecks which lead to the excessive execution time of query statements. Analyzing the execution process of these queries is conductive to study on the influence of query statements on database performance. In this paper, we proposed a performance bottleneck analysis and optimization framework named PeBAO to solve the database performance bottleneck problems in a multitasking concurrent execution environment. This framework quantifies the bottleneck impact factors of querying tasks and operations based on cost strategy. Then we designed a bottleneck optimization module based on heuristic rules. Finally, we solve the bottleneck purposefully and improve the performance of database system by combining the bottleneck impact factor with query optimization suggestions. We use the datasets of TPC-H to evaluate the accuracy of bottleneck judgement and bottleneck impact factor quantifying method. Our experimental results show that PeBAO provides effective optimization suggestions for database performance bottlenecks in a multitasking concurrent executive environment.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
References
Myalapalli, V.K., Savarapu, P.R.: High performance SQL. In: 11th IEEE International Conference on Emerging Trends in Innovation and Technology, December 2014, Conference 2016, Pune, India. LNCS, vol. 9999, pp. 1–13. Springer, Heidelberg (2016)
Myalapalli, V.K., Chakravarthy, A.S.N., Reddy, K.P.: Accelerating SQL queries by unravelling performance bottlenecks in DBMS engine. In: 2015 International Conference on Energy Systems and Applications, pp. 7–12 (2015)
Myalapalli, V.K., Shiva, M.B.: An appraisal to optimize SQL queries. In: International Conference on Pervasive Computing (ICPC), pp. 1–6, Pune 2015 (2015)
Myalapalli, V.K., Teja, B.L.R.: High performance PL/SQL programming. In: 2015 International Conference on Pervasive Computing (ICPC), pp. 1–5 (2015)
Myalapalli, V.K., Totakura, T.P., Geloth, S.: Augmenting database performance via SQL tuning. In: 2015 International Conference on Energy Systems and Applications, pp. 13–18 (2015)
Myalapalli, V.K., Padma, T., Totakura, K.: Overhauling PL/SQL applications for optimized performance. Int. J. Innov. Res. Sci. Eng. Technol. 4(9) (2015)
Mishra, S., Beaulieu, A.: Mastering Oracle SQL, 1st edn. O’Reilly, Massachusetts (2002)
Oppel, A.: SQL Demystified, 1st edn. McGraw-Hill, New York (2005)
Zhang, G., Chen, M., Liu, L.: A model for application-oriented database performance tuning. In: 2012 6th International Conference on New Trends in Information Science, Service Science and Data Mining (ISSDM2012), Taipei, pp. 389–394 (2012)
Myalapalli, V.K., Chakravarthy, A.S.N.: Revamping SQL queries for cost based optimization. In: 2016 International Conference on Circuits, Controls, Communications and Computing (I4C), pp. 1–6 (2016)
Weiner, A.M., Härder, T., Oliveira da Silva, R.: Visualizing cost-based XQuery optimization. In: 2010 IEEE 26th International Conference on Data Engineering (ICDE 2010), Long Beach, CA, pp. 1165–1168 (2010)
Zhou, S., Tomov, N., Williams, H.W., Burger, A., Taylor, H.: Cache modelling in a performance evaluator for parallel database systems. In: Proceedings Fifth International Symposium on Modeling, Analysis, and Simulation of Computer and Telecommunication Systems, Haifa, Israel, pp. 46–50 (1997)
Cook, J.E., Wolf, A.L., Zorn, B.G.: A highly effective partition selection policy for object database garbage collection. IEEE Trans. Knowl. Data Eng. 10(1), 153–172 (1998)
Abul-Basher, Z.: Multiple-query optimization of regular path queries. In: 2017 IEEE 33rd International Conference on Data Engineering (ICDE), San Diego, CA, pp. 1426–1430 (2017)
Pt-query-digest. https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html
Mysqlsla. https://github.com/daniel-nichter/hackmysql.com/tree/master/mysqlsla
SQLAdvisor. https://github.com/Meituan-Dianping/SQLAdvisor
SQLautoreview. https://github.com/taobao/sqlautoreview
Acknowledgement
This work was supported by the Fund by the National Natural Science Foundation of China (Grant No. 61462012, No. 61562010, No. U1531246, No. 71964009), the Innovation Team of the Data Analysis and Cloud Service of Guizhou Province (Grant No. [2015]53), the Program for Innovative Talent of Guizhou Province (2017).
Author information
Authors and Affiliations
Corresponding authors
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2020 Springer Nature Switzerland AG
About this paper
Cite this paper
Chen, D. et al. (2020). PeBAO: A Performance Bottleneck Analysis and Optimization Framework in Concurrent Environments. In: Silhavy, R. (eds) Intelligent Algorithms in Software Engineering. CSOC 2020. Advances in Intelligent Systems and Computing, vol 1224. Springer, Cham. https://doi.org/10.1007/978-3-030-51965-0_21
Download citation
DOI: https://doi.org/10.1007/978-3-030-51965-0_21
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-030-51964-3
Online ISBN: 978-3-030-51965-0
eBook Packages: Intelligent Technologies and RoboticsIntelligent Technologies and Robotics (R0)