[Dbix-class] Apache::DBI and DBIx::Class

[Mark Hedges]

On Tue, 4 Apr 2006, Doran Barton wrote:
> I'm a relative newbie with DBIx::Class- but recently deployed a new web 
> application using it. The application works great and thanks to DBIx::Class, 
> it took less time to do.
> 
> The application is implemented as mod_perl content handler. I am using 
> Apache::DBI, as with other mod_perl applications, to maintain persistent 
> connections to our PostgreSQL database. However, since deploying this 
> application, we frequently exhaust our database connections. I think, 
> perhaps, DBIx::Class is not taking advantage of Apache::DBI.

It works fine for me.  Try this in startup.pl which gets run 
from your main Apache config file every time the server starts:

use Apache::DBI;
$Apache::DBI::DEBUG = 1;

Then every DBI connection made by anything run by Apache will go 
through Apache::DBI instead of regular DBI, transparently.
DBIx::Class shouldn't have to know anything about Apache::DBI.

The DEBUG line will show real or ping-only connects in the log.

It helps in a high volume environment to greatly reduce the idle 
timeout of connections in /etc/my.cnf or your equivalent:

    [mysqld]
    # ...
    # mysql client connections
    interactive_timeout=120
    
    # apache::dbi connections
    wait_timeout=8
    
    # ditch connects that fail to sync?
    connect_timeout=10
    
If you're running in a true Apache handler, go ahead and
reconnect to the database for every request.  If the connection
is still live, Apache::DBI will just do a ping and doesn't need
to reconnect.  With the above settings it will only happen if
the same Apache child is used within 8 seconds of the last activity.
But Apache::DBI should do everything for you transparently.

If it still doesn't work, try increasing $Apache::DBI::DEBUG to 2
in startup.pl and try to figure out the noisy log output.

Mark