Strategi Tuning MySQL Berbasis Slow Query Log untuk Meningkatkan Throughput Aplikasi Transaksional

Abstract

In the era of higher education digitalization, collaborative information systems such as faculty research platforms often suffer performance degradation due to high transaction volumes burdening MySQL databases. This common issue manifests as increased query latency, reducing application throughput by 40-50% during peak loads, like joint proposal submissions or collaborative research data analysis. Specifically in university settings, where faculty rely on these systems for real-time research data sharing, database bottlenecks critically impede academic productivity and cross-disciplinary collaboration.

This study addresses this specific challenge by proposing a MySQL tuning strategy based on slow query log analysis for transactional applications in faculty collaborative information systems. The background stems from observations that 70% of slow queries arise from suboptimal indexing and server configurations, as experienced in Indonesian state universities. The primary objective is to boost throughput by 2-3 times without additional hardware, while preserving collaborative data integrity.

The research method involves identifying slow queries via MySQL slow query log, pattern analysis using tools like pt-query-digest and MySQL Workbench, followed by index tuning, query rewriting, and adjustments to parameters such as innodb_buffer_pool_size and query_cache. Testing was conducted in a simulated environment with JMeter for loads of 1,000-5,000 transactions per second, comparing pre- and post-tuning performance.

Results demonstrate a 250% throughput increase (from 450 TPS to 1,125 TPS), with average query execution time reduced from 2.5 seconds to 0.8 seconds. Key findings highlight that log-based tuning prioritization outperforms generic approaches, particularly for read-write intensive patterns in collaborative systems.

In conclusion, this strategy offers broad applicability for academic database optimization, enhancing faculty collaboration efficiency and system scalability. Implications include up to 30% operational cost savings and improved user satisfaction.