CODEWITHSUNDEEP
databaseconnectiontimeoutmuledatabase-connection
DenCowboy
DenCowboy

Reputation: 15076

Close database connections after inactivity

I have a Mule application that connects to an Oracle database. The application is a SOAP api which allows executing SQL Stored Procedures. My connector is set up to use connection pooling and I've been monitoring the connections themselves. I have a maximum pool size of 20 and when doing calls to the database, I can see them opening (netstat -ntl | grep PORTNUMBER).

 tcp4       0      0  IP HERE     OTHER IP HERE       SYN_SENT
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4       0      0  IP HERE     OTHER IP HERE       ESTABLISHED
 tcp4      10      0  IP HERE     OTHER IP HERE       ESTABLISHED

When the calls are done, I expect the connections to be closed after a certain period of time. This does not happen. I've noticed that when the application was running on a server, connections were still open from july (that's a couple of months back).

The only way I found so far that actually closes the connections after a couple of seconds is by enabling XA transactions and setting the Connection Timeout. However, this completely messes up the performance of the application and it's unnecessary overhead.

How would I go about adding such a timeout without using XA connections? I'd like for my database connections to be closed after 20 seconds of inactivity.

Thank you

Edit: Generic database connector is used - Mule version 3.8.0 We have a maximum number of connections that are allowed to the database, we have multiple instances of this flow running. This means connections are reserved by one of the instances which causes the other instances unable to get new connections.

The specific issue we've had was that one instance still had 120 connections reserved, even though the last time it ran was weeks before. When the second instance requested more connections, it could only get 30 since the maximum on the database side is 150.

Upvotes: 11

Views: 2655

Answers (3)

aled
aled

Reputation: 25699

You should use a connection pool implementation that provides you control of the time to live of a connection. Ideally the pool should also provide validation queries to detect stale queries.

For example the c3p0 pool has a configuration called maxConnectionAge that seems to match your needs. maxIdleTime also could be of interest.

Upvotes: 1

Scott Boring
Scott Boring

Reputation: 2781

Database connections via JDBC are designed to stay open with the intention of being reused. In general, most database technologies including the next generation NoSQL databases, have expensive startup and shutdown costs. Database connections should be established at application startup and closed gracefully at application shutdown. You should not be closing connections after each usage.

Oracle offers a connection pool called UCP. UCP offers options to control stale connections which includes setting a max reuse time and inactive connection timeout among other options.

This can be useful for returning resources to the application as well as checking for broken connections. Regardless, connections should be reused multiple times before closing.

Upvotes: 0

Ivan
Ivan

Reputation: 3836

You can try using Oracle Transparent Connection Caching if will have no luck with Mule.

A few questions to understand the case better:

  1. which type of connector you're using (jdbc/database) and which version of Mule is that?
  2. why do you care about connection being open afterwards? are you observing some other symptoms you're not happy with?

Upvotes: 0

Related Questions