Database Connection Pooling in Perl CGI Script WITHOUT mod_perl

[Pete Butler]

I'm working on a Perl CGI application, and I'd like to set up database
connection pooling for obvious performance-related reasons.

However, the server I'm working on is provided by a third party, so I
do NOT have access to mod_perl.

Does anybody know of a way I can do connection pooling under these
conditions? Or am I just screwed without mod_perl?

(The database I'm accessing is MySQL, if that matters.)

Thanks,
-- Pete Butler

 

[Pete Butler]

DB Connections are relatively LOW overhead compared to loading / compiling /
executing a Perl CGI --> Perhaps your CGI's need to be optimized... the
other question is: How manny DB connections does your third-party server
allow each account to have ??? you may be up against an arbitrary limit over
which you have no control...

They only limit the number of cross-server connections; if script and
DB are on the same server, they claim they don't limit it.

Not that it matters a lot; I was trying to set up a connection pool as
"pre-emptive" performance tuning. (The performance problems I'm
seeing appear to have more to do with the environment than the code,
but I figured tweaking the code might help a bit.) Sounds like the
cost:benefit ratio isn't breaking correctly for me with this approach,
though.

On top of that -- you'd probably be better off implicitly "connecting" and
"disconnecting" in your script so you can immediately "free-up" any open
connections that are not really in use...

Already doing that, though nice to hear it re-affirmed as a solid
design decision.

I have a FreeBSD / mySQL / postgreSQL dedicated server with about 200 sites
hosted on it --- Roughly 30,000 queries per day and have no performance
issues with database connections.... Perhaps your "third-party" server is
'over-hosting"...

Can't rule that out; site performance seems to vary quite a bit
depending on time of day.

you might also analyze your SQL queries for efficiency /
speed while you're at it...

In the queue; I don't claim to be a SQL guru, so I'm sure a little
research there will be well worth my while.

Thanks for the help!

-- Pete Butler

 

[Pete Butler]

With these, I find it very hard to see where you could store your
open database connections. Or, ok, you could have a separate standalone
process having the DB connections open, and then connecting to that
process form your CGI, but I think that wouldn't be that much faster.
Drat.

As for performance; I think the cost of setting up a MySQL connection
can't be even 1/10 of the cost of firing up a CGI process, so your
bottleneck won't be the database connection set-up time.

I didn't realize the DB connection was chump change compared to the
CGI itself, so thanks for that tip. Even so, I think I can help my
performance if I trim down the number of connections I make; I have it
set up so that each SQL query opens and closes its own connection.
That's going to be a minimum of two connections per page load (one to
grab user session data, at least one to populate the page). Maybe if
I do a little refactoring, I can get my code to open and pass around a
single connection. Or something.

But regardless, thanks for letting me know I'm pretty much chasing a
dead end.

-- Pete Butler

 

[ctcgag]

I'm working on a Perl CGI application, and I'd like to set up database
connection pooling for obvious performance-related reasons.

However, the server I'm working on is provided by a third party, so I
do NOT have access to mod_perl.

That makes it tough. You could have one long-lived non-CGI perl script
that held the connection and acted as agent to the CGI perl scripts, but
the overhead of connecting to that agent would be about as bad as the DB
connect in the first place.

Does anybody know of a way I can do connection pooling under these
conditions? Or am I just screwed without mod_perl?

(The database I'm accessing is MySQL, if that matters.)

Yes, it matters a lot. Connecting to MySQL is so light weight that
there is little benefit to be had by pooling, IMHO. Oracle is an entirely
different beast.

Xho

 

[Bryan Castillo]

I'm working on a Perl CGI application, and I'd like to set up database
connection pooling for obvious performance-related reasons.

However, the server I'm working on is provided by a third party, so I
do NOT have access to mod_perl.

Does anybody know of a way I can do connection pooling under these
conditions? Or am I just screwed without mod_perl?

Does you hosting co. offer any other alternatives, such as fastcgi or
php?

As you now know, the biggest performance problem is CGI (fork, exec,
script compilation etc....) Many hosting companies Ive seen offer
php, which is usually embedded in the web server and the api's for
databases usually have a cached connection option.

(Im not saying php is better than perl, but embedded php in the
webserver would probably have better performance than a perl CGI app).