Learning From User-Specified Optimizer Hints in Database Systems

Zakrzewicz, Maciej

doi:10.2478/fcds-2024-0011

Abstract

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.

References

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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
Hayek R., Shmueli O., Improved Cardinality Estimation by Learning Queries Containment Rates. arXiv:1908.07723 [cs], Aug. 2019.
Search in Google Scholar Back to article
Ho T.K., Random decision forests. In Proceedings of 3rd international conference on document analysis and recognition, 1995. pp. 278–282.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
Krishnan S., Yang Z., Goldberg K., Hellerstein J., Stoica I., Learning to Optimize Join Queries With Deep Reinforcement Learning. arXiv:1808.03196, Aug. 2018.
Search in Google Scholar Back to article
Li Q., Li W., Wang J., Cheng M., A SQL injection detection method based on adaptive deep forest. IEEE Access, 2019, 7: 145385-145394.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
MS SQL Server Hints, https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql
Search in Google Scholar Back to article
MySQL Optimizer Hints, https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
Search in Google Scholar Back to article
Oracle Database Influencing the Optimizer, https://docs.oracle.com/en/database-/oracle/oracle-database/21/tgsql/influencing-the-optimizer.html
Search in Google Scholar Back to article
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
Search in Google Scholar Back to article
PostgreSQL Query Planning, https://www.postgresql.org/docs/current/runtime-config-query.html
Search in Google Scholar Back to article
Read J., Pfahringer B., Holmes G., Frank E., Classifier Chains for Multi-label Classification. Machine Learning Journal. Springer. Vol. 85(3), (2011)
Search in Google Scholar Back to article
Stillger M., Lohman G. M., Markl V., Kandil M., LEO - DB2’s Learning Optimizer. In VLDB, VLDB ’01, pages 19–28, 2001.
Search in Google Scholar Back to article
Sun J., Li G., An end-to-end learning-based cost estimator. Proceedings of the VLDB Endowment, 13(3):307–319, Nov. 2019.
Search in Google Scholar Back to article
Trummer I., Moseley S., Maram D., Jo S., Antonakakis J.. SkinnerDB: Regretbounded Query Evaluation via Reinforcement Learning. PVLDB, 11(12):2074–2077, 2018.
Search in Google Scholar Back to article
Tzoumas K., Sellis T., Jensen C., A Reinforcement Learning Approach for Adaptive Query Processing. A DB Technical Report, June 2008.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article
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.
Search in Google Scholar Back to article

Learning From User-Specified Optimizer Hints in Database Systems

Abstract

Paradigm

My account