How I Fixed a 10‑Second MySQL Query Using Druid and JMC Monitoring

Problem Overview

In a Docker test environment, application A called application B via Dubbo RPC and encountered a timeout. Debugging revealed that a simple SQL statement, originally a batch insert, took over 1000 ms. The database process list showed many threads stuck in a deadlock with status 'sending data'.

Using Druid to Monitor SQL Execution

To pinpoint the slowdown, the Alibaba Druid connection pool was enabled for SQL monitoring. The Druid dashboard displayed each SQL's average, slowest, execution count, and error count. Normal queries run within 10 ms (30 ms for large data), so the observed 1000 ms was unacceptable.

Remote Monitoring with JMC

Java Mission Control (JMC) was used to monitor the Tomcat JVM remotely. The following JVM arguments were added to catalina.sh:

-Dcom.sun.management.jmxremote=true
-Dcom.sun.management.jmxremote.port=8888
-Dcom.sun.management.jmxremote.ssl=false
-Dcom.sun.management.jmxremote.authenticate=false
-XX:+UnlockCommercialFeatures -XX:+FlightRecorder

After restarting Tomcat, a JMX connection was created in JMC, allowing a flight recorder session to capture CPU, memory, and thread metrics. No obvious JVM anomalies were found.

Investigating MySQL Locks

Since the code and JVM appeared healthy, the focus shifted to MySQL. The SHOW PROCESSLIST; command revealed many threads in Sending data state. One thread was repeatedly executing a function that generated primary‑key IDs:

SELECT next_value INTO ret_val FROM `xxx` WHERE table_name=tableName FOR UPDATE;

UPDATE `xxx` SET current_value=current_value+step, next_value=next_value+step WHERE table_name=tableName;

The SELECT … FOR UPDATE statement placed an exclusive lock on the table. Because the table lacked an index on the table_name column, InnoDB row locks were applied to the entire index range, causing lock contention and deadlocks across multiple services that shared the same sequence table.

Resolution

The blocking threads were killed, and an index was added to the table_name column of the sequence table. After the index was created, the deadlock disappeared, query latency returned to normal, and the Dubbo RPC calls succeeded without timeout.

Key Takeaways

When MySQL queries become unexpectedly slow, after ruling out application code, check for database deadlocks, examine the process list, and verify that tables involved in frequent SELECT … FOR UPDATE operations have appropriate indexes. Monitoring tools like Druid and JMC can provide valuable visibility into SQL performance and JVM behavior.