Why Transaction‑Level Monitoring Beats Log‑Only Approaches in Modern Apps

In typical internet applications, an app performs four core functions: handling multiple business tasks, executing complex calculations, calling other services over the network, and accessing or updating data stored in databases or caches. Monitoring all four aspects is ideal but often too costly.

Most apps do access data, so the real monitoring challenge lies in the data layer, particularly relational databases. Unlike caches, SQL can express multi‑table joins, aggregations, pagination, and transactions, making performance analysis far more complex.

Relying on the application itself to emit detailed logs sounds attractive, but it requires a large log‑analysis platform, invasive code changes, and can degrade performance due to excessive I/O and GC overhead. Moreover, log formats vary across services, making cross‑team understanding difficult.

Middle‑layer solutions can overcome these limitations. A middleware that tracks session state can identify which SQL statements belong to the same transaction, map SQL traffic to originating IPs (hence to specific applications), and operate without modifying application code or needing heavy log‑analysis infrastructure.

OneProxy Middleware Capabilities

Collects SQL execution statistics, including network latency, giving a more realistic view of performance.

Tracks table‑level access patterns (CRUD operations) across the database.

Aggregates SQL activity per IP address, enabling identification of top‑talking services.

Detects multi‑table usage within the same statement or transaction.

Provides detailed transaction metrics: constituent SQLs, call counts, total duration, pure SQL time, DML time, average times, and busy times.

These metrics reveal that raw SQL execution time is often a small fraction of total transaction time; the dominant cost may be application‑level orchestration or network delays. Optimizing the transaction as a whole—by reducing unnecessary calls or pre‑bundling statements—can yield greater performance gains than tuning individual queries.

Practical Example

A screenshot from OneProxy shows three captured transactions: one from a sysbench OLTP benchmark and two from MySQL client inputs. The displayed fields include Exec (total executions), Call (SQL calls per transaction), DML Call, Time (total including network and GC), Avg Time, Busy (pure SQL time), DML Busy, and Avg Busy.

Analysis of a WordPress workload demonstrates how transaction‑level data can guide database vertical sharding decisions. By ranking tables based on query frequency and row counts, the author suggests moving wp_posts and wp_postmeta to separate shards, while noting that OneProxy’s read/write splitting feature could also address the load.

The article concludes that middleware‑based transaction monitoring provides a low‑cost, high‑value insight into application‑database interactions, facilitating communication between developers and DBAs and enabling targeted optimizations without invasive code changes.