Mule Application becomes Unresponsive or Shuts Down Due to Long Database Connection Pool `maxWait` Time

CAUSE

• maxWait is the time a thread waits for a free connection when the pool is exhausted; if the wait exceeds it, the call fails with a connection-timeout exception.
• After that wait time it will spin up a new connection until it reaches max pool size
• If the max wait is set to 0 , it will wait indefinately to acquire a connection under load
• The `maxWait` parameter within the `<db:pooling-profile>` configuration for a database connector defaults to a unit of seconds
• if the `maxWaitUnit` attribute is not explicitly defined. When a large numeric value (e.g., `10000`) is assigned to `maxWait` without specifying `maxWaitUnit`, the application attempts to wait for an excessively long duration (e.g., 10000 seconds, which is over 2.5 hours) to acquire a connection from the pool.
• This prolonged waiting can exhaust application threads, leading to the application becoming unresponsive, experiencing severe performance degradation, or ultimately restarting/shutting down due to resource starvation.

SOLUTION

To resolve this issue, explicitly define the `maxWaitUnit` attribute in your database connector's pooling profile and set a reasonable `maxWait` value.

• Locate your Database Connector Configuration: Open your Mule application's configuration file (e.g., `mule-app.xml`) and find the `<db:config>` element for the affected database connection.
• Modify the Pooling Profile:Within the `<db:config>` element, locate the `<db:pooling-profile>` section.
• Add `maxWaitUnit` Attribute:Add the `maxWaitUnit` attribute to the `<db:pooling-profile>` and set its value to `MILLISECONDS` or `MICROSECONDS`.
• Adjust `maxWait` Value:Ensure the `maxWait` value reflects the desired timeout in the specified unit. For example, if you want a 10-second timeout, set `maxWait="10000"` and `maxWaitUnit="MILLISECONDS"`.
• Example Configuration:
  <db:config name="Database_Config_YourApp" doc:name="Database Config">
      <db:mssql-connection host="${DB.host}" port="${DB.port}" user="${DB.user}" password="${DB.password}" databaseName="${DB.databaseName}">
          <db:pooling-profile
              maxPoolSize="150"
              minPoolSize="13"
              acquireIncrement="10"
              maxIdleTime="300"
              maxWait="10000"
              maxWaitUnit="MILLISECONDS"/> <!-- Explicitly set maxWaitUnit -->
      </db:mssql-connection>
  </db:config>

• Redeploy the Application: