Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

dbh->disconnect or leave to scope

by Cagao (Monk)
on Jan 17, 2011 at 17:33 UTC ( #882708=perlquestion: print w/ replies, xml ) Need Help??
Cagao has asked for the wisdom of the Perl Monks concerning the following question:

Hi all,

I don't tend to use dbi->disconnect at all, and just let the scope deal with it, as I've seen in some docs.

Is there any benefit to explicitly disconnecting (especially in a mod_perl environment)?

Any DB work is contained in classes and methods, so never at a top-most level, ie in handler code.

Thanks in advance for any advice.

Comment on dbh->disconnect or leave to scope
Re: dbh->disconnect or leave to scope
by sundialsvc4 (Abbot) on Jan 17, 2011 at 18:54 UTC

    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 (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.

Re: dbh->disconnect or leave to scope
by tilly (Archbishop) on Jan 17, 2011 at 23:05 UTC
    Creating database connections tends to be somewhat expensive. Therefore in a mod_perl environment you want to minimize how much you do that. Therefore you actively want to avoid disconnecting database connections in that environment unless it is a connection to a database that is only rarely needed.

      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.)

Re: dbh->disconnect or leave to scope
by nikosv (Hermit) on Jan 18, 2011 at 10:18 UTC
Re: dbh->disconnect or leave to scope
by FloydATC (Chaplain) on Jan 18, 2011 at 13:05 UTC
    Why not just disconnect it?

    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.

    -- Time flies when you don't know what you're doing

      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.
Re: dbh->disconnect or leave to scope
by petecm99 (Pilgrim) on Jan 18, 2011 at 14:55 UTC
    Yeah, I don't worry too much about it either. That's why I never really got into programming C/C++ - I never was much for cleaning up after myself. When writing, many participles dangle too... :)
Re: dbh->disconnect or leave to scope
by theleftsock (Beadle) on Jan 19, 2011 at 01:51 UTC
    While I completely appreciate from the perspective of the perl it's not a big deal to handle it with scope, please remember to be considerate when you are connecting to a system that maintains a persistent connection. Be aware of how long the infrastructure will maintain that session. Using disconnect will hopefully tell the DB that you are done with that connection and session, freeing it for other systems to use. I have seen many times people hitting infrastructure and not cleaning up their connections causing problems on the infrastructure side by taking all connections and not cleaning up after themselves. I personally think it's very bad practice.
Re: dbh->disconnect or leave to scope
by andreas1234567 (Vicar) on Jan 19, 2011 at 05:45 UTC
    It depends largely on your choice of database and your use of it.
    • 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 ?
    No matter how great and destructive your problems may seem now, remember, you've probably only seen the tip of them. [1]

      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).

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://882708]
Approved by Corion
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (9)
As of 2015-03-06 12:20 GMT
Find Nodes?
    Voting Booth?

    When putting a smiley right before a closing parenthesis, do you:

    Results (161 votes), past polls