Why Java’s replaceFirst Slowed My Middleware to 10‑Second SQL Delays

Background

After a year of stable operation, a sharding middleware that can handle up to 17,000 SQL statements per second began showing occasional 10‑second timeouts on simple primary‑key updates or queries. The issue only appeared when traffic passed through the middleware, not when applications accessed the database directly.

Initial Investigation

Log analysis confirmed that the SQL execution on the database side was fast (≈0.5 ms). The problem lay between the application and the middleware.

Finding Patterns

By listing all timeout incidents, the author noticed that the slow SQLs occurred at various times of day and from different application IPs, ruling out a single host or time‑window cause. A closer look revealed two timeout events that started only three seconds apart and finished at the same moment, suggesting a shared resource contention.

Hypothesis: Middleware Thread Blocking

Further log correlation showed that both slow SQLs were processed by the same Reactor‑Thread‑2 on one of the four middleware instances. At the moment of the slowdown, this thread logged nothing for about ten seconds, indicating it was blocked.

Investigating the Root Cause

The author examined the handling of prepareStatement in the middleware. The middleware rewrites prepared statements (with ? placeholders) into plain SQL for routing, then sends the original prepared statement to the database. The rewrite used String.replaceFirst("\\?", param) inside a loop.

When reproducing the scenario offline with a 150 KB SQL, the same 10‑second delay appeared only with the replaceFirst approach.

Performance Problem in String.replaceFirst

The replaceFirst call triggers a regular‑expression engine for each placeholder, causing huge overhead for large SQL strings with many parameters. This was identified as the primary bottleneck.

Optimization Steps

Replace the regex‑based replacement with a simple split("\\?") and manual concatenation. This reduced the processing time from 10 s to about 2 s.

Further improve by using a StringBuilder instead of repeated string concatenation. The runtime dropped from 2 s to roughly 8 ms.

String[] splits = sql.split("\\?");
StringBuilder builder = new StringBuilder();
for (int i = 0; i < splits.length; i++) {
    if (i < paramNumber) {
        builder.append(splits[i]).append(getParamString(paramNumber));
    } else {
        builder.append(splits[i]);
    }
}
return builder.toString();

Conclusion

IO‑heavy threads must never perform expensive operations; even a seemingly harmless replaceFirst can become a performance nightmare under certain conditions. Careful profiling and replacing regex‑based string manipulation with efficient builders restored the middleware’s throughput.

Original article: https://my.oschina.net/alchemystar/blog/2994406