Database Connection Lifetime

By default, when MFDBFH establishes a database connection it will keep the connection open for the remainder of the process's execution. This is done to avoid the unnecessary performance overhead of establishing and disconnecting database connections. However, there may be times when the lifetime of database connections needs to be restricted. For example:

• The SQL Server master, and PostgreSQL postgres database connections are only required when determining whether a database exists, or when creating a new database. These operations are generally performed the very first time that a datastore needs to be accessed by a process, but after that are not required.
• A database server, such as PostgreSQL, may not efficiently handle more than a few hundred active connections at any one time. In such circumstances, it would be preferable to only establish database connections when they are actually needed, and then disconnect them once the associated database operations are complete (for example, the lifetime of a batch job).
• When optimised I/O is enabled, MFDBFH will perform I/O operations for a file on a background thread. A separate database connection to the one used for database operations on the main execution thread will be used. Once the file is closed, the background thread is stopped, and the database connection is returned to an internal cache for later re-use. By default, MFDBFH will cache up to 10 connections. Instead of caching the connection, it may be preferable to close the connection instead to avoid unnecessary database server resources being tied up for a connection that may never get re-used.

Use the dbfhconfig -connopts command line utility to update the database configuration file to override the default behavior. The following table shows the entries that each option creates and an explanation of the altered behavior: