Spring MVC+Hibernate应用数据库连接频繁占满问题排查求助

Hey there, let's work through this connection exhaustion issue you're facing with your Spring MVC 4.2.5 and Hibernate 4.3.11 app. First, let's clarify some key points about the metrics you're seeing, then dive into actionable steps to fix this.

First: Understand the Hibernate Statistics Metrics #

Your log shows connectCount=>139591 and sessionOpenCount=>139590 – as your team noted, these aren't the current active database connections, but rather cumulative counts:

• getConnectCount(): Total number of times Hibernate requested a connection from the pool (not active connections)
• getSessionOpenCount(): Total number of Sessions opened
• The gap between open and close counts (sessionOpenCount vs sessionCloseCount) is a red flag if it grows over time – that means Sessions aren't being properly closed, which can lead to connection leaks.

Step 1: Check Real-Time Connection Status #

First, get visibility into actual active connections:

• Database-side: Run a query to see active connections. For MySQL:

  SHOW PROCESSLIST;
  

  Look for rows where Command is Query (active) vs Sleep (idle). Count the active ones to confirm if they're hitting your database's connection limit.
• Connection Pool Metrics: If you're using a pool like C3P0 (common with Hibernate 4) or Tomcat JDBC Pool, enable metrics:
  • For C3P0, add these properties to your Hibernate config (dev environment only):

    hibernate.c3p0.debugUnreturnedConnectionStackTraces=true
    hibernate.c3p0.unreturnedConnectionTimeout=300
    

    This will log stack traces for connections that aren't returned to the pool within 5 minutes, helping you find leak points.
  • Use JMX to monitor pool metrics like activeConnections, idleConnections, and numConnections.

Step 2: Fix Connection Leaks #

The most common cause of exhaustion is unclosed Sessions/connections. Here's how to verify and fix:

• Validate getCurrentSession() Usage: getCurrentSession() is tied to Spring's transaction context. Ensure all code using it runs within a @Transactional method. If you call it outside a transaction, Hibernate may not auto-close the Session, leaving connections hanging.
• Check Exception Handling: Make sure exceptions trigger transaction rollback (Spring's default behavior for unchecked exceptions, but verify for checked exceptions). A stuck transaction can leave connections open.
• Add Session Close Logging: Temporarily add logs to track Session creation and closure, or use Spring AOP to monitor when Sessions are opened/closed. Look for mismatches where opens outnumber closes.

Step 3: Optimize Connection Pool Configuration #

Even without leaks, poor pool settings can cause exhaustion. Here's a sample C3P0 config tailored for most apps (adjust based on your database's capacity):

# Core pool settings
hibernate.c3p0.max_size=20          # Max active connections (match your DB's max_connections)
hibernate.c3p0.min_size=5           # Min idle connections
hibernate.c3p0.timeout=1800         # Idle connections are recycled after 30 mins
hibernate.c3p0.idle_test_period=300 # Test idle connections every 5 mins
hibernate.c3p0.max_statements=50    # Cache prepared statements to reduce overhead

# Leak detection (dev only)
hibernate.c3p0.unreturnedConnectionTimeout=300
hibernate.c3p0.debugUnreturnedConnectionStackTraces=true

If using another pool like HikariCP, focus on maximumPoolSize, idleTimeout, and connectionTimeout parameters.

Step 4: Ensure Secondary Cache Is Actually Working #

You've enabled the cache, but it might not be active for your entities/queries:

• Entity Caching: Add @Cacheable and @Cache annotations to your entity classes:

  @Entity
  @Cacheable
  @Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "userRegion")
  public class User {
      // Entity fields and methods
  }
  

• Query Caching: For HQL queries, enable caching with setCacheable(true):

  Query<User> query = session.createQuery("from User where id = :userId", User.class);
  query.setParameter("userId", 123);
  query.setCacheable(true);
  query.setCacheRegion("userQueryRegion");
  

• Validate EhCache Config: Check your ehcache.xml to ensure cache regions have reasonable time-to-live (TTL) and max entries. A cache that expires too quickly won't reduce database hits.

Step 5: Fix Long Transactions & Slow Queries #

Long-running transactions tie up connections, preventing other requests from using them:

• Identify Slow Queries: Enable your database's slow query log (e.g., MySQL's slow_query_log) to find queries taking too long. Optimize them with indexes or rewritten SQL.
• Split Long Transactions: Break up large methods into smaller, focused transactions. Move non-database operations (like external API calls) outside the transaction scope.

Final Checks #

• Monitor sessionOpenCount and sessionCloseCount over time. If they stay roughly equal, Sessions are being closed properly – exhaustion is likely due to pool size or query load.
• If sessionCloseCount lags behind sessionOpenCount, you have a leak – use the connection pool's leak detection logs to trace the problematic code.

内容的提问来源于stack exchange，提问作者Sweety Agrawal