QUESTION: Should I be concerned about DB connection issues? We have around 50 tasks that run every 1 to 5 minutes. Is there a reason not to set the connection timeout to 1 day considering our use case?
Lucee: 5.3.9.133
USE CASE
We are working with a few TEMP tables within a Postgres DB from a backend reports server (only scheduled tasks and a few devs use this machine). Postrgres does NOT support Global temp tables. Therefore every time the DB connection is reset, the tables are dropped. I looked in the Lucee Admin and I noticed that the default settings were:
As long as we refresh the page, it stays active - but after 5 minutes the connection closes. So I upped the Connection idle timeout to 5 minutes and the Connection timeout to 30 minutes (see screenshot).
You may need to create a “TEMP” table for your application.
You can look at the docs, Datasources :: Lucee Documentation, you may be able to set the connection time out to a value in days by setting the connection timeout at the application.cfc / cfm level by setting the " connectionTimeout" value to 1440 (1 day) or 7980 (1 week) or 43200 (30 days).
As I understand it, Lucee normally pulls a connection from the connection pool with every database query, so under load every statement in a page request may be handled by a different connection (although that is not normal).
There is an experimental option to disable this behavior and use one connection per page request but apparently it doesn’t work with almost any database yet. The option is called “Exclusive connections for request” as you can see in the screenshot you posted above.
That behavior is default for ACF and we would be delighted if someone who understands the issue could make the changes to make it work.
Please note that even if that problem is resolved, you should expect to get a different connection for every page request by the way. Connection specific temporary tables are only useful while you are in control of a connection and with connection pooling you probably want to clear them both at the beginning and at the end of every request that uses them.
