proxy control of database handles

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

If you are developing Perl applications which will run on many machines simultaneously, you run the risk of opening too many connections to your database at once. We are currently not completely mod-perl enabled. That is, we have some raw CGI scripts and some HTML::Embperl-stuff running mod-perl-enabled, FWIW. It is my understanding that DBI::Proxyserver is a method by which to control the number of connections available to the database at one time. However, I did not see any way of controlling the number of connections it would allow. Does anyone have any (fairly complete) examples of using database connection proxying?

Comment on proxy control of database handles

Replies are listed 'Best First'.
Re: proxy control of database handles by cleen (Pilgrim) on Jun 19, 2000 at 19:19 UTC
Ok, since there wasnt any replies to this post, I will try to answer your question as completly as possible, giving a few options and ideas for you to toy around with. To make a long story short, DBI::Proxyserver is a module that allows you to become a proxy server for databases. IE your database requests are sent not to your real database, but to an intermediary machine with this module (along with socket code etc..). As with a any other proxy server, all it does is take requests, interpret them, and redirect as rules specify. The keyword here is rules. With DBI::Proxyserver there is a "configuration" section, which entails the 'accept' paremeter. if accept param here was set to a maximum of 10 connections that can be queued, anything above that will be thrown out. Still, in order to utilize the DBI::Proxyserver, you will have to use the DBI::Proxy module, which understands how the DBI::Proxyserver interprets requests. Another, and in my eyes, a more suitable and cheaper solution to your problem is to use fork() to "hold back" connections that your program might make..For example: `#!/usr/local/bin/perl # How many conncurrent connections do we want to make? $MAXCHILD = 10; @pids; $npids = 0; for ($i = 0; $i<$MAXCHILD;$i++) { my $pid; $pid=fork(); if($pid>0){ $npids++; if($npids>=$MAXCHILD){ for(1..($MAXCHILD)){ $wait_ret=wait(); if($wait_ret>0){ $npids--; } } } next; } elsif(undef $pid) { exit(0); } else { CALL_YOUR_DBI_ROUTINE_HERE(); exit(0); } exit(0); } for(1..$npids){ $wt=wait(); if($wt==-1){ redo; } }` [download] Erm...I think thats right. (I have a directory with snippets of useful code Ive used in other programs, and I tailored this one a little just for this example without testing heheh =P) A third and final thing you could do that I can think of is fine tune your acctuall database options. I know there are maxconnection options in mysql,msql,oracle, but I dont know the exact syntax off hand, but I do know they are pretty well bluntly stated in the documentation. I hope I helped in some little way. -cleen	[reply] [d/l]

Replies are listed 'Best First'.

Re: proxy control of database handles
by cleen (Pilgrim) on Jun 19, 2000 at 19:19 UTC

#!/usr/local/bin/perl

# How many conncurrent connections do we want to make?
$MAXCHILD = 10;

@pids;
$npids = 0;
for ($i = 0; $i<$MAXCHILD;$i++) {
 my $pid;
 $pid=fork();
 if($pid>0){
  $npids++;
   if($npids>=$MAXCHILD){
    for(1..($MAXCHILD)){
     $wait_ret=wait();
     if($wait_ret>0){
      $npids--;
     }
    }
   }
   next;
  }  
  elsif(undef $pid) {
   exit(0);
  }
  else {
   CALL_YOUR_DBI_ROUTINE_HERE();
   exit(0);
  } 
  exit(0);
 } 
 for(1..$npids){
  $wt=wait();
  if($wt==-1){
   redo;
 }
}
[download]

[reply]
[d/l]