Exhausted Connection Pool Investigation

You can investigate the reasons why the JDatabase C connection pool is exhausted.

If you notice that the JDatabase C connection pool is exhausted, investigate the possible issues using one of the following methods:
  • Code: Start collecting thread dumps, the code can highlight the transactions and queries that are consuming each connection. Thread dumps also can identify long-running queries or if the number of queries being handled exceed the current capacity limits.
  • Database: Perform an analysis on the database to confirm if the Database Transaction Unit (DTU) usage is high, or if there are any blocked sessions.

Application Threads

Here the focus is specifically on the analysis of threads when knowing that the database pool is exhausted.

For a full reference on thread analysis: 3059625 - How to Monitor and Analyze Thread Utilization on Commerce Cloud Using DynatraceInformation published on SAP site.

Using the dashboard, you can identify pods that have reached the database connection pool limit. Use the Profiling and Optimization tool to analyze these threads.

  1. From the side menu, select Start of the navigation pathApplications & Microservices Next navigation step Profiling and OptimizationEnd of the navigation path.
  2. Click Continuous CPU profiling.
  3. Select a service to analyze, click More, and select Threads.
  4. From the Thread breakdown page, click Thread states, then Network I/O to filter for results that are relevant to the database connection pool problem.
Filtering for Network I/O identifies threads that use a connection from the pool. These threads and are often waiting for a response from the database through a socket connection. With thread dumps, you can identify these threads using a stack trace similar to the following snippet, where java.net.SocketInputStream.socketRead0 contributes to the Network I/O in Dynatrace.
"hybrisHTTP2" #133 daemon prio=5 os_prio=0 cpu=177854.95ms elapsed=19321.61s allocated=33142M defined_classes=224 tid=0x00007f826b142800 nid=0x1b4 runnable [0x00007f81761de000]
 java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(java.base@11.0.10/Native Method)
at java.net.SocketInputStream.socketRead(java.base@11.0.10/SocketInputStream.java:115)
at java.net.SocketInputStream.read(java.base@11.0.10/SocketInputStream.java:168)
at java.net.SocketInputStream.read(java.base@11.0.10/SocketInputStream.java:140)
at com.microsoft.sqlserver.jdatabasec.TDSChannel$ProxyInputStream.readInternal(IOBuffer.java:1006)
at com.microsoft.sqlserver.jdatabasec.TDSChannel$ProxyInputStream.read(IOBuffer.java:996)
The system uses socket connections for communication with the database and for requests to external servers or the communication across the nodes in the cluster. The latter corresponds to thread groups like the following, which you can ignore when investigating the database connection pool usage:
Connection.Receiver [*.*.*.*:* - *.*.*.*:*]-*,hybris-broadcast,hybrisnode
FD_SOCK pinger-*,hybris-broadcast,hybrisnode
FD_SOCK conn-handler-*,hybris-broadcast,hybrisnode
DiagHandler-*,hybris-broadcast,hybrisnode
jgroups-*,hybris-broadcast,hybrisnode

hybrisHTTP is usually the thread group of interest when it comes to the database connection pool. If you select More Start of the navigation path More Next navigation step  Next navigation step Method hotspotsEnd of the navigation path, you can review the method hotspots of an individual thread.

Individual Thread Analysis

From the list of thread groups, select a group to analyze, such as hybrisHTTP, More, and select Method hotspots.

When reviewing the method hotspots of an individual thread, narrow the time range down even further to one minute to determine if the same code is running for the entire duration, or if multiple samples are being analyzed.

Sometimes, the number of samples will be 1, which means the call hierarchy represents a single stack trace for an individual request. If 100% of the minute is spent on a single stack for the same request, this can indicate the presence of a slow query or the database DTU has reached its limit.

Stack trace can identify the code that leads to the requests to the database. The drawback is that you need to perform this analysis for each individual thread to get a full picture of the call hierarchy. It can be more efficient to analyze the entire thread group.

Thread Group Analysis

Instead of clicking on hybrisHTTP, you can use the More button to view the method hotspots of the entire thread group.

To narrow the results, select Hotspots in the Method hotspots page. Review all stack traces that lead to a request to the database. Look for JDatabase C related packages and drill down to determine the origin of the most common requests.

Database Queries and DTU

To discover any long-running transactions or queries that impact the database connection pool utilization, include the following request types in your analysis:
  • SQL Transactions - for Commits and Rollbacks
  • SQL Modifications - for Updates, Inserts, and Deletes
  • SQL Queries or Procedures

For each of these request types, click View database statements to see relevant information.

For a full reference on database performance analysis, see 3059664 - How to Analyze Database Performance and SQL Queries on Commerce Cloud Using DynatraceInformation published on SAP site.

For each request type, use the PurePaths feature with a Response time filter to identify the slowest requests. If any query has an execution time measured in minutes, this is a strong indication that there are blocked sessions on the database.

For more information, see 2852560 - How to check Azure MSSQL database information from hACInformation published on SAP site

When you discover blocked sessions, restart the pods from which they originate so that you can temporarily release the locks. If there’s no locking on the database level, it's possible that the DTU usage has reached its limit. This scenario can result in an exhaustion of the database connection pool. Review the DTU Usage from the Administration Console to verify if this is true.