|Syntactic Confectionery Delight|
Best practices for closing database connections?by Polyglot (Hermit)
|on Mar 17, 2022 at 09:47 UTC||Need Help??|
Polyglot has asked for the wisdom of the Perl Monks concerning the following question:
After years of running a script with no such issue, an error has emerged with respect to "too many connections." The database is set at the default 150 connections, and with the expected traffic, this should be plenty. Instead of increasing the max_connections variable, I would like to decrease the number of connections the script actually makes. (A single connection would be nice.)
The script makes anywhere from about 20 to 80 requests from the database each time it is run. I have tried both explicitly closing the connection after each request (i.e. (1) open DBI connection, (2) run query, (3) parse results, and (4) close connection), AND I have tried leaving the connection unclosed with the assumption that this is automatically done in garbage collection.
Reading online I see multiple opinions on this subject. Should the connection be explicitly closed? and if so, should some command, e.g. "commit", be given first? Should a single connection be opened, and all queries funneled through that before closing it (and the script itself)?
What is considered the best practice?
Note: I did try several searches in Super Search here and found little that seemed on topic. It is likely I've missed something, not finding the right keywords to use, so if there is something here on the subject already, I would welcome a link.