How to solve database connection leaks?

Body

Maximo manages database connection pool internally in its code and does not use the application server database connection pool. At times, there are database connection leaks due to incorrect code or memory leaks that result in database connections increasing and stopping the database.

The database connection leaks should be identified and fixed in the code. It can be accomplished by using the dbconnection watchdog logger. The log files can indicate where the connection leaks might be.

Ideally, all database connection leaks should be fixed by fixing the offending code, but sometimes it may be difficult to find the connection leak and it may be necessary to find an alternate solution to release the leaking database connection. To address this problem, from Maximo 7.5.0.3 onwards, three new properties are added and through these properties, Maximo application server database connection pool will release the lost connection automatically.

The Maximo system properties used for this feature are:

mxe.db.closelongrunconn - default is false

mxe.db.longruntimelimit - default 180 minutes.

mxe.db.detectlongrunconninterval - default is 30 minutes. This is the frequency in which the long running connections are checked.

Note: If the above property is not created as part of the fixpack application, you must define the property in the system properties application. The value of this property cannot be less than 30 minutes.

From Maximo and Control Desk Database Connection Leak Resolution

When mxe.db.closelongrunconn is set to true, Maximo will close the lost connections if the connections have been held for greater than the value of mxe.db.longruntimelimit in minutes. Maximo will check for any lost connections in a 30 minute frequency. This value is not configurable.

After changing the value for mxe.db.closelongrunconn, click Live Refresh to apply the changes

If the application server finds any database connection held for greater than the value of mxe.db.longruntimelimit, it will check in the database to make sure the connection is not actually being used by any processes in that time duration. If the connection was not used by any process, then it sets the Maximo database connection reference count to zero and returns the connection back to the connection pool. With the database connection back in the connection pool, it can be used for later database connection needs or be closed if there are excess free connections.

If database connection is held for a long time but is being used by some process, then the database will indicate usage of the connection. In this case, Maximo will not return the connection back to the database connection pool and leave it as is. This makes sure database connections used by some long running process are not closed by accident.

For Maximo application server to check the database connection usage, Maximo database account should have permission to read the following database system tables.

Oracle : v$session, gv$session (on RAC)

Microsoft SQL Server : master.sysprocesses

Db2 : SYSIBMADM.SNAPAPPL_INFO

Db2 monitoring needs to be enabled to trace usage.

By default, mxe.db.closelongrunconn is set to false. Maximo will not automatically close any database connections that are lost. Note that Maximo connection pool mechanism closes unused free connections and decreases the connection pool to the value of the system property mxe.db.maxFreeConnections. This is independent of the process that closes the lost connections. Closing unused database connections is part of the connection pool management.

The default value for mxe.db.longruntimelimit is 180 minutes. This time is used to check if the database connection was actually used by any process at the database level. If the database connection is not used at the database level, it will be returned to the Maximo database connection pool. This value can be adjusted as needed.

If there are database connections outside of Maximo application server, Maximo is unaware of it and they are outside the scope of Maximo database connection pool management.

With this feature enabled, long-running database connection leaks should be resolved without having to restart the Maximo server to release the database connections.