Database Connection Pools with Perl

[Tanalis]

Monks,

While I'm aware that this is something that's been discussed once or twice before, the node's I've managed to uncover so far haven't taken the tack on this that I expected them to, so I'm posting anew.

I'm in the process of designing and implementing a simple database accessor, capable of performing either a pre-defined or arbitrary (read: user specified) query on a database, rolling the results up in XML, and passing this data back to a client script.

For academic reasons, I am looking at setting this accessor up as a web service, using a simple SOAP interface to allow requests to be sent from (not necessarily Perl) clients to the accessor.

Again for academic reasons, I would like to set up a simple pool of database connections, allowing clients to obtain a connection for the duration of their session. Is this as simple as creating an array of connections, and simply assigning database handles as requests come in, or is there more to it?

Moreover, does anyone have experience creating such a pool in Perl, and, if so, what pitfalls did you encounter? I'm aware that connection pools can be (fairly) easily implemented in "other" languages (I've looked primarily at Java): would I be better to write this part of the project in that over Perl?

Any advice is more than welcome.

Thanks in advance.

-- Foxcub

[stvn]

Foxcub,

Abigail-II is right, there are many modules on CPAN related to pooling. If you are running your server with Apache/mod_perl, the simplest is to use Apache::DBI, it works great, I have used it for years. If you however are not going to use Apache/mod_perl, then I found ResourcePool with a quick search for "database pools" on CPAN. There seemed to be alot of "pool"-oriented modules there, although not all DB-oriented. You can always read the module code though, it may provide ideas.

Now, you say "academic" alot, so i am going to take a guess that you are actually looking more for information about how to write a connection pool, rather than a connection pool module you can use. (after all the point of academics is to accumulate knowledge and learn is it not? or is it all about beer and parties?). In that case, I would just Google any combination of "database" and "pooling", and see if anything worthwhile pops up.

From what I know (and dont take me as an expert) resource pooling really is as simple as setting up an array (or hash or whatever data structure strikes you fancy or fits your requirements) and then being very very very very anal about managing those resources. If its DB connections, you need to always check that they are in fact connected, don't require rollback of cleanup in some way, and are generally in good working order before giving them out. As for reclaiming them, you can either force the user of your pool to do this, or (as in Apache::DBI) assume a one per-process connection (and since one client to one process is usually a fair assumption, management becomes pretty easy). It has always seemed to me that there was never any deep magic to DB pooling, just good old common sense and a big health dose of paranoia.

I know nothing about SOAP however, so I will rely on the other monks to answer that part.

-stvn

[mpeppler]

In general it is difficult to share database connections between processes, or even between perl threads (assuming iThreads, and the older 5.005 threads are broken anyway). So in my opinion the first thing you need to define is how is your SOAP server is going to be implemented.

One thing that you might be able to implement assuming the work load isn't too high is a threaded connection pool server in perl, where you create a number of worker threads that each have a connection, and then use something like Thread::Queue to feed data to the threads. However, given the state of threading support in perl 5.8 I would be a little wary of implementing this in a production environment.

I've written a connection pooling module for Apache/Modperl (Apache::Sybase::ConPool) that works pretty well, but it requires Sybase as the back-end database, and it works because Sybase has one of the few database client APIs that is capable of sharing database connections between processes.

You also mention that you'd like to have each connection stay bound to the session for the duration of the session. This will be difficult to do if by "session" you mean more than a single request, given that such requests aren't guaranteed to be picked up by the same server process (at least under the http protocol), and if the process that handles the request changes, then you've obviously got a different connection for the new request...

Michael

[Anonymous Monk]

I am preparing a Orable DB Connection Pool at Apache server startup, which should be available on demand to httpd childs. I have to make sure that there are Maximum possible number of connection at heavy load condition and minimum connection otherwise. I guess I can implement the Connection Pool, but need to know how my httpd child, which is actually php page calling perl scripts are gonna get the connection objects. Any lead would be helpful.

[Anonymous Monk]

Orable manual should explains everything, like just appending :POOLED to connect, if you have the right versions of client softwares

[Roger]

There are many (many) CPAN modules that fits your project description, below are a few I would pick to implement the simple client/server:


Database	DBI + DBD::mysql, DBD::Sybase, etc.
SOAP	SOAP::Lite, SOAP, etc.
XML	XML::Simple, XML::Parser, etc.

[Abigail-II]

You mention "academic" several times. So I guess you have done research (that's what they do in the academic world). So, you must have searched CPAN, and came up empty handed. Which surprises me, because I always thought someone had solved the problem and made the solution available.

Abigail