Log and Wait Event Analysis as a Structured Methodology for Performance Diagnosis in Managed Relational Databases
PDF (Spanish)

Keywords

Performance
Wait Events
Databases
Diagnosis
Optimization
Indexes
Concurrency
Observability

Categories

How to Cite

Log and Wait Event Analysis as a Structured Methodology for Performance Diagnosis in Managed Relational Databases. (2026). Journal SCIENCEVOLUTION, 5(4). https://doi.org/10.61325/rnzrw588

Abstract

Performance issues in relational databases are often diagnosed reactively through the isolated analysis of metrics, making it difficult to identify their underlying causes. The objective of this study was to measure the effectiveness of a structured methodology based on logs and wait events. A quantitative approach with an applied quasi-experimental design was employed to compare two conditions on the same PostgreSQL 16 instance: a baseline condition without supporting indexes and an optimized condition following a diagnosis-driven intervention. A concurrent workload was generated using `pgbench` on a synthetic dataset comprising 32,050,000 records. Performance Insights, EXPLAIN ANALYZE, and `pg_stat_statements` were used to analyze wait events, execution plans, and query statistics. During the three complete minutes of the baseline test, the average active load was 14.867 sessions, compared with 2 available vCPUs. Following the intervention, throughput increased from 0.43 to 203.48 transactions per second, while mean latency decreased from 18,409 to 39 milliseconds. The findings provided favorable evidence of the methodology's effectiveness under the evaluated conditions, although its applicability to other environments requires further validation.

PDF (Spanish)

References

Abadi, D., Ailamaki, A., Andersen, D. G., Bailis, P., Balazinska, M., Bernstein, P. A., Boncz, P. A., Chaudhuri, S., Cheung, A., Doan, A., Dong, L., Franklin, M. J., Freire, J., Halevy, A. Y., Hellerstein, J. M., Idreos, S., Kossmann, D., Kraska, T., Krishnamurthy, S., ... Suciu, D. (2022). The Seattle report on database research. Communications of the ACM, 65(8), 72–79. https://doi.org/10.1145/3524284

Abbasi, M., Bernardo, M. V., Váz, P., Silva, J., & Martins, P. (2024). Revisiting database indexing for parallel and accelerated computing: A comprehensive study and novel approaches. Information, 15(8), Article 429. https://doi.org/10.3390/info15080429

Anchlia, A. (2024). Enhancing query performance through relational database indexing. International Journal of Computer Trends and Technology, 72(8), 130–133. https://doi.org/10.14445/22312803/IJCTT-V72I8P119

Barbosa, D., Gruenwald, L., D’Orazio, L., & Bernardino, J. (2024). QRLIT: Quantum reinforcement learning for database index tuning. Future Internet, 16(12), Article 439. https://doi.org/10.3390/fi16120439

Currim, S., Snodgrass, R. T., & Suh, Y. K. (2024). Identifying the root causes of DBMS suboptimality. ACM Transactions on Database Systems, 49(1), Article 4. https://doi.org/10.1145/3636425

Das, S., Grbic, M., Ilic, I., Jovandic, I., Jovanovic, A., Narasayya, V. R., Radulovic, M., Stikic, M., Xu, G., & Chaudhuri, S. (2019). Automatically indexing millions of databases in Microsoft Azure SQL Database. In Proceedings of the 2019 International Conference on Management of Data (pp. 666–679). Association for Computing Machinery. https://doi.org/10.1145/3299869.3314035

Harizopoulos, S., Abadi, D. J., Madden, S., & Stonebraker, M. (2018). OLTP through the looking glass, and what we found there. En M. L. Brodie (Ed.), Making databases work: The pragmatic wisdom of Michael Stonebraker (pp. 409–439). Association for Computing Machinery. https://doi.org/10.1145/3226595.3226635

Hashim, H. B. (2026). Performance evaluation of B-Tree and hash indexing under varying data sizes in relational database systems. Computing and Artificial Intelligence, 4(1), Article 4104. https://doi.org/10.59400/cai4104

Huang, S., Wang, Z., Wu, Y., Tu, Y., Wang, J., & Cui, B. (2025). OpDiag: Unveiling database performance anomalies through query operator attribution. IEEE Transactions on Knowledge and Data Engineering, 37(6), 3613–3626. https://doi.org/10.1109/TKDE.2025.3557049

Kleppmann, M. (2017). Designing data-intensive applications: The big ideas behind reliable, scalable, and maintainable systems. O’Reilly Media.

