LukeyBoy has asked for the wisdom of the Perl Monks concerning the following question:

Hey everyone. I couldn't find any information on this, so here goes. I'm building a site that connects to an Oracle database on the backend (so far without using modperl). After searching Perlmonks, Google and the Perl documentation I can't really find any references to database connection pooling. The only thing that comes close is Apache::DBI, but that looks like it has quite a few caveats.

One idea that popped into my head was using some IPC communications to fire the database handle between each Perl process running, but if there's work already done I don't want to reinvent the wheel. Any ideas?

Replies are listed 'Best First'.
Re: Database Pooling with DBI
by perrin (Chancellor) on Feb 28, 2002 at 15:33 UTC
    What makes you think you need database connection pooling? Apache::DBI gives persistent connections, which solves the speed problem. It's easy to use, and whatever caveats you've heard are probably exaggerated. Unless some of your mod_perl processes are handling requests that don't involve a database at all, they should hold onto their connections.

    Doing actual sharing of database handles is hard, since it involves passing sockets and file handles around between processes safely. That's why it hasn't been done.

      I agree that it's often unnecessary to pool database connections, especially if the heavyweight Web servers are configured to use a lightweight front-end server as recommended in the mod_perl guide. I also agree that it's difficult to do. DBD::Proxy looks like an interesting attempt to deal with this, although its documentation mentions that its connect_cached method, which pools connections using an intermediary process, is new and still experimental. It might be worth a look, though.
        It's not worth a look. It's very slow.
Re: Database Pooling with DBI
by mpeppler (Vicar) on Feb 28, 2002 at 17:05 UTC
    Whether connection pooling can be done "easily" depends on how the underlying database client library handles sockets. AFAIK Oracle OCI doesn't support sharing connections between different clients, meaning that you'd actually have to write your own server daemon that sits between your web server and Oracle. This is seriously non-trivial to get right.

    At a previous job we wrote such a beast - actually at the time we used FreeBSD for the front-end, and there were no Sybase libraries available for that platform. So we designed a simple communication protocol, with a server on the front-end listening on a Unix domain socket and then communicating with a multi-threaded server on the middle tier that itself talked to Sybase. It worked reasonably well, but there were always problems, and maintaining the two multi-threaded apps was a pain.

    As perrin says - just use Apache::DBI, and maybe use a lightweight http proxy front-end to limit the number of processes that need to have a database connection.

    Michael

    P.S. - now if you were using Sybase instead you could use Apache::Sybase::ConPool :-)

Re: Database Pooling with DBI
by lachoy (Parson) on Feb 28, 2002 at 16:50 UTC

    You might want to look at ResourcePool, which does this outside of a web framework and for different types of datasources.

    Chris
    M-x auto-bs-mode

      That looks like it's only for a single process though. Not much use for pooling anything in a multi-process server like Apache/mod_perl.
Re: Database Pooling with DBI
by ccmclane (Initiate) on Mar 01, 2002 at 03:21 UTC
    You should look at SQL Relay. From it's description:

    "SQL Relay is a persistent database connection pooling, proxying and load balancing system for Unix and Linux supporting ODBC, Oracle, MySQL, mSQL, PostgreSQL, Sybase, MS SQL Server, IBM DB2, Interbase, Lago and SQLite with C, C++, Perl, Perl-DBD, Python, Python-DB, Zope, PHP, Ruby and Java APIs, command line clients, a GUI configuration tool and extensive documentation. The APIs support advanced database operations such as bind variables, multi-row fetches, client side result set caching and suspended transactions. It is ideal for speeding up database-driven web-based applications, accessing databases from unsupported platforms, migrating between databases, distributing access to replicated databases and throttling database access. "

    I have not used it, but it does have a great deal of documentation and an apparently active email list.