Mastering MySQL Performance: 5 Key Issues and Proven Tuning Strategies

Five Characteristics of System Performance Problems

System response is consistently slow regardless of load.

Performance degrades over time, eventually locking the system.

Sudden spikes of errors appear while the system was previously stable.

Partial functionality failures, e.g., specific pages time out.

Performance worsens as user load increases and recovers when load drops.

Performance Investigation Methodology

The investigation follows two overarching principles: proactive communication to minimise impact and rigorous analysis to derive justified solutions.

Gather information (5W1H) : what, when, where, how, who, plus logs and traffic data.

Customer reassurance : calm users to prevent panic‑driven incidents.

Emergency response : apply historical experience for quick mitigation.

Full‑link analysis : examine system‑level and transaction‑level traces.

Solution design : implement a temporary fix for immediate relief, then a permanent fix to prevent recurrence.

Post‑mortem : summarise lessons, capture optimisation points, and disseminate knowledge.

A fishbone diagram further breaks down the steps: eliminate impact, collect现场 data, define problem scope, analyse, devise solutions, and summarise.

Java Middleware Analysis

Comparison of several javacore files revealed connection‑pool exhaustion as a primary cause of latency. OOM analysis identified a massive BankFunctionTypePool object consuming ~1 GB, exhausting JVM memory; combined heap‑dump and javacore analysis is recommended for rapid diagnosis.

Database Issue Analysis

For MySQL, start with mysql.err to detect connection failures, then examine slow‑queries.log (transactions >10 s) and binlog for replication issues. Oracle troubleshooting involves listener.log, lsnrctl status, alert.log, AWR/ASH reports, and historical snapshot tables ( dba_hist_sqlstat, dba_hist_snapshot).

Performance analysis includes reviewing execution plans ( EXPLAIN for MySQL, v$sql_plan for Oracle), checking for deadlocks, and monitoring wait events.

MySQL Tuning Strategies

1) Index Optimization

Focus on execution‑plan types: system > const > eq_ref > ref > … > ALL. Prefer short key_len, low rows, and high filtered values. Ensure indexes are used for range scans (ref) rather than full table scans (ALL).

2) Sharding / Partitioning

Industry thresholds for sharding range from 0.5 M (Alibaba) to 2 M (common practice) and 3 M (internal policy). Calculations based on B+‑tree fan‑out (≈1280 leaves per node) and leaf‑node row capacity determine the data‑volume limit.

3) Lock Management

MySQL uses record and gap locks; READ‑COMMITTED reduces gap‑lock impact. Monitor performance_schema (5.7) or information_schema (8.0) for lock contention. Typical lock‑wait timeout is set to 5–10 s. Deadlock detection follows a depth‑first search in lock0lock.cc, rolling back the transaction with the smallest undo log.

Performance Governance System

The governance framework integrates DevOps pipelines, periodic QA checks, and stage‑wise controls:

Design phase : enforce metadata standards, reusable structures, and forward‑looking schemas.

Development phase : automate SQL injection checks and coding standards via SonarQube plugins.

Testing phase : conduct security, performance, and chaos testing.

Release phase : SRE provides situational reports and remediation advice.

Operation phase : monitor slow SQL, perform knowledge‑base case studies, and apply AIOps root‑cause analysis.

Additional controls include automated quality gates (custom SonarQube rules based on Druid), large‑transaction detection and killing (different thresholds for OLTP vs batch workloads), and proactive source‑code mastery.

Future Outlook

Goals are full‑stack end‑to‑end monitoring, advanced AIOps achieving 1‑5‑10 (detect‑respond‑recover) objectives, and deeper understanding of open‑source component source code to mitigate hidden bugs.