Do I have to put DB connection/initialization outside of the FCGI loop to take advantage of FastCGI in Perl?

Question

Let's say I've got some Perl code that increments a column in a specific row of a database each time it's hit, and I'm expecting it to be hit pretty frequently, so I'd like to optimize it with FCGI. Right now, I basically wrapped most of the code in something like this:

while (FCGI::accept() >= 0) {
 [code which currently creates a db connection and makes calls through it]
}

I'm wondering if it's better to put the database connection (my $dbh = DBI->connect(etc)) outside of the FCGI loop so that the script keeps the connection alive, or will I still gain the advantages of FCGI in speed & resources by leaving it in the loop?

Answer 1

You would still gain from FCGI even if you do keep your DB connection in the loop - but you would gain even more if you moved it out.

Answer 2

Connect performance largely depends on the database you're using. PostgreSQL and MySQL are very fast to connect (MySQL especially), and thus usually connect on each request. Other databases such as Oracle are a bit slower and tend to require longer connection lifetimes. It should be easy to test by writing a while 1..100000 loop with DBI->connect() and disconnect to see how fast your database is.

Answer 3

bmdhacks is right that if you're using MySQL or PostgreSQL it doesn't matter as much since connections are pretty cheap. But no matter your database you will have speed gains by using persistent connections.

But if you do decide to go with persistent connections you will need to worry about connection timeouts. These will happen at any time during the life of your program depending on your server settings and the amount of traffic you're getting. ping() is your friend here. And if you need more help, look at how Apache::DBI does it.

Answer 4

Don't put the connection outside the loop, you might lose the connection and then you can't reconnect. You could put it into a global, but then you'd have to do the connection check and reconnects yourself.

Instead, use Ima::DBI or DBI->connect_cached() to do the connection caching for you. It'll do all the work to make sure the connection is alive and reconnect if necessary.

But before you bother, do some benchmarking to find out where your bottleneck really is. I have had the database connection be the bottleneck in the past, but that was with Oracle and it was also 10+ years ago.