Kompalli, K. (2024). Automated monitoring and alerting frameworks for enterprise databases: Architecture, thresholds, and root-cause identification. International Journal of Scientific Research in Science and Technology, 11(4), 766–780. https://doi.org/10.32628/IJSRST25125216

Kurmanji, M., & Triantafillou, P. (2023). Detect, distill and update: Learned DB systems facing out of distribution data. Proceedings of the ACM on Management of Data, 1(1), 1–27. https://doi.org/10.1145/3588713

Lao, J., Wang, Y., Li, Y., Wang, J., Zhang, Y., Cheng, Z., Chen, W., Tang, M., & Wang, J. (2024). GPTuner: A manual-reading database tuning system via GPT-guided Bayesian optimization. Proceedings of the VLDB Endowment, 17(8), 1939–1952. https://doi.org/10.14778/3659437.3659449

Leis, V., Gubichev, A., Mirchev, A., Boncz, P., Kemper, A., & Neumann, T. (2015). How good are query optimizers, really? Proceedings of the VLDB Endowment, 9(3), 204–215. https://doi.org/10.14778/2850583.2850594

Li, Z., Tu, Y., & Ma, Z. (2023). A sample-aware database tuning system with deep reinforcement learning. Journal of Database Management, 35(1), 1–25. https://doi.org/10.4018/JDM.333519

Lim, W. S., Ma, L., Zhang, W., Butrovich, M., Arch, S., & Pavlo, A. (2024). Hit the gym: Accelerating query execution to efficiently bootstrap behavior models for self-driving database management systems. Proceedings of the VLDB Endowment, 17(11), 3680–3693. https://doi.org/10.14778/3681954.3682030

Ma, L., Van Aken, D., Hefny, A., Mezerhane, G., Pavlo, A., & Gordon, G. J. (2018). Query-based workload forecasting for self-driving database management systems. In Proceedings of the 2018 International Conference on Management of Data (pp. 631–645). Association for Computing Machinery. https://doi.org/10.1145/3183713.3196908

Ma, L., Zhang, W., Jiao, J., Wang, W., Butrovich, M., Lim, W. S., Menon, P., & Pavlo, A. (2021). MB2: Decomposed behavior modeling for self-driving database management systems. In Proceedings of the 2021 International Conference on Management of Data (pp. 1248–1261). Association for Computing Machinery. https://doi.org/10.1145/3448016.3457276

Shin, Y., Ahn, J., & Im, D. (2022). Join optimization for inverted index technique on relational database management systems. Expert Systems with Applications, 198, Article 116956. https://doi.org/10.1016/j.eswa.2022.116956

Solohub, V., & Pashkevych, V. (2026). Comparative analysis of query optimization techniques in modern relational database systems. Journal of Computer Sciences Institute, 39, 132–137. https://doi.org/10.35784/jcsi.8944

Stonebraker, M., & Pavlo, A. (2024). What goes around comes around... and around. ACM SIGMOD Record, 53(2), 21–37. https://doi.org/10.1145/3685980.3685984

Tong, Y., Liu, F., Xu, J., Wang, H., Zhou, K., & Miao, J. (2025). BISLearner: Block-aware index selection using attention-based reinforcement learning for data analytics. ACM Transactions on Database Systems, 51(1), Article 2. https://doi.org/10.1145/3760773

Trummer, I. (2022). DB-BERT: A database tuning tool that reads the manual. In Proceedings of the 2022 International Conference on Management of Data (pp. 190–203). Association for Computing Machinery. https://doi.org/10.1145/3514221.3517843

Van Aken, D., Yang, D., Brillard, S., Fiorino, A., Zhang, B., Billian, C., & Pavlo, A. (2021). An inquiry into machine learning-based automatic configuration tuning services on real-world database management systems. Proceedings of the VLDB Endowment, 14(7), 1241–1253. https://doi.org/10.14778/3450980.3450992

Zhang, J., Liu, Y., Zhou, K., Li, G., Xiao, Z., Cheng, B., Xing, J., Wang, Y., Cheng, T., Liu, L., Ran, M., & Li, Z. (2019). An end-to-end automatic cloud database tuning system using deep reinforcement learning. In Proceedings of the 2019 International Conference on Management of Data (pp. 415–432). Association for Computing Machinery. https://doi.org/10.1145/3299869.3300085

Creative Commons License

This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.

Copyright (c) 2026 Angel Enrique Romero Cuevas, Gabriel Sánchez Bautista