“mod_perl,” o’course, “is a very different kettle o’ fish,” because of its inherently persistent nature as an integrated part of the long life of the web-server process(es).

As mentioned in http://modperlbook.org/html/20-1-1-Apache-DBI-Connections.html (for example...) database handles routinely get special treatment in the mod_perl environment, and these treatments are more-or-less designed to ignore exactly what you do.

Nevertheless, in writing my original code, for whatever environment, I do try to follow this gentlemanly maxim:   “Be tidy.   Wipe the toilet seat.   Flush.   Lower the lid for the next lady.”   Whatever you open, close.   Don’t throw your trash on the ground and expect some faceless public worker to clean up after you.   More-reliable code will be your reward.

Following your maxim you'll wind up with a bunch more code, but I'm not sure what actual benefit you're hoping to wind up with in Perl. If you just use autodie and properly scoped variables you'll get the same effect with fewer bugs. (Assuming that you its error handling behavior is sufficient for you.)

Of course this doesn't hold if you're using a different language. For instance in C++ stuff doesn't get closed at all and you'll leak memory. Alternately in a true garbage collected language like Java it won't get closed until the next gc run, and you can wind up running out of resources because of leaked handles, which is also bad.

But if you're coding in Perl, you might as well take advantage of the features it offers. And prompt cleanup of stuff is one of them.

So all-in-all, leaving to scope to clean up is okay?

I've recently seen some '99' MySQL errors, which after a little googling appears to be related to too many TCP connections, strange that a script was hitting this limit and not the mysql connection limit first, but I digress.

I've attempted to use Apache::DBI in the past to keep persistent connections but it caused more headaches at the time, so I've always steered clear, and we'll be moving to Fast::CGI/Lighttpd soon anyway, what's similar in a fastCGI env?

I think I'll stick with letting Perl's scope do the work of disconnecting, thanks for making me feel a tad more relaxed in doing so.

When this "active avoidance" makes any difference, I wonder if a connection pooler isn't preferable, e.g. skype's pgBouncer (postgres).

(I haven't measured/benchmarked.)

That depends on your use case. In general, in Perl, I would not advise connection pools.

For mod_perl the standard architecture is to use a pre-fork model with a reverse proxy in front to avoid wasting the time of the expensive mod_perl processes. In this model it generally makes the most sense for each mod_perl process to have one (and only one) database connection to your main database server. In this design a connection pool would have to be maintained outside of mod_perl, and adding one requires another rpc layer of overhead. This represents a lot of work, with little benefit.

The classic place where a connection pool makes sense is in a multi-threaded program where only a fraction of the threads need access to a database handle at any given time. In this architecture a connection pool is much cheaper to implement, and the rewards are more obvious. However threading in Perl is heavy enough that I would not advocate using this architecture for Perl.

The other place where a connection pool makes sense is when you've reached the point where your database is maxing out its capacity. Then you'll want to consider tricks like sharding your database, and maintaining connection pools for accessing each shard. Based on my past experience, I would not expect you to need to go there at below a million dynamic pages an hour. (Though before then you may think you need to go there, when what you really need is to tune your app and database.)

You probably know exactly when you're done with it, otherwise you may need to re-examine your design. Keeping the connection open longer than you need it is a waste of resources somewhere, somehow.

For long-running programs it can be tempting to just "keep it around" but unless your design requires that you hammer the server with a constant stream of queries it's probably better to make your conversations with the server short and to the point.

Establishing a new TCP connection with MySQL isn't that expensive, unless we're talking about thousands of connections per second I doubt that's where your bottleneck will be.

...that's my personal opinion anyway.

We probably have 200 methods that connect to the db to do something, it's a pain having to $sth->finish and $dbh->disconnect at the end of every one, if they're both going to be called anyway when I return from the sub.

Only if $dbh is scoped to the sub. If $dbh is scoped to the file or the module, it is not disconnecting at the return from the sub.

Personally I never disconnect, which has several reasons:

• Most of my script/programs use the database from start to finish, so there is no use in disconnecting half-way through.
• Connects to remote databases is very costly, so I rather have the database handle survive as long as possible.
• Cleaning up my handles, certainly for prepared statements and cursors, should already give the server enough room to clean up for my session. Only having the session/handle open won't hurt the server too much. Note that I am using the handle, not just keeping it open to pester the server.
• I am also working with a database that has a very nasty bug in opening a new handle to the same database (for some versions of this database server). It will simply die (in very different variations of die ranging from a database crash to a script exit). As I mostly script for database-agnostic processes, I rather prevent this from happening.

Personally, I think scoped cleanup is the neatest way to manage database resources, but YMMV. The local DBA certainly should have a word in the matter.

FWIW I manage two DBD's on CPAN and am co-maint of DBI.


Enjoy, Have FUN! H.Merijn

Why would you call finish? From the DBI docs - Indicate that no more data will be fetched from this statement handle before it is either executed again or destroyed. You almost certainly do not need to call this method. I thought the only useful case for finish is when you absolutely know there are more rows you have not read and you know you are not going to read them. If I had not read all rows but intended to that would be a bug in my code and calling finish might mask that.

As for not disconnecting and letting scope handle it I'd just warn you that this can be a pain to debug if your code is wrong e.g., if you fail to commit a transaction in part A of your code and the connection handle goes out of scope in part B of your code and DBI rolls it back. You'll get a warning of the rollback when the handle goes out of scope at B but the problem code is at A.

Probably not your case, but when you have long-persisting connections against MySQL from a long-running process, there are automatic timeouts from the server that will eventually hose your connection.

• Are your database server's connect operations lightweight (MySQL) or heavyweight (DB2) ?
• Do you connect and disconnect often?
• Do you maintain a small or large number of simultaneous connections?
• Do you fork ?

We connect to 3 DB platforms (all on MySQL tho).

One is a single master, the other 2 are masters with several slaves.

I don't pass dbh's around between methods, since sometimes I may have connected to a slave for read-only operations, but might have passed the the dbh into a method requiring write access to the master, so it's easier to connect in each method (to the relevant master/slave), plus, it's MySQL, so not a massive hit on the connection time.

We clearly connect/disconnect a fair amount, but this isn't causing performance implications (yet).