How can I have DBIC persistent database connection

Question

I am using mod_perl for my web application. Currently, I plan to use a mysql database across the network. In every CGI request to display_customer_transaction.cgi, my script will

1. Open up database connection across network
2. Perform query on the database using SQL statement
3. Analysis the data retrieved from database
4. Print out the data in HTML format
5. Close the database connection

After some profiling, I realize step (1) is the bottleneck. Hence, I wish to avoid opening and closing a database connection for every CGI request. My wish is, if my first CGI request opens up a database connection, my second incoming CGI request (from different client) may reuse the first database connection.

I tried to Google for "DBIX Persistent Database Connection" but hardly find relevant result. (Edit: that's because it's called DBIC, or DBIx::Class, not DBIX.)

I further find for relevant information, using Apache::DBI (However, my intention is on DBIX, not Apache::DBI). There are some information which confused me:

The Apache::DBI module still has a limitation: it keeps database connections persistent on a per process basis.

All the while, my concept on how Apache serving CGI request is that

1. Apache will always spawn a new process to serve an incoming new CGI request. Whenever the Perl interpreter finish executed Perl script, the process will dead.

So, if Apache::DBI module only able to keeps database connections persistent on a per process basis, how can my second CGI request re-use back the connection opened by the first CGI request?

But come back to my original question. How can I have DBIX persistent database connection in mod_perl?

Answer 1

Finally, I didn't use persistence connection at this time based on the judgment of my connection per seconds is very low. For performance scaling plan, I would rather go for DBIx + memcache solution.

Answer 2

Apache::DBI alters the way the DBI module works. Assuming you're using DBIx::Class (you aren't being specific enough) then it uses the DBI module to get its DB connections, so this will work for you.

When you use mod_perl (and assuming your mod_perl setup is right), a Perl interpreter is loaded into Apache and your program loads into that. Apache runs a limited number of processes to serve requests - each one serves more than one request.

The Apache processes do die eventually, and get respawned, but they serve many requests before that happens. These are the processes that the Apache::DBI documentation refers to; it's trying to say "you will re-use the same connection over and over, but that doesn't mean that only one connection is open at a time."

Answer 3

Try Apache::DBI before you write it off. However, you also want to make your CGI scripts persistent. If you have vanilla CGI programs right now, you can use the PerlRun or PerlRegistry options to make them persistent. That, along with Apache::DBI, should do the job. Sure, each child process has DBI connections, but that's not unreasonable.

Give it a try before you give up on it. :)