Learning From User-Specified Optimizer Hints in Database Systems

• Autorzy: Zakrzewicz Maciej

Identyfikatory

DOI

10.2478/fcds-2024-0011

• Języki publikacji: EN

Abstrakty

EN

Recently, numerous machine learning (ML) techniques have been applied to address database performance management problems, including cardinality estimation, cost modeling, optimal join order prediction, hint generation, etc. In this paper, we focus on query optimizer hints employed by users in their queries in order to mask some Query Optimizer deficiencies. We treat the query optimizer hints, bound to previous queries, as significant additional query metadata and learn to automatically predict which new queries will pose similar performance challenges and should therefore also be supported by query optimizer hints. To validate our approach, we have performed a number of experiments using real-life SQL workloads and we achieved promising results.

Słowa kluczowe

EN

database systems; query optimization

• Wydawca: Wydawnictwo Politechniki Poznańskiej
• Czasopismo: Foundations of Computing and Decision Sciences
• Rocznik: 2024
• Tom: Vol. 49, No. 2
• Strony: 181--197
• Opis fizyczny: Bibliogr. 31 poz., rys., tab.

Twórcy

autor

Zakrzewicz Maciej

• Institute of Computing Science Poznan University of Technology, Poznan, Poland

Bibliografia

• [1] Anagnostopoulos C., Triantafillou P., Learning Set Cardinality in Distance Nearest Neighbours. In Proceedings of the 2015 IEEE International Conference on Data Mining (ICDM), ICDM’15, pages 691–696, USA, Nov. 2015. IEEE Computer Society.
• [2] Anagnostopoulos C., Triantafillou P., Learning to accurately COUNT with query-driven predictive analytics. In 2015 IEEE International Conference on Big Data (Big Data), Big Data ’15, pages 14-23, Oct. 2015.
• [3] Anagnostopoulos C., Triantafillou P., Query-Driven Learning for Predictive Analytics of Data Subspace Cardinality. ACM Trans. Knowl. Discov. Data, 11(4): 47:1-47:46, June 2017.
• [4] Ding B., Das S., Marcus R., Wu W., Chaudhuri S., Narasayya V. R., AI Meets AI: Leveraging Query Executions to Improve Index Recommendations. In 38th ACM Special Interest Group in Data Management, SIGMOD’19, 2019.
• [5] Duggan J., Papaemmanouil O., Cetintemel U., Upfal E., Contender: A Resource Modeling Approach for Concurrent Query Performance Prediction. In Proceedings of the 14th International Conference on Extending Database Technology, EDBT’14, pages 109-120, 2014.
• [6] Gao H., Zhu J., Liu L., Xu J., Wu Y., Liu A., Detecting SQL injection attacks using grammar pattern recognition and access behavior mining. In: 2019 IEEE International Conference on Energy Internet (ICEI). IEEE, 2019. p. 493-498.
• [7] Hall M., Eibe F., Holmes G., Pfahringer B., Reutemann P., Witten I., The WEKA Data Mining Software: An Update. SIGKDD Explorations, 11.1, 2009, 10-18.
• [8] Hayek R., Shmueli O., Improved Cardinality Estimation by Learning Queries Containment Rates. arXiv:1908.07723 [cs], Aug. 2019.
• [9] Ho T. K., Random decision forests. In Proceedings of 3rd international conference on document analysis and recognition, 1995. pp. 278-282.
• [10] Kipf A., Kipf T., Radke B., Leis V., Boncz P., Kemper A., Learned Cardinalities: Estimating Correlated Joins with Deep Learning. In 9th Biennial Conference on Innovative Data Systems Research, CIDR’19, 2019.
• [11] Kraska T., Beutel A., Chi E. H., Dean J., Polyzotis N., The Case for Learned Index Structures. In Proceedings of the 2018 International Conference on Management of Data, SIGMOD ’18, New York, NY, USA, 2018. ACM.
• [12] Krishnan S., Yang Z., Goldberg K., Hellerstein J., Stoica I., Learning to Optimize Join Queries With Deep Reinforcement Learning. arXiv:1808.03196, Aug. 2018.
• [13] Li Q., Li W., Wang J., Cheng M., A SQL injection detection method based on adaptive deep forest. IEEE Access, 2019, 7: 145385-145394.
• [14] Li Y., Bin Z., Detection of SQL Injection Attacks Based on Improved TFIDF Algorithm. Journal of Physics: Conference Series. 1395. 012013. 10.1088/1742-6596/1395/1/012013, 2019
• [15] Liu H., Xu M., Yu Z., Corvinelli V., Zuzarte C., Cardinality Estimation Using Neural Networks. In Proceedings of the 25th Annual International Conference on Computer Science and Software Engineering, CASCON ’15, pages 53–59, Riverton, NJ, USA, 2015. IBM Corp.
• [16] Liu R., Zhang W., A detection methodology for SQL injection attacks based on the TFIDF-CHI algorithm. Proc. SPIE 12941, International Conference on Algorithms, High Performance Computing, and Artificial Intelligence (AHPCAI 2023), 129410N https://doi.org/10.1117/12.3011777
• [17] Marcus R., Papaemmanouil O., Deep Reinforcement Learning for Join Order Enumeration. In First International Workshop on Exploiting Artificial Intelligence Techniques for Data Management, aiDM @ SIGMOD’18, Houston, TX, 2018.
• [18] MS SQL Server Hints, https://learn.microsoft.com/en-us/sql/t-sql/queries/hintstransact-sql
• [19] MySQL Optimizer Hints, https://dev.mysql.com/doc/refman/8.0/en/optimizerhints.html
• [20] Oracle Database Influencing the Optimizer, https://docs.oracle.com/en/database-/oracle/oracle-database/21/tgsql/influencing-the-optimizer.html
• [21] Oudah M. A., Marhusin M. F., Narzullaev A., SQL Injection Detection Using Machine Learning with Different TF-IDF Feature Extraction Approaches. In: Al-Emran, M., Al-Sharafi, M. A., Shaalan, K. (eds) International Conference on Information Systems and Intelligent Applications. ICISIA 2022. Lecture Notes in Networks and Systems, vol 550. Springer, Cham. https://doi.org/10.1007/978-3-031-16865-9_57
• [22] PostgreSQL Query Planning, https://www.postgresql.org/docs/current/runtime-configquery.html
• [23] Read J., Pfahringer B., Holmes G., Frank E., Classifier Chains for Multi-label Classification. Machine Learning Journal. Springer. Vol. 85(3), (2011).
• [24] Stillger M., Lohman G. M., Markl V., Kandil M., LEO - DB2’s Learning Optimizer. In VLDB, VLDB ’01, pages 19-28, 2001.
• [25] Sun J., Li G., An end-to-end learning-based cost estimator. Proceedings of the VLDB Endowment, 13(3): 307-319, Nov. 2019.
• [26] Trummer I., Moseley S., Maram D., Jo S., Antonakakis J., SkinnerDB: Regretbounded Query Evaluation via Reinforcement Learning. PVLDB, 11(12): 2074-2077, 2018.
• [27] Tzoumas K., Sellis T., Jensen C., A Reinforcement Learning Approach for Adaptive Query Processing. A DB Technical Report, June 2008.
• [28] Woltmann L., Hartmann C., Thiele M., Habich D., Lehner W., Cardinality estimation with local deep learning models. In Proceedings of the Second International Workshop on Exploiting Artificial Intelligence Techniques for Data Management, aiDM’19, pages 1-8, Amsterdam, Netherlands, July 2019. Association for Computing Machinery.
• [29] Yang Z., Kamsetty A., Luan S., Liang E., Duan Y., Chen X., Stoica I., NeuroCard: One Cardinality Estimator for All Tables. arXiv:2006.08109, June 2020.
• [30] Yang Z., Liang E., Kamsetty A., Wu C., Duan Y., Chen X., Abbeel P., Hellerstein J. M., Krishnan S., Stoica I., Deep unsupervised cardinality estimation. Proceedings of the VLDB Endowment, 13(3):279–292, Nov. 2019.
• [31] Zhang K., A machine learning based approach to identify SQL injection vulnerabilities. In: 34th IEEE/ACM International Conference on Automated Software Engineering (ASE). IEEE, 2019. p. 1286-1288.