Tomcat, CGI scripts and DBI connection persistence

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

Replies are listed 'Best First'.
Re: Tomcat, CGI scripts and DBI connection persistence by Perlbotics (Archbishop) on Oct 29, 2011 at 20:06 UTC
FCGI could solve your problem with the persistent DB connection, however AFAIK Tomcat does not support FCGI out of the box. Though there seems to be a working JFastCGI servlet... (never tried it myself). Another idea would be to emulate FCGI by creating a daemon that implements your service and interfaces with a stub CGI script, e.g. by means of (domain-)sockets? Thus, the daemon maintains a permanent connection to the DB and handles request forwarded from the CGI stub. `client <-(HTTP)-> Tomcat <-(CGI)-> CGI-stub <-(IPC)-> Daemon <-DBI +-> DB` [download] Very awkward and probably not much faster than simply re-connecting to the DB... Maybe it's easier to stay with Tomcat and write a servlet (Java) or stay with the Perl (F)CGI script and use a Perl based HTTP server (e.g. Starman, Twiggy, HTTP::Server::)? Perhaps you can have two servers running (Tomcat and Perl-HTTP-server) and edit the CGI URIs, so CGI calls get routed to the Perl HTTP server? Very awkward again... I would negotiate with customer or boss again to get rid of this artificial limitation. Why spend time and energy circumventing a problem that could be solved better the traditional* way? HTH	[reply] [d/l]
Re: Tomcat, CGI scripts and DBI connection persistence by bart (Canon) on Oct 29, 2011 at 21:53 UTC
Are you sure the DBI connection is your bottleneck? You shoul time it, with Time::HiRes you fetch the times just before and after the connection, to calculate how many milliseconds it actually takes. Depending on the database this might actually be not more than a few milliseconds, in which case I'd say: don't bother.	[reply]
Re^2: Tomcat, CGI scripts and DBI connection persistence by spx2 (Deacon) on Oct 30, 2011 at 11:02 UTC
Thanks, I will use Time::HiRes to see if it is indeed a bottleneck	[reply]
Re: Tomcat, CGI scripts and DBI connection persistence by ikegami (Patriarch) on Oct 29, 2011 at 19:57 UTC
DB connections cannot survive a process's lifespan. I really don't know anything about Tomcat, but I don't see anything about a persistent Perl interpreter (i.e. one that survives more than one request).	[reply]
Re^2: Tomcat, CGI scripts and DBI connection persistence by spx2 (Deacon) on Oct 30, 2011 at 11:07 UTC
After reading the docs of Apache::DBI I thought that this is .. in fact , possible. Are you sure they can't span a process's lifespan ?	[reply]
Re^3: Tomcat, CGI scripts and DBI connection persistence by afoken (Chancellor) on Nov 01, 2011 at 09:57 UTC
Apache::DBI is used in combination with mod_perl. It maintains a pool of DBI connections inside each Apache process, that can be used by Perl code executed by mod_perl, still inside the Apache process. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re^3: Tomcat, CGI scripts and DBI connection persistence by ikegami (Patriarch) on Nov 01, 2011 at 20:37 UTC
Perl code executed by mod_perl is executed in a Perl interpreter in the Apache process. The interpreter is created when apache starts up and it persists on until the apache child exits. While the db connection is still limited to a process -- each apache process has its own pool -- that process can handle more than one HTTP request.	[reply]
Re: Tomcat, CGI scripts and DBI connection persistence by keszler (Priest) on Oct 29, 2011 at 19:58 UTC
One option: write a standalone Perl program that runs as a daemon/service; that program maintains the database connection and answers requests from the cgi-bin/<scriptname>.pl programs. (Communication to the standalone program via socket, pipe, etc.)	[reply]
Re^2: Tomcat, CGI scripts and DBI connection persistence by spx2 (Deacon) on Oct 30, 2011 at 11:06 UTC
That's a good idea, but wouldn't that induce additional overhead ? opening the socket, passing data through, receiving it .. ?	[reply]
Re^3: Tomcat, CGI scripts and DBI connection persistence by keszler (Priest) on Oct 30, 2011 at 13:15 UTC
There would be overhead, but how do the cgi-bin/<scriptname>.pl programs communicate with the DB? That has all the same overhead, plus the database's authentication. If the individual programs typically run a small number of queries per invocation then the overhead of DB authentication is relatively more significant. Also, if the website is heavily used the DB might be "happier" with a small number of connections making many queries vs many connections making few queries each.	[reply]