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.

Blessings,

~Polyglot~

In reply to Best practices for closing database connections? by Polyglot

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.