Tomcat8部署新WAR包后MySQL查询运行数小时后性能大幅下降

Hey there, let's dig into why your JDBC-based query is slowing down hours after deployment—this is a common pitfall when switching from ORMs like JOOQ to raw JDBC, so let's break down the most likely causes and actionable fixes:

JOOQ handles connection management under the hood, but raw JDBC puts that responsibility on you. If you're calling DriverManager.getConnection() directly without reusing or properly closing connections, you'll quickly hit connection leaks or exhaust your database's connection limit. Here's how to fix this:

• Always use try-with-resources to auto-close connections, statements, and result sets—this eliminates leaks even if exceptions are thrown:

  try (Connection conn = getPooledConnection();
       PreparedStatement stmt = conn.prepareStatement("SELECT id, name, description FROM forums");
       ResultSet rs = stmt.executeQuery()) {
      // Process your result set here
  } catch (SQLException e) {
      // Log or handle the error appropriately
  }
  

• Configure Tomcat's built-in JDBC connection pool instead of manual connection handling. Add this snippet to your Tomcat context.xml:

  <Resource name="jdbc/ForumDB"
            auth="Container"
            type="javax.sql.DataSource"
            maxTotal="100"
            maxIdle="30"
            maxWaitMillis="10000"
            username="your_db_user"
            password="your_db_pass"
            driverClassName="com.mysql.cj.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/your_forum_db?useSSL=false&amp;serverTimezone=UTC"
            validationQuery="SELECT 1"
            testOnBorrow="true"/>
  

  Fetch connections via JNDI afterward—Tomcat will handle connection reuse, validation, and cleanup automatically.

Even with a pool, if you accidentally hold onto connections (e.g., skipping closure in edge cases), you'll see connections pile up as "sleeping" processes in MySQL. To verify:

• Run this MySQL command to check active connections:

  SHOW PROCESSLIST;
  

  If you see dozens of sleep-state connections or the count is near your database's max_connections limit, you've got a leak. Double-check your code for missing finally blocks or incomplete try-with-resources usage.

While fetching all forum sections seems simple, over time:

• The forums table may have grown, making full-table scans slower.
• MySQL's query cache could have been invalidated, forcing repeated full executions.
• Locking or contention from other queries might be slowing this one down.

To debug:

• Use EXPLAIN to inspect how MySQL runs your query:

  EXPLAIN SELECT * FROM forums;
  

  If it shows type: ALL (full table scan) and the table is large, add an index on the primary key or commonly accessed columns to speed up row retrieval.
• Enable MySQL's slow query log to track performance over time. Add these lines to your my.cnf:

  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/slow_queries.log
  long_query_time = 2
  

  Restart MySQL, then check the log after a few hours to confirm if this query is actually slow, or if other queries are bogging down the database.

Sometimes what looks like a database slowdown is actually Tomcat or the JVM struggling:

• GC thrashing: If your JVM lacks enough heap space, frequent garbage collection pauses can make queries appear slow. Add GC logging to your Tomcat startup script:

  -Xloggc:/var/log/tomcat/gc.log -XX:+PrintGCDetails -XX:+PrintGCDateStamps
  

  Look for frequent Full GC events—if found, increase the heap size with -Xmx (e.g., -Xmx2g for 2GB of heap).
• Thread pool exhaustion: Check Tomcat's server.xml Connector settings. Ensure maxThreads is set high enough to handle concurrent requests (start with maxThreads="200" if unsure) and minSpareThreads keeps warm threads ready.

Over time, idle connections in your pool can be closed by MySQL. The validationQuery and testOnBorrow settings in the Tomcat pool fix this by validating connections before use, ensuring you never get a dead connection that causes retries or delays.

Start with checking for connection leaks and setting up the Tomcat connection pool—these are the most likely fixes for this "works at first, slows down later" pattern.

内容的提问来源于stack exchange，提问作者Errore Fatale