postgresqlconnection-poolingdbiapache2.2mod-perl2

Should I disconnect() if I'm using Apache::DBI's connect_cached()?


My mod_perl2-based intranet app uses DBI->connect_cached() which is supposedly overridden by Apache::DBI's version of the same. It has normally worked quite well, but just recently we started having an issue on our testing server--which had only two users connected--whereby our app would sometimes, but not always, die when trying to reload a page with 'FATAL: sorry, too many clients already' connecting to our postgres 9.0 backend, despite all of them being <IDLE> if I look at the stats in pgadmin3.

The backend is separate from our development and production backends, but they're all configured with max_connections = 100. Likewise the httpd services are all separate, but configured with

StartServers       8
MinSpareServers    5
MaxSpareServers   20
ServerLimit      99
MaxClients       99
MaxRequestsPerChild  4000
....
PerlModule Apache::DBI

I had been under the impression that I shouldn't call disconnect() on my database handles if I wanted them to actually benefit from caching. Was I wrong about that? If not, I guess I'll ask about the above error separately. Just wanted to make sure it wasn't this setup...

Apache::DBI's docs say:

When loading the DBI module (do not confuse this with the Apache::DBI module) it checks if the environment variable 'MOD_PERL' has been set and if the module Apache::DBI has been loaded. In this case every connect request will be forwarded to the Apache::DBI module. .... There is no need to remove the disconnect statements from your code. They won't do anything because the Apache::DBI module overloads the disconnect method.

If you are developing new code that is strictly for use in mod_perl, you may choose to use DBI->connect_cached() instead, but consider adding an automatic rollback after each request, as described above.

So I guess for my mod_perl2-only app, I don't need Apache::DBI because Apache::DBI's devs recommend using DBI->connect_cached. And I don't need disconnect statements.

But then DBI's docs say:

Note that the behaviour of [ connect_cached ] differs in several respects from the behaviour of persistent connections implemented by Apache::DBI. However, if Apache::DBI is loaded then connect_cached will use it.

This makes it sound like Apache::DBI will actually affect connect_cached, in that instead of getting DBI->connect_cached behaviour when I call that, I'll get Apache::DBI->connect behaviour. And Apache::DBI's docs recommend against that.

UPDATE: I've set the first 5 parameters in the above config all to 1, and my app is still using up more and more connections as I hit its pages. This I don't understand at all--it should only have one process, and that one process should be re-using its connection.


Solution

  • Unless you plan on dropping Apache::DBI, the answer is a firm no, because Apache::DBI's override really does nothing:

    # overload disconnect
    {
      package Apache::DBI::db;
      no strict;
      @ISA=qw(DBI::db);
      use strict;
      sub disconnect {
          my $prefix = "$$ Apache::DBI            ";
          Apache::DBI::debug(2, "$prefix disconnect (overloaded)");
          1;
      }
      ;
    }