Close database connections after inactivity

Question

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.

score 1 · Answer 1 · answered Apr 25 '20 at 22:35

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.

score 0 · Answer 2 · answered Sep 27 '16 at 09:26

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

A few questions to understand the case better:

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

score 0 · Answer 3 · answered Oct 04 '16 at 03:05

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.

I'm aware that connections should not be closed after every use, and can be re-used using a pool. Connections being open for 2 months is a bit much though. The application itself never shuts down. It's not supposed to shut down, but whenever it hasn't used a connection for a certain period of time, I'd want it to close. Anyhow, this is a Mule-specific question and I want to know how to use this UCP using Mule. I have tried using beans as a way of configuring my Database connector but I've always had worse performance doing this compared to just using a default Mule connector." — DenCowboy, Oct 04 '16 at 07:26

Close database connections after inactivity

3 Answers3