AppTrace: An Automated System for Detecting and Analyzing Slow SQL in Application Performance Testing

Author : Sedro, a senior test engineer at Ctrip, focuses on testing technology exploration and tool development.

Application Performance Issues – In many testing and production incidents, database operations cause performance bottlenecks, often because indexes are missing, unused, or ineffective, accounting for 60%‑80% of performance problems.

Traditional performance testing requires extensive resources and specialized personnel, which many organizations lack, especially under rapid release cycles.

AppTrace Goal – Provide a simple, automated, and efficient solution to eliminate the majority of SQL‑related performance issues during the testing phase.

Identify slow SQL as a primary cause of performance degradation.

Offer an easy way to discover potential SQL performance problems.

Lower the technical difficulty for testers to find slow SQL.

Achieve near‑100% coverage of SQL performance detection.

Produce analysis results that closely reflect production behavior.

Basic Process – AppTrace captures all SQL statements during functional and interface testing, sends them to a production mirror database for execution‑plan retrieval, and alerts on suspected performance‑risk SQL for manual verification and optimization.

AppTrace Architecture

CPTrace (CtripPaymentTrace) Component : Based on BTrace, it captures SQL statements and stores them. It consists of: CPTraceClient : Compiles generic monitoring scripts to enable the CPTrace Agent to capture SQL. CPTraceAgent : Captures SQL and persists it.

AppTraceAgent Component : Parses the captured SQL from CPTrace and forwards it to the AppTrace Server via MQ.

AppTraceServer Component : Retrieves execution plans from the production mirror, analyzes them against predefined risk rules, and generates alerts.

Processing Flow – The three components work together to automatically capture, store, and analyze SQL, then issue risk‑based alerts as illustrated in the diagram:

Technical Solution

1) Dynamic SQL Tracing – Uses the safe JVM tracing tool BTrace. Scripts are written in Java‑like syntax and attached to the target JVM via the Attach API. Example annotation:

@OnMethod(class="com.example.Service", method="execute", location=@Location(Kind.ENTRY))

The script modifies java.sql.Connection and java.sql.PreparedStatement via JDK Instrumentation, records SQL and parameters to sqltrace.txt , and obtains the execution plan from the mirror database.

2) Isolation of Risk Analysis – The tracing plugin (apptrace‑agent) focuses on capturing SQL and parameters, while the risk analysis system (apptrace‑server) evaluates the captured SQL against risk rules.

3) Execution‑Plan Analysis & Alerting – Uses the database’s EXPLAIN interface on the mirror to obtain execution plans, compares them against configurable risk thresholds, and sends email alerts to developers until the SQL is optimized.

Performance Analysis Method – Relies on execution‑plan data combined with risk thresholds to determine if a SQL statement is problematic.

SQL Capture Technique – Core capture is built on BTrace, which injects bytecode at runtime with minimal performance impact, allowing real‑time SQL tracing without modifying application code.

Conclusion – The tracing plugin integrates with the deployment pipeline, automatically starts after application release, and has successfully identified and resolved many SQL performance risks. As more services adopt AppTrace, further scaling and refinement are planned to improve detection accuracy and coverage